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', 'email@example.com');
DROP TABLE CustomerTable;
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:
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
firstname.lastname@example.org'; 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.