in reply to Re^2: Line separators when passing multi-line fields to a database
in thread Line separators when passing multi-line fields to a database

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]

Replies are listed 'Best First'.
Re^4: Line separators when passing multi-line fields to a database
by davies (Monsignor) on Mar 21, 2011 at 17:13 UTC 
    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

[reply]
[d/l]
[select]
      So what happens if you use the 2nd variant of the query? The one that uses double-quotes and as such allows perl to interpret the \n and \r\n sequences? That should work just like you wanted.

      Also, I don't quite understand what you mean by
      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.
      If the table has no indexes (which is always a bad idea), it doesn't matter where you use "it". Besides, whatever you use in WHERE-clauses is preferably looked up using indexes by the database.
      Also, you can probably just create an index on the field you want; bugzilla will likely not even notice and your queries will go faster.

[reply]

In Section Seekers of Perl Wisdom