How and Why to Use Parameterized Queries

First published on MSDN on Sep 30, 2008

I recently attended ZendCon 08 in Santa Clara, CA with David Sceppa, Program Manager for the SQL Server 2005 Driver for PHP. We hosted a "Birds of a Feather" (BOF) session and were happy to get feedback and field lots of good questions from developers who are using our driver. During the session, our discussion touched on how and why to use parameterized queries. The focus of this post is to recap that conversation and to demonstrate how to use parameterized queries with the SQL Server 2005 Driver for PHP.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks.

Let's take a look at what can happen if we don't use parameterized queries. Consider the following code that concatenates user input with SQL syntax:

$name = $_REQUEST['name'];

$email = $_REQUEST['email'];

$sql = "INSERT INTO CustomerTable (Name, Email)

VALUES ('$name', '$email')";

Now suppose a user enters the following data:

The resulting SQL query (defined by $sql) is the following:

INSERT INTO CustomerTable (Name, Email)
VALUES ('Brian', 'bswan@microsoft.com');
DROP TABLE CustomerTable;
PRINT 'Gotcha!'--')

This is a perfectly valid SQL query, and, as you can see, the results of executing this on the server (with a function such as sqlsrv_query) would not be desired. This does assume that the user has some knowledge of your database (or that he guessed the table name correctly) and that credentials used to access the server have sufficient permissions to drop a table. However far-fetched these assumptions may seem, when you construct SQL queries by concatenating user input with SQL syntax you run the risk of the user supplying input that may cause your query to do something that you had not expected.

The simplest and most effective way to avoid the scenario described above is to use parameterized queries. Here is how the code above would look when using a parameterized query:

$name = $_REQUEST['name'];
$email = $_REQUEST['email'];
$params = array($name, $email);
$sql = 'INSERT INTO CustomerTable (Name, Email) VALUES (?, ?)';

Now, to execute the query, we just pass an open connection ($conn), the SQL query ($sql), and the parameter array ($params) to the sqlsrv_query function:

$stmt = sqlsrv_query($conn, $tsql, $params);

(The sqlsrv_query function returns a PHP statement resource.)

The difference here (as opposed to concatenating user input with SQL syntax) is that a query plan is constructed on the server before the query is executed with parameter values. In other words, a query plan is constructed on the server for this query:

INSERT INTO CustomerTable (Name, Email) VALUES (?, ?)

When you execute this query using parameterized values and the same user input , only the INSERT query is executed. The server accepts the user input of

bswan@microsoft.com'; DROP TABLE CustomerTable; PRINT 'Gotcha!'--

and inserts that entire value into the Email field. Using a parameterized query prevents the user input from leading to SQL injection. Plus, using a parameterized query allows you to handle less malicious scenarios, such as where the user supplies a value like "O'Leary" without forcing you to replace single quotes with double single quotes.

For more information about avoiding SQL injection attacks, see SQL Injection . For more information about how to execute parameterized queries, see How to: Perform Parameterized Queries , How to: Execute a Single Query , and How to: Execute a Query Multiple Times in the driver documentation.

Regardless of which database server or driver you use, a best practice for writing code is to use parameterized queries for security reasons.

Please let me know if this post has been helpful, and/or post questions or other comments.

Thanks.

Brian Swan
Programming Writer, SQL Server Driver for PHP