escape characters for dbi

Baz has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I'm using the code below to retrieve a number form a table, for a corresponding surname . This work's, except when the surname is of the following type - "O'Brien", for which case I get null. So I'm guessing that the ' needs to be escaped for dbi (it doesn't need to be escaped when I tried it on the mysql command line).

 $sth = $dbh->prepare("SELECT SUM(number) FROM $serverTable where name
+ = \'$surname\'");
[download]

I also tried the lines below

 $sth2 = $dbh->prepare("SELECT SUM(number) FROM $serverTabl_4 where na
+me =
\'O'Connor\'");
[download]

 $sth2 = $dbh->prepare("SELECT SUM(number) FROM $serverTabl_4 where na
+me =
\'O\'Connor\'");
[download]

and these also returns null - what am I doing wrong??

Comment on escape characters for dbi Select or Download Code

Replies are listed 'Best First'.
•Re: escape characters for dbi by merlyn (Sage) on Jun 10, 2002 at 21:07 UTC
If you use placeholders, you don't have to worry about escaping. `$sth2 = $dbh->prepare("SELECT SUM(number) FROM $serverTabl_4 where nam +e = ?"); $sth2->execute("O'Connor");` [download] -- Randal L. Schwartz, Perl hacker	[reply] [d/l]
Re: escape characters for dbi by dsheroh (Monsignor) on Jun 10, 2002 at 21:10 UTC
Use SQL placeholders and you won't have to worry about it: `$sth = $dbh->prepare("SELECT SUM(number) FROM $serverTable where name += ?"); $sth->execute("O'Connor");` [download] This will also improve performance if you run the query more than once, since it doesn't need to be re-prepared each time.	[reply] [d/l]
Re: escape characters for dbi by twerq (Deacon) on Jun 10, 2002 at 21:09 UTC
You could try: `my $name = $dbh->quote("O'Brien"); $sth = $dbh->prepare("SELECT SUM(number) FROM $serverTable where name = $name);` [download] --twerq UPDATED -- merlyn: why does that work? Does using placeholder auto-quote for you?	[reply] [d/l]
Re: escape characters for dbi by Jenda (Abbot) on Jun 10, 2002 at 22:58 UTC
First, the backslashes in front of the single quotes are not necessary. Second, while it definitely IS better to use placeholders (especialy if you are gonna execute the same command with different "parameters" several times) here is another cute solution: `use Interpolation "'" => 'sqlescape'; $sth = $dbh->prepare( "SELECT SUM(number) FROM $serverTable where name = $'{$surname}'");` [download] Jenda@Krynicky.cz	[reply] [d/l]