DBI, MySQL and apostrophes

Discussion in 'Perl Misc' started by Tomasz Chmielewski, Aug 20, 2008.

  1. 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
     
    Tomasz Chmielewski, Aug 20, 2008
    #1
    1. Advertising

  2. Tomasz Chmielewski

    Guest

    Tomasz Chmielewski <> 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.
     
    , Aug 20, 2008
    #2
    1. Advertising

  3. Tomasz Chmielewski

    Ben Morrow Guest

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


    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.
    The Levellers, 'Believers'
     
    Ben Morrow, Aug 20, 2008
    #3
  4. 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
     
    Martijn Lievaart, Aug 20, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Huddle

    SQL and apostrophes

    Chris Huddle, Dec 10, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    758
    Steve C. Orr [MVP, MCSD]
    Dec 10, 2003
  2. musosdev

    HtmlEncode and apostrophes

    musosdev, Mar 31, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    691
    musosdev
    Mar 31, 2008
  3. Ken Fine
    Replies:
    2
    Views:
    187
    Ken Fine
    Feb 24, 2004
  4. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    120
    Paul Vudmaska
    Apr 21, 2004
  5. Jerome Hauss
    Replies:
    0
    Views:
    184
    Jerome Hauss
    Oct 13, 2004
Loading...

Share This Page