Those of you who have ever worked on an application written in Perl and involving a database have probably heard or read these pieces of advice repeatedly:
  1. use DBI, or some more abstract wrapper over it
  2. don't trust user input
  3. don't trust yourself to sanitize user input
  4. use placeholders so the database just does the right thing with user input
You've probably heard placeholders make things easier. You've almost certainly heard that they make your application more robust. It's often said that they can save you from security issues such as SQL injection. That's all true.

So why are those things important? The first two I think are obvious: less maintenance time. Preventing SQL injection is important, too, though. SQL injection can reveal data your application shouldn't. It can be used to empty or even drop a table if the user has sufficient privileges. It might be used to drop a whole database. It can even, without such lofty access, put the wrong information into your database. That's a subtle and possibly very costly attack. I doubt you want your customers greeted as "Mr. !#@$%^" and allowed to buy an $80,000 widget for $5.

Yet there's one thing you may not have heard about placeholders and SQL injection that's also very important. The cost of an SQL injection attack may not be limited to a single database. It may not even be limited to the database software. If there's a security flaw in your database software itself, you could face arbitrary code execution on your database server. If an attacker can send arbitrary queries to your database and they use one of those holes, they're suddenly a user on the server.

If that sounds far-fetched, consider that Microsoft's SQL server currently has an unpatched flaw that allows just that. This flaw currently requires a workaround to prevent those who can issue arbitrary queries from executing arbitrary code. However, if you can keep arbitrary queries from website users from getting to the DB server in the first place, you have an extra measure of safety.

While this kind of flaw in database software may not be very common, this is not the first DBMS to have such a flaw. It is unlikely to be the last. So if you're still trying to escape user input in an ad hoc fashion and none of the common warnings against that have moved you, think about it. Instead of just deleting or corrupting your data, an attacker could add your database server to a botnet or use it to attack the rest of your network. That's something you can't solve by just reaching for a backup tape.

In reply to Yet another reason to use DBI placeholders by mr_mischief

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.