A Non-Kludgey way to Escape MySQL quotes in Perl?

software development

#1

I find that, even if the value is not a keyword, MySQL always wants it to be in single quotes. But single quotes are swallowed by perl to convert my variable names into literal values. So, I write stuff like this:

my $query = q{
SELECT licenseNumber, licenseeName
FROM sales
WHERE serialNumber = ‘} ;
$query .= $serialNumberReceived ;
$query .= q{’} ;

Is there not a more elegant way to do this??


#2

Use PHP?


Simon Jessey | Keystone Websites
Save $97 on yearly plans with promo code [color=#CC0000]SCJESSEY97[/color]


#3

OK my sracastic answer is RTFM at Quote and Quote-like Operators

my $query = qq{ SELECT licenseNumber, licenseeName FROM sales WHERE serialNumber = '$serialNumberReceived' };Now what is the difference? Duh, I used the qq operator so that the string will be interpolated for variables. Granted, this is equivalent to

my $query = " SELECT licenseNumber, licenseeName FROM sales WHERE serialNumber = '$serialNumberReceived' ";
Except that with qq the string delimiter can be brackets or other characters, not just quotation mark.

:cool: [color=#6600CC]Atropos[/color] | openvein.org


#4

Use PHP?

You misspelled “Ruby”.


If you want useful replies, ask smart questions.


#5

Very cool. Just use qq instead of quote. I was reading this:

and found what I think is an even better way, which I just tested and works:

my $query = q{
SELECT licenseNumber, licenseeName
FROM sales
WHERE serialNumber = ?} ;

my $statementHandle = $databaseHandle->prepare($query) ;

Error-handling code omitted

my $didExecute = $statementHandle->execute($serialNumberReceived) ;

Then, you can use any quote operator on the prepare, and the execute will never be fooled if the value contains the quote operator, since there is no quote operator!

It looks like you can handle multiple values by using (?, ?, ?) and then ($val1, $val2, $val3), but I haven’t tried that yet.

Thanks!


#6

Or you can just use the Database hander:

my $quoted = $dbh->quote($sql);

All Database API libraries will have an ability to quote strings built in. Use them.


yerba# rm -rf /etc
yerba#


#7

Definitely. Especially if $serialNumberReceived can be manipulated by users. If you don’t know what SQL injection is then have a look at:

http://en.wikipedia.org/wiki/SQL_injection


#8

What has curry got to do with anything?


Simon Jessey | Keystone Websites
Save $97 on yearly plans with promo code [color=#CC0000]SCJESSEY97[/color]


#9

What has curry got to do with anything?

Bravo!


If you want useful replies, ask smart questions.