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

Replies are listed 'Best First'.
Re^3: Line separators when passing multi-line fields to a database by Neighbour (Friar) on Mar 21, 2011 at 15:55 UTC
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]	[reply] [d/l] [select]
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]
Re^5: Line separators when passing multi-line fields to a database by Neighbour (Friar) on Mar 22, 2011 at 07:46 UTC
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]
Re^3: Line separators when passing multi-line fields to a database by moritz (Cardinal) on Mar 21, 2011 at 14:32 UTC
Maybe that can be fixed by a setting in your database driver? Perl 6 - second systems done right	[reply]
Re^4: Line separators when passing multi-line fields to a database by davies (Monsignor) on Mar 21, 2011 at 14:39 UTC
Unfortunately, I am trying to write something that will work with any installation of BugZilla. Regards, John Davies	[reply]
Re^5: Line separators when passing multi-line fields to a database by moritz (Cardinal) on Mar 21, 2011 at 15:06 UTC
And the bugzilla code base has no examples of how to do it correctly? At least that's where I'd start looking for solutions Perl 6 - second systems done right	[reply]
Re^3: Line separators when passing multi-line fields to a database by dsheroh (Monsignor) on Mar 21, 2011 at 15:03 UTC
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.	[reply] [d/l] [select]
Re^3: Line separators when passing multi-line fields to a database by Anonymous Monk on Mar 21, 2011 at 14:32 UTC
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.	[reply]
Re^4: Line separators when passing multi-line fields to a database by davies (Monsignor) on Mar 21, 2011 at 14:43 UTC
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	[reply]
Re^5: Line separators when passing multi-line fields to a database by Anonymous Monk on Mar 21, 2011 at 20:08 UTC
Blasted auto-correction mode ... changed "newline" to "beeline." Remember that character strings in single quotes are not interpolated; character strings in double quotes, are.	[reply]