Do Stored Procedures Protect Against SQL Injection?
When I’ve asked people about their strategies for preventing SQL injection, one response is sometimes “I use stored procedures.” But, stored procedures do not, by themselves, necessarily protect against SQL injection. The usefulness of a stored procedure as a protective measure has everything to do with how the stored procedure is written. Write a stored procedure one way, and you can prevent SQL Injection. Write it another way, and you are still vulnerable. This post will look at one common pitfall that can leave stored procedures vulnerable to SQL injection.
For starters, let’s suppose we have the following (admittedly contrived) login script:
<form method="post" action="injection.php" enctype="multipart/form-data" >
Username:<input type="text" name="Username" id="Username"/></br>
Password:<input type="text" name="Password" id="Password"/></br>
<input type="submit" name="submit" value="Submit" />
</form>
<?php
if(isset($_POST['Username']))
{
$username = $_POST['Username'];
$password = $_POST['Password'];
// Connect to the server.
$server = "MyServer\sqlexpress";
$uid = "userName";
$pwd = "password";
$database = "ExampleDB";
$connectionoptions = array("Database" => $database
, "UID" => $uid
, "PWD" => $pwd);
$conn = sqlsrv_connect($server, $connectionoptions);
// Execute a parameterized query.
$params = array($username, $password);
$stmt = sqlsrv_query($conn, "{call VerifyUser( ?, ? )}", $params);
// If a row is returned, we have a username-password match.
if(sqlsrv_has_rows($stmt))
{
echo "Welcome.";
}
else
{
echo "Invalid password.";
}
}
?>
First, note that this example is very similar to the example I showed in this post: What’s the right way to prevent SQL Injection in PHP Scripts? Also note that it uses a parameterized query, which is a very important in preventing SQL injection (see the linked-to post). The difference here is that I’m using a stored procedure, VerifyUser, to determine if a user is valid.
Note: Calling stored procedures using canonical syntax (as shown in the example above) is the recommended practice when using the SQL Server Driver for PHP.. For more information about canonical syntax, see Calling a Stored Procedure.
As I said in the introduction, how that stored procedure is written can determine whether or not my script is vulnerable to SQL injection. Let’s take a look at two ways to write that stored procedure…
The wrong way
Suppose the VerifyUser stored procedure was created by dynamically building a SQL string within the stored procedure, like this:
CREATE PROCEDURE VerifyUser
@username varchar(50),
@password varchar(50)
AS
BEGIN
DECLARE @sql nvarchar(500);
SET @sql = 'SELECT * FROM UserTable
WHERE UserName = ''' + @username + '''
AND Password = ''' + @password + ''' ';
EXEC(@sql);
END
GO
Now, when I execute my PHP script with this input…
…the SQL that the stored procedure executes is this:
SELECT * FROM UserTable WHERE UserName = 'Brian' --' AND Password = 'any password'
The last half of the query is commented out! As long as my user name matches some user name in the database, I’m in.
By building the SQL query as a string in the stored procedure and concatenating parameter values in that string, I run the same risks that are inherent in concatenating parameter values in application code – I’m vulnerable to SQL injection. I admit that building a query dynamically as shown in the stored procedure above is somewhat contrived, but it is meant to show what is possible (and what NOT to do). Fortunately, avoiding the problem above is easy…
The right way
Now suppose the VerifyUser stored procedure was created like this:
CREATE PROCEDURE VerifyUser
@username varchar(50),
@password varchar(50)
AS
BEGIN
SELECT * FROM UserTable
WHERE UserName = @username
AND Password = @password;
END
GO
Now, an execution plan for the SELECT query exists on the server before the query is executed. The plan only allows our original query to be executed. Parameter values (even if they are injected SQL) won’t be executed because they are not part of the plan. So, if I submit a username like I did in the example above (Brian' --), it will be treated as user input, not SQL code. In other words, the query will look for a user with this password instead of executing unexpected SQL code.
The lesson is the same as it was in this post: don’t concatenate user input with SQL. Use prepared statements.
Once again, that’s my 2 cents.
Thanks.
-Brian