Those who post code snippets showing hand-built queries are frequently admonished to use bind variables. And for good reason: values supplied for bind variables are automagically quoted by the appropriate DBD. This eliminates the need for you to do database-specific character escaping within character strings. A cannonical example of this problem is: 
  $name = "O'Reilly";
  $sth = $db->prepare("SELECT * FROM emp WHERE name = '$name'");
  $sth->execute();

[download]
Here, the query that is constructed through interpolation has a syntax error. (Don't see it? Look again.)

Again, using bind variables: 

  $name = "O'Reilly";
  $sth = $db->prepare("SELECT * FROM emp WHERE name = ?");
  $sth->execute($name);

[download]
Here, $name is correctly escaped by the appropriate quote() method supplied by the DBD in use, and everyone is happy.

Query preparation separate from execution allows the database to plan out execution of the query, and save the plan for reuse by multiple executions. This can be a big win, but some databases don't support it. For those that don't, DBI pretends that they do, remembers the query, and when execute() is invoked, constructs the query (from correctly quoted bind variables), and ships it to the database for execution. This pretending is actually a good thing. It allows you to write portable code that will work regardless of whether the underlying RDBMS supports separate query preparation. Oracle supports separate query preparation. Sybase, MS SQL (through 7.0, at least), and MySQL (as of now) do not.

The Pitfall

The lurking horror in this scheme is that DBI doesn't actually parse queries. If you're using a database that doesn't support separate preparation and you hand DBI a query with bind parameters in the wrong places, it will dutifully hold on to it, fill in the pieces when you pass arguments to execute(), and everything will appear to work. Until you try to support a different database. Then, KaBOOM. If you're developing against MySQL with the intent of eventually supporting Oracle, then watch out.

You can only reliably bind values, not field or table names.

Database that plan query execution won't accept this, because they need table and field names to make their query execution plans. Without knowing table and field names, how can the database construct a plan that calls for using indexes that might be available? (Answer: It can't.)

Where people screw themselves with query binding is in trying to do something like this: 

  my $field = $old ? "olddata" : "newdata";
  $sth = $db->prepare("SELECT * FROM ?");
  $sth->execute($field);

[download]
This will work with several database, but only by an accidental side-effect of implementation. Move to Oracle, and KaBOOM. I've seen several code fragments like this in production code, and occassionally in PM posts.

If you want your code to survive migration to a new database, bind values in your queries, not field or table names.

In reply to A DBI Query Binding Pitfall by dws

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.