Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

What are placeholders in DBI, and why would I want to use them?

( #7548=categorized question: print w/ replies, xml ) Need Help??
Contributed by btrott on Apr 14, 2000 at 01:42 UTC
Q&A  > database programming

Answer: What are placeholders in DBI, and why would I want to use them?
contributed by btrott

Say that you're executing the same SQL statement over and over; for example, perhaps you're importing data from a tab-separated data file into a database. Your insert statement is going to look the same each time; the only thing that will be different between statements is the actual values you're inserting.

For example, you want to insert 100 records into a table with two columns, so you write your insert statement like this:

my $sth = $dbh->prepare(<<SQL); insert into my_table (id, name) values ('15', 'Foo') SQL
The '15' and 'Foo' are literal values that you've placed into your SQL statement; they will be different (presumably) for each record that you're inserting. So you would have to prepare a similar statement 100 times (once for each record).

Placeholders allow you to prepare the statement once; instead of using literal values, you just use '?', like this:

my $sth = $dbh->prepare(<<SQL); insert into my_table (id, name) values (?, ?) SQL
When you want to actually execute this statement, you give execute the values to insert:
$sth->execute('15', 'Foo');
This means that the database has to do much less work. Instead of compiling your statement 100 times, it only has to compile it once.

In order to benefit from this newfound efficiency, you'll have to modify your code slightly. The best way to do this is to use the prepare_cached method instead of prepare. prepare_cached does the same thing as prepare, but first looks to see if the statement has already been compiled; if it has, it returns the compiled version.

So your final code would look something like this:

my $sth = $dbh->prepare_cached(<<SQL); insert into my_table (id, name) values (?, ?) SQL $sth->execute('15', 'Foo');
The other reason to use placeholders is that DBI will automatically take care of any quoting issues for you. You don't need to enclose the ?'s in single quotes, and you don't need to call DBI's quote method on them.

For more information, read the DBI manpage and A Short Guide to DBI.

Answer: What are placeholders in DBI, and why would I want to use them?
contributed by dsb

Placeholders are just what they sound like. They hold the place in an SQL query for a SCALAR variable from somewhere else in the script to be plugged in. EX:

#! /usr/bin/perl use DBI; print "Enter the city you live in: "; chomp( $city = <STDIN> ); print "Enter the state you live in: "; chomp( $state = <STDIN> ); $dbh = DBI->connect(your db info here); $sth = $dbh->prepare( "SELECT name WHERE city = ? AND state = ?" ); $sth->execute( $city, $state );
In this code the first placeholder(?) would be filled with $city and the second would be filled with $state. They are filled in the order that the variables appear as arguments to the 'execute()' function. -kel
Answer: What are placeholders in DBI, and why would I want to use them?
contributed by htoug

You should note though, that here as many other places in Perl, your mileage may vary.

Some DBD's (Database Drivers) support placeholders (most do, but some may not).

Some DBD's have a performance gain from using placeholders others have no, and in some cases even a performance loss.

The best advice is probably to code in a way that is as clear and maintainable as possible, whether that entains using placeholders or not, and then (perhaps) optimize later.

The performance gain from using placeholders in 100 insert statements is probably minor compared to the cost of connecting to the database, so if that is all you do in your script, then why bother? On the other hand, if it is in the inner loop of something repeated umpteen times, then please do bother.

Prepare_cached is also only supported by some DBD's, so it would definitly not be a good idea to use it without checking that it does work for your database.

Just the 0.02 euro of a DBD-writer trying to handle placeholders and prepare_cached in a sensible way with a calcitrant Database System.

Answer: What are placeholders in DBI, and why would I want to use them?
contributed by DrHyde

Be aware that there are some places where you can't use placeholders, even if your DBD supports them. The two which I hit most often are that you can't use a placeholder for a table name; and that you can't use placeholders for variable-length WHERE IN('foo', 'bar', 'baz') constructs. Another I have come across less often is the multiple-record INSERT variation.

This is because all of those alter the structure of the query, as opposed to just plugging different constant values into the same query.

Answer: What are placeholders in DBI, and why would I want to use them?
contributed by schweini

I'd just like to add that extensive use of DBI-placeholders renders a CGI-app basically IMMUNE to the so-called "SQL-injection" family of attacks, which consist of shoving a bit of nasty SQL instrucions down your CGI's thraot, which it would - if it doesn't use placeholders or another escaping tactic, just pass on to the DB.
This, all by itself, is a very, very nice thing to have, i think. additionally, you can never be sure that some data you could've sworn would never contain a single-quote doesn't end up containing one some day, leaving you with a sometimes quite cryptic syntax-error.

Answer: What are placeholders in DBI, and why would I want to use them?
contributed by zby

Sometimes you get an error in a query with placeholders, but the same query with values pasted in the placeholders works. In this case you might need to use bind_params with the third parameter - type. Like this:  $sth->bind_param(1, $value, 4) for integer values on my DBI.

I encountered this problem on a DB2 database accessed via ODBC, it seems that DBI quoted integer parameters and the database did not like it.

Please (register and) log in if you wish to add an answer

  • 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, 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, 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.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others musing on the Monastery: (10)
    As of 2015-11-25 21:39 GMT
    Find Nodes?
      Voting Booth?

      What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

      Results (691 votes), past polls