http://qs321.pair.com?node_id=7548

btrott has asked for the wisdom of the Perl Monks concerning the following question: (database programming)

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

Originally posted as a Categorized Question.

Replies are listed 'Best First'.
Re: What are placeholders in DBI, and why would I want to use them?
by btrott (Parson) on Apr 14, 2000 at 01:59 UTC
    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.

Re: What are placeholders in DBI, and why would I want to use them?
by dsb (Chaplain) on Jan 24, 2001 at 03:01 UTC
    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
Re: What are placeholders in DBI, and why would I want to use them?
by htoug (Deacon) on Aug 14, 2001 at 16:06 UTC
    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.

Re: What are placeholders in DBI, and why would I want to use them?
by DrHyde (Prior) on Jun 19, 2003 at 13:30 UTC
    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.

Re: What are placeholders in DBI, and why would I want to use them?
by schweini (Friar) on Nov 13, 2003 at 08:29 UTC
    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.
      You mean that DBI queries should be written:
      my $query = $dbh->prepare ( "select id, name from user where name = ?" ); $query->execute( $name_from_params );
      Instead of:
      my $query = $dbh->prepare ( "select id, name from user " . "where name = '$name_from_params'" ); $query->execute();
      Couldn't agree more.

      --tidiness is the memory loss of environmental mnemonics

      You have mentioned two good reasons for using placeholders - security and problems with embedded quote marks. There is a third reason which applies in many situations: placeholders, when properly placed outside of a loop save time by allowing the RDBMS to parse and optimize the SQL statement once and then execute it many times without re-parsing or re-optimizing it.
Re: What are placeholders in DBI, and why would I want to use them?
by zby (Vicar) on Jul 16, 2003 at 10:32 UTC
    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.

Re: What are placeholders in DBI, and why would I want to use them?
by erix (Prior) on Dec 30, 2015 at 13:14 UTC

    ... you can't use placeholders for variable-length WHERE IN('foo', 'bar', 'baz') constructs.

    PostgreSQL offers the following construct:

     where array[ column_name ] <@ ( ? )

    you can then $sth->execute(), passing an arrayref (i.e.: ['foo', 'bar', 'baz'] ) to the single placeholder.

      You can, however, do something along the lines of:

      my $sql = '... WHERE foo IN (' . join(',', ('?') x scalar( @values ) +) . ') ...';

      in order to generate the proper placeholder-based statement that you can then pass your parameters into.

      See also DBIx::PreQL.

      Update: As per BrowserUK (below), in order to make use of this, you either need to prepare your query each time, cache your prepared query based on number of parameters using prepare_cached or some other method, or know that the number of parameters will be the same each and every time.

      --MidLifeXis

        You can, however, do something along the lines of: 01 my $sql = '... WHERE foo IN (' . join(',', ('?') x scalar( @values )) . ') ...'; in order to generate the proper placeholder-based statement that you can then pass your parameters into.

        Only if you will have the same number of parameters for each binding; and know that number a priori when you prepare the statement.


        With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :)
        In the absence of evidence, opinion is indistinguishable from prejudice.