I think there is something of a misunderstanding here. Although do() can use placeholders, many statements cannot use placeholders.

This means that there is a possible SQL injection vector where placeholders cannot be used. For example, in PostgreSQL, utility statements such as CREATE USER, CREATE TABLE, DROP TABLE, etc. have no query plan associated with them and therefore cannot be parameterized. In these cases, you have to properly use quote_identifier and quote methods to properly escape your variables.

There are some things you can do to allow these to be parameterized from the Perl side such as wrapping in user-defined functions and then calling with SELECT queries, but this pushes into the function definition level on the SQL side.

It's important to understand and think through how SQL Injection happens to assess it. In Postgres, for example, parameterized queries come in with the placeholder-including text and the parameters supplied separately. This means that the parsing of the query happens the parameters are not considered and the parameters are fed into the parse tree after the query is parsed but before it is planned. In this regard, there is no room for sql injection at all on this level. But anywhere you have to have string interpolation, you get the parsing happening once the variables are interpolated in, so this can affect the output parse tree. Once that happens, you have to use other measures to prevent that. In queries that are not planned, in Postgres this means that all utility statements are problems.

In reply to Re: DBI do() SQL injection by einhverfr
in thread DBI do() SQL injection 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.