The other trick is to not explicitly care, but insert and let DBI take care of it. You must do this as: 
    my $sth = $dbh->prepare ("INSERT INTO x (col1) VALUES(?)");
    foreach my $value (@list_of_evil_stuff)
    {
        $sth->execute($value);
    }

[download]
Bound parameters using the '?' operator are handled correctly by DBI on the way in, quoting as required, and such. They can even be binary data which would upset your terminal, for BLOB fields.

As the DBI documentation says:
Using placeholders and `@bind_values' with the `do' method can be useful because it avoids the need to correctly quote any variables in the `$statement'. But if you'll be executing the statement many times then it's more efficient to `prepare' it once and call `execute' many times instead.
You should not do something like this:     $dbh->do("INSERT INTO x (col1) VALUES('$evil_value')"); It is, as you have noted, far too risky since the $evil_value might be "); DROP TABLE foo;", or something sinister.

Quoting is really a non-issue. What is more likely to cause trouble is a user submitting non-numeric data in an INT field, or something equivalent which causes a DBI error. Make sure you handle these elegantly, or at least allow for the possiblity that an INSERT, UPDATE or REPLACE might fail because of user error.

In reply to Re: Escaping user input for MySQL queries by tadman
in thread Escaping user input for MySQL queries by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.