How and Why to Use Parameterized Queries

http://blogs.msdn.com/b/sqlphp/archive/2008/09/30/how-and-why-to-use-parameterized-queries.aspx

 

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:

Image

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s