SQL String Escape Special Characters

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

Hello, I am trying to do a query basically like the following where my query includes special characters. The data in the DB is stored as exactly as leander@whatever.com . I am getting an error of course on the @ symbol but if I escape it, then it doesn't coincide with the data in the DB

The variabe $uname = 'leander@whatever.com'

my $res = $dbh->selectall_arrayref('SELECT uname, pwd FROM login WHERE
+ uname=' . $uname) || die $dbh->errstr;
[download]

Regards

Comment on SQL String Escape Special Characters Download Code

Replies are listed 'Best First'.
Re: SQL String Escape Special Characters by jZed (Prior) on May 25, 2005 at 19:36 UTC
Use placeholders! Like this: `my $uname = q{leander@whatever.com}; my $res = $dbh->selectall_arrayref( q{SELECT uname, pwd FROM login WHERE uname=?},{},$uname };` [download] P.S. This has nothing to do with the @ character, it has to do with the fact that unless you use placeholders or the $dbh->quote() method, you need to have single quote marks around string values in SQL.	[reply] [d/l]
Re: SQL String Escape Special Characters by davidrw (Prior) on May 25, 2005 at 20:20 UTC
JZed's response is correct -- use placeholders (best way) or you're required to obey SQL syntax and single quote the values. Just an additional tip, though -- a simple debugging statement like this: `my $uname = 'leander@whatever.com'; my $sql = 'SELECT uname, pwd FROM login WHERE uname=' . $uname; warn $sql; # <====== would print: # SELECT uname, pwd FROM login WHERE uname=leander@whatever +.com # which is illegal SQL my $res = $dbh->selectall_arrayref($sql) \|\| die $dbh->errstr;` [download] would (could/should) of pointed the problem out as well.	[reply] [d/l]
Re^2: SQL String Escape Special Characters by Joost (Canon) on May 25, 2005 at 23:10 UTC
Never just single-quote variables; just use $dbh->quote() if you can't use placeholders. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re^3: SQL String Escape Special Characters by jZed (Prior) on May 25, 2005 at 23:18 UTC
Good advice, but I think davidrw was just pointing out how the OP could see that the OP's method of concating the SQL string didn't even put in quotes at all.	[reply]
Re: SQL String Escape Special Characters by monarch (Priest) on May 26, 2005 at 08:18 UTC
Hmm.. do you need apostrophes around the string constant you are matching against? `my $quote = $dbh->dbquote( $uname ); my $sql = <<SQLCODE; SELECT uname ,pwd FROM login WHERE uname=$quote SQLCODE` [download] or `SELECT uname ,pwd FROM login WHERE uname='leander\@whatever.com' SQLCODE` [download] Finally a more obvious question, most DBs are case sensitive on searches, could this be a contributing problem?	[reply] [d/l] [select]