Using Prepared Statements and Placeholders in Queries

2.7.1 Problem

You want to write queries that are more generic and don't refer to specific data values, so that you can reuse them.

2.7.2 Solution

Use your API's placeholder mechanism, if it has one.

2.7.3 Discussion

One way to construct SQL statements from within a program is to put data values literally into the query string, as in these examples:

SELECT * FROM profile WHERE age > 40 AND color = 'green'

INSERT INTO profile (name,color) VALUES('Gary','blue')

Some APIs provide an alternative that allows you to specify query strings that do not include literal data values. Using this approach, you write the statement using placeholdersspecial characters that indicate where the values go. One common placeholder character is ?, so the previous queries might be rewritten to use placeholders like this:

SELECT * FROM profile WHERE age > ? AND color = ?

INSERT INTO profile (name,color) VALUES(?,?)

For APIs that support this kind of thing, you pass the string to the database to allow it to prepare a query plan. Then you supply data values and bind them to the placeholders when you execute the query. You can reuse the prepared query by binding different values to it each time it's executed.

One of the benefits of prepared statements and placeholders is that parameter binding operations automatically handle escaping of characters such as quotes and backslashes that you have to worry about yourself if you put the data values into the query yourself. This can be especially useful if you're inserting binary data such as images into your database, or using data values with unknown content such as input submitted by a remote user through a form in a web page.

Another benefit of prepared statements is that they encourage statement reuse. Statements become more generic because they contain placeholders rather than specific data values. If you're executing an operation over and over, you may be able to reuse a prepared statement and simply bind different data values to it each time you execute it. If so, you gain a performance benefit, at least for databases that support query planning. For example, if a program issues a particular type of SELECT statement several times while it runs, such a database can construct a plan for the statement, then reuse it each time, rather than rebuilding the plan over and over. MySQL doesn't build query plans, so you don't get any performance boost from using prepared statements. However, if you port a program to a database that does use query plans, you'll gain the advantage of prepared statements automatically if you've written your program from the outset to use them. You won't have to convert from non-prepared statements to enjoy that benefit.

A third benefit is that code that uses placeholder-based queries can be easier to read, although that's somewhat subjective. As you read through this section, you might compare the queries used here with those from the previous section that did not use placeholders, to see which you prefer.

2.7.4 Perl

To use placeholders in DBI scripts, put a ? in your query string at each location where you want to insert a data value, then bind the values to the query. You can bind values by passing them to do( ) or execute( ), or by calling a DBI method specifically intended for placeholder substitution.

With do( ), pass the query string and the data values in the same call:

my $count = $dbh->do ("UPDATE profile SET color = ? WHERE name = ?",
 undef,
 "green", "Mara");

The arguments after the query string should be undef followed by the data values, one value for each placeholder. (The undef argument that follows the query string is a historical artifact, but must be present.)

With prepare( ) plus execute( ), pass the query string to prepare( ) to get a statement handle. Then use that handle to pass the data values via execute( ):

my $sth = $dbh->prepare ("UPDATE profile SET color = ? WHERE name = ?");
my $count = $sth->execute ("green", "Mara");

You can use placeholders for SELECT statements, too. The following query looks for records having a name value that begins with "M":

my $sth = $dbh->prepare ("SELECT * FROM profile WHERE name LIKE ?");
$sth->execute ("M%");
while (my $ref = $sth->fetchrow_hashref ( ))
{
 print "id: $ref->{id}, name: $ref->{name}, cats: $ref->{cats}
";
}
$sth->finish ( );

A third way of binding values to placeholders is to use the bind_param( ) call. It takes two arguments, a placeholder position and a value to be bound to the placeholder at that position. (Placeholder positions begin with 1, not 0.) The previous two examples can be rewritten to use bind_param( ) as follows:

my $sth = $dbh->prepare ("UPDATE profile SET color = ? WHERE name = ?");
$sth->bind_param (1, "green");
$sth->bind_param (2, "Mara");
my $count = $sth->execute ( );

my $sth = $dbh->prepare ("SELECT * FROM profile WHERE name LIKE ?");
$sth->bind_param (1, "M%");
$sth->execute ( );
while (my $ref = $sth->fetchrow_hashref ( ))
{
 print "id: $ref->{id}, name: $ref->{name}, cats: $ref->{cats}
";
}
$sth->finish ( );

No matter which method you use for placeholders, don't put any quotes around the ? characters, not even for placeholders that represent strings. DBI adds quotes as necessary on its own. In fact, if you do put quotes around the placeholder character, DBI will interpret it as the literal string constant "?", not as a placeholder.

The high-level retrieval methods such as selectrow_array( ) and selectall_arrayref( ) can be used with placeholders, too. Like the do( ) method, the arguments are the query string and undef, followed by the data values to be bound to the placeholders that occur in the query string. Here's an example:

my $ref = $dbh->selectall_arrayref (
 "SELECT name, birth, foods FROM profile
 WHERE id > ? AND color = ?",
 undef, 3, "green");

Generating a List of Placeholders

When you want to use placeholders for a set of data values that may vary in size, you must construct a list of placeholder characters. For example, in Perl, the following statement creates a string consisting of n placeholder characters separated by commas:

$str = join (",", ("?") x n);

The x repetition operator, when applied to a list, produces n copies of the list, so the join( ) call joins these lists to produce a single string containing n comma-separated instances of the ? character. This is handy when you want to bind an array of data values to a list of placeholders in a query string, because the size of the array indicates how many placeholder characters are needed:

$str = join (",", ("?") x @values);

Another method of generating a list of placeholders that is perhaps less cryptic looks like this:

$str = "?" if @values;
$str .= ",?" for 1 .. @values-1;

Yet a third method is as follows:

$str = "?" if @values;
for (my $i = 1; $i < @values; $i++)
{
 $str .= ",?";
}

That method's syntax is less Perl-specific and therefore easier to translate into other languages. For example, the equivalent method in Python looks like this:

str = ""
if len (values) > 0:
 str = "?"
for i in range (1, len (values)):
 str = str + ",?"

2.7.5 PHP

PHP provides no support for placeholders. See Recipe 2.9 to find out how to construct queries that refer to data values that may contain special characters. Or see Recipe 2.10, which develops a class-based interface for PHP that emulates placeholders.

2.7.6 Python

Python's MySQLdb module implements the concept of placeholders by using format specifiers in the query string. To use placeholders, invoke the execute( ) method with two arguments: a query string containing format specifiers, and a sequence containing the values to be bound to the query string. The following query uses placeholders to search for records where the number of cats is less than 2 and the favorite color is green:

try:
 cursor = conn.cursor ( )
 cursor.execute ("SELECT * FROM profile WHERE cats < %s AND color = %s", 
 (2, "green"))
 for row in cursor.fetchall ( ):
 print row
 print "%d rows were returned" % cursor.rowcount
 cursor.close ( )
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

If you have only a single value val to bind to a placeholder, you can write it as a sequence using the syntax (val,). The following UPDATE statement demonstrates this:

try:
 cursor = conn.cursor ( )
 cursor.execute ("UPDATE profile SET cats = cats+1 WHERE name = %s", 
 ("Fred",))
 print "%d rows were updated" % cursor.rowcount
except MySQLdb.Error, e:
 print "Oops, the query failed"
 print e

Some of the Python DB-API driver modules support several format specifiers (such as %d for integers and %f for floating-point numbers). With MySQLdb, you should use a placeholder of %s to format all data values as strings. MySQL will perform type conversion as necessary. If you want to place a literal % character into the query, use %% in the query string.

Python's placeholder mechanism provides quotes around data values as necessary when they are bound to the query string, so you need not add them yourself.

2.7.7 Java

JDBC provides support for placeholders if you use prepared statements rather than regular statements. Recall that the process for issuing regular statements is to create a Statement object and then pass the query string to one of the query-issuing functions executeUpdate( ), executeQuery( ), or execute( ). To use a prepared statement instead, create a PreparedStatement object by passing a query string containing ? placeholder characters to your connection object's prepareStatement( ) method. Then bind your data values to the statement using setXXX( ) methods. Finally, execute the statement by calling executeUpdate( ), executeQuery( ), or execute( ) with an empty argument list. Here is an example that uses executeUpdate( ) to issue a DELETE query:

PreparedStatement s;
int count;
s = conn.prepareStatement ("DELETE FROM profile WHERE cats = ?");
s.setInt (1, 2); // bind a 2 to the first placeholder
count = s.executeUpdate ( );
s.close ( ); // close statement
System.out.println (count + " rows were deleted");

For a query that returns a result set, the process is similar, but you use executeQuery( ) instead:

PreparedStatement s;
s = conn.prepareStatement ("SELECT id, name, cats FROM profile"
 + " WHERE cats < ? AND color = ?");
s.setInt (1, 2); // bind 2 and "green" to first and second placeholders
s.setString (2, "green");
s.executeQuery ( );
// ... process result set here ...
s.close ( ); // close statement

The setXXX( ) methods that bind data values to queries take two arguments: a placeholder position (beginning with 1, not 0) and the value to be bound to the placeholder. The type of the value should match the type in the setXXX( ) method name. For example, you should pass an integer value to setInt( ), not a string.

Placeholder characters need no surrounding quotes in the query string. JDBC supplies quotes as necessary when it binds values to the placeholders.