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

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??

In reply to:
Is there not a more elegant way to do this?

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 Atropos | openvein.org

Use PHP?

You misspelled "Ruby".

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

http://www.perl.com/pub/a/1999/10/DBI.html

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!

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.

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

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

In reply to:
You misspelled "Ruby".

What has curry got to do with anything?

What has curry got to do with anything?

Bravo!

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