DBI, MySQL and apostrophes

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Let's assume I have this code which inserts some values into a MySQL

my $var = "Jake's cake";
$SQL = "INSERT INTO `recipes` (`name`, `stat`) VALUES ('$var', 'ok')";
$my_db->do($SQL) or die "Error!\n";

Executing this will render an error:

DBD::mysql::db do failed: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 's cake', 'ok')' at line 1 at my_script.pl line 77.

Obviously, it breaks because of an apostrophe in $var variable.

What are the ways to get around this issue?

I could prepend each apostrophe with a backslash:

$var =~ s/'/\'/;

But I'm not sure if it's the right way to do this.

Are there any "better" approaches?

Tomasz Chmielewski

Re: DBI, MySQL and apostrophes

Quoted text here. Click to load it

In my opinion, the only sane way to deal with this is to use
placeholder/bind variables.

$SQL = "INSERT INTO recipes (name, stat) VALUES (?, 'ok')";
$my_db->do($SQL,undef,$var) or die "Error!\n";


-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

Re: DBI, MySQL and apostrophes

Quoted text here. Click to load it

See 'Placeholders and Bind Values' in perldoc DBI.


   If you put all the prophets,   |   You'd have so much more reason
   Mystics and saints             |   Than ever was born
   In one room together,          |   Out of all of the conflicts of time.
ben@morrow.me.uk                                    The Levellers, 'Believers'

Re: DBI, MySQL and apostrophes

On Wed, 20 Aug 2008 23:03:06 +0200, Tomasz Chmielewski wrote:

Quoted text here. Click to load it

Just drop the apostrophes, they are not needed.

Besides, use DBI variable binding. What if $var contained "');delete from
users;"? Well actually that will not work with DBI, but interpolating
variables in SQL queries is a bad idea and has to be done very carefully.
Variable binding is the easy way to get it right.

The following should work (untested and error handling omitted):

my $var = "Jake's cake";
$SQL = "INSERT INTO recipes (name, stat) VALUES (?, 'ok')";
my $sth = $my_db->prepare($SQL);


Site Timeline