Every minute spent quoting variables by hand and working around the endless stream of problems it will inevitably lead to, is a complete waste.

Just rewrite the queries to use placeholders, because sooner or later that's what you will end up having to do. Not only does it solve all problems related to SQL injection and special characters, it also makes your code easier to read and maintain. With most databases it will even improve performance.

And no, it's not difficult either. Just replace this

my $sth = $dbh->prepare("
  SELECT * FROM foo WHERE bar=$qstring1 AND baz=$qstring2
"); 
$sth->execute();

[download]

with this

my $sth = $dbh->prepare("
  SELECT * FROM foo WHERE bar=? AND baz=?
"); 
$sth->execute($string1, $string2);

[download]

...it's really that simple!

Now, if you really REALLY must quote each variable because you have to build the queries on the fly, don't do the quoting yourself using regular expressions. Instead, use the DBD "quote" method conveniently provided for you. The following code works for simple cases:

my %quoted = map { $_ => $dbh->quote($params{$_}) } keys %params;

It produces a copy of the original hash, with all values properly quoted for safe use by the database you happen to be working with.

-- FloydATC

Time flies when you don't know what you're doing

In reply to Re: Escaping %params by FloydATC
in thread Escaping %params by DaisyLou

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.