2.7 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
placeholders�special
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}\n";
}
$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}\n";
}
$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");
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.
| 
No comments:
Post a Comment