DBI, MySQL and apostrophes

[Tomasz Chmielewski]

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


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.
Error!

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?

 

[xhoster]

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

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.
Error!

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

What are the ways to get around this issue?

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";

Xho

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

 

[Ben Morrow]

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


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.
Error!

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

What are the ways to get around this issue?

See 'Placeholders and Bind Values' in perldoc DBI.

Ben

 

[Martijn Lievaart]

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?

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);
$sth->execute($var);

HTH,
M4