But passing \n through a placeholder converts it to \\n, which does not match the WHERE clause.

Regards,

John Davies

If you execute:

$sth = $dbh->prepare('UPDATE sometable SET value1=? WHERE value2=?');
$sth->execute('something with \n in it', 'something else which inciden
+tally has a \n in it');
[download]

I'm quite sure that the SQL will receive the strings exactly as you supplied it (Otherwise database-users around the world will have to start worrying quite a bit).
In this case, using single-quotes, the \n will not be interpreted and supplied to the database as-is. If you want (perl) to interpret the \n and send a singular newline (0x0A), use double-quotes in the execute-statement.

However (using double-quotes), when you use \n in the WHERE-clause it will only match LF's (0x0A) and not CRLF (0x0D0A), should those be present. If you also want to match those, you should do this:

$sth = $dbh->prepare('UPDATE sometable SET value1=? WHERE value2=? OR 
+value2=?');
$sth->execute("something with \n in it", "something else which inciden
+tally has a \n in it", "something else which incidentally has \r\n in
+ it");
[download]

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("DBI:mysql:bugs:localhost", "bugs", "sockmonkey
+", {RaiseError => 1}) or die $!;
$dbh->trace(2);
my $sth = $dbh->prepare('UPDATE sometable SET value1=? WHERE value2=?'
+);
$sth->execute('something with \n in it', 'something else which inciden
+tally has 
+a \n in it');
[download]

returns

F:\BugZilla>testplaceholder.pl
    DBI::db=HASH(0x1ab141c) trace level set to 0x0/2 (DBI @ 0x0/0) in 
+DBI 1.616-
ithread (pid 3592)
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x1ab132c)~0x1ab141c '
+UPDATE som
etable SET value1=? WHERE value2=?') thr#233974
        -> dbd_st_prepare MYSQL_VERSION_ID 50147, SQL statement: UPDAT
+E sometabl
e SET value1=? WHERE value2=?
        >- dbd_st_free_result_sets
        <- dbd_st_free_result_sets RC -1
        <- dbd_st_free_result_sets
>count_params statement UPDATE sometable SET value1=? WHERE value2=?
        <- dbd_st_prepare
    <- prepare= ( DBI::st=HASH(0x1ab10cc) ) [1 items] at TestPlacehold
+er.pl line
 7
    -> execute for DBD::mysql::st (DBI::st=HASH(0x1ab10cc)~0x1ab110c '
+something
with \n in it' 'something else which incidentally has
+a \n in it') thr#233974
   Called: dbd_bind_ph
   Called: dbd_bind_ph
 -> dbd_st_execute for 01a34d2c
        >- dbd_st_free_result_sets
        <- dbd_st_free_result_sets RC -1
        <- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 50147
>parse_params statement UPDATE sometable SET value1=? WHERE value2=?
Binding parameters: UPDATE sometable SET value1='something with \\n in
+ it' WHERE
 value2='something else which incidentally has \n+a \\n in it'
                --> do_error
Table 'bugs.sometable' doesn't exist error 1146 recorded: Table 'bugs.
+sometable'
 doesn't exist
                <-- do_error
IGNORING ERROR errno 0
 <- dbd_st_execute returning imp_sth->row_num 4294967294
    !! ERROR: 1146 'Table 'bugs.sometable' doesn't exist' (err#0)
    <- execute= ( undef ) [1 items] at TestPlaceholder.pl line 8
DBD::mysql::st execute failed: Table 'bugs.sometable' doesn't exist at
+ F:\BugZil
la\TestPlaceholder.pl line 8.
DBD::mysql::st execute failed: Table 'bugs.sometable' doesn't exist at
+ F:\BugZil
la\TestPlaceholder.pl line 8.
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x1ab110c)~INNER) thr#
+233974
        Freeing 2 parameters, bind 0 fbind 0
       ERROR: 1146 'Table 'bugs.sometable' doesn't exist' (err#0)
    <- DESTROY= ( undef ) [1 items] at TestPlaceholder.pl line 8
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x1ab141c)~INNER) thr#
+233974
imp_dbh->pmysql: 1882fac
       ERROR: 1146 'Table 'bugs.sometable' doesn't exist' (err#0)
    <- DESTROY= ( undef ) [1 items] at TestPlaceholder.pl line 8
[download]

As you will see, the parameter received by DBI is single quoted, but when it binds the parameters, the literal \n is escaped. The line break is converted to an unescaped \n. This is what I would expect to protect against Bobby Tables, and is what I observed when trying on my own.

I don't have a problem with plain text from normal sources that contains line breaks. As this shows, they are converted to unescaped \ns that work perfectly. This is how BugZilla enters text wherever I have been able to find it. Normally, I would be able to follow this and simply pass the text with line breaks to the database to insert. But it's the WHERE clause that has me stuffed. The text comes from the database and may contain 0D or 0D0A depending on the installation. I can convert it to a literal \n, but as the trace shows, this then gets escaped. I have to use it in the WHERE clause because the table isn't indexed, so I can't use the index key that I use for everything else.

Regards,

John Davies

Maybe that can be fixed by a setting in your database driver?

Perl 6 - second systems done right

Unfortunately, I am trying to write something that will work with any installation of BugZilla.

Regards,

John Davies

Not if your database and driver support placeholders it doesn't. Placeholders pass the data to the db completely unchanged. They do not escape any characters¹. Passing a newline character through a placeholder results in the database receiving a newline character, not the string '\\n' (or the string '\n', for that matter).

Are you perhaps searching for the single-quoted string '\n' (as opposed to the double-quoted string "\n", which is a single newline character, not the two characters \ and n) when putting it into the WHERE clause?


¹ Some drivers may fall back to escaping in order to emulate placeholders for database engines which don't support them - but, in that case, the exact same escaping will be done in the WHERE clause, so it won't affect you anyhow.

You need to tell Perl to interpolate the string; double quotes should do it. The database engine will receive a string containing a literal beeline character.

I'm afraid I don't understand. I don't know what a "literal beeline" character is - Google doesn't help, so any references would be appreciated. The other problem is that I'm trying to prevent Perl changing the \n to \\n or anything else, so I'm not clear on what string I should be getting Perl to interpolate.

Regards,

John Davies