MySQL Cookbook

Using Prepared Statements and Placeholders in Queries

Problem

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

Solution

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

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 ...