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?

--
Tomasz Chmielewski
http://wpkg.org

[xho...@gmail.com]

Tomasz Chmielewski <t...@nospam.wpkg.org> wrote:
> 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.

[Martijn Lievaart]

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

> 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

[Ben Morrow]

Quoth Tomasz Chmielewski <t...@nospam.wpkg.org>:

> 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

--
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.
b...@morrow.me.uk The Levellers, 'Believers'