Stored Procedures vs SQL Injection

There's a myth going around that using Stored Procedures when querying your database, prevents SQL Injection.

That myth needs to be nipped in the bud. Or at the very least, qualified.

What is SQL Injection?

It'll take me too long to explain in detail. I'll just say - SQL Injection occurs when an SQL query accepts user input as parameters, and the input turns out to be malicious SQL snippets which compromise your database.

For examples and a fuller explanation, try this.

So you're saying Stored Procedures don't prevent SQL injection?

No, I'm not saying that. Nobody's saying that. Stored Procedures, like any tool, can be used or misused. Stored Procedures can prevent SQL Injection. It's a matter of how they're used.

Let's have an example of how a normal query can be compromised. In this case, x and y represent the user input.

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' AND field2 = 'y';

So our table here is named table1 and this is its initial content.

field1	field2	field3
x	x	x
y	y	y
x	y	x
z	x	y
y	x	z
x	y	y

Running the query above would give you this result:

field1	field2	field3
x	x	x
y	y	y
x	y	z
z	x	y
y	x	z
x	y	z

Inject something into it. Try "newvalue';DELETE FROM table1;"

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' AND field2 = 'newvalue';DELETE FROM table1;

And there you go. SQL Injection! This query first does an update, and then deletes the entire contents of table1!

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' and field2 = 'newvalue';

DELETE FROM table1;

Now, say you have a Stored Procedure, named sp_update, that does the same thing. For the purposes of this exercise, we'll be using SQLServer.

CREATE PROC [sp_update]
    @x VARCHAR(50),
    @y VARCHAR(50),
    @z VARCHAR(50)
AS
    UPDATE table1
    SET field3 = @z
    WHERE field1 = @x AND field2 = @y;
GO

Now try injecting the malicious code "newvalue';DELETE FROM table1;".

CALL sp_select("x","y","newvalue';DELETE FROM table1;");

field1	field2	field3
x	x	x
y	y	y
x	y	newvalue';DELETE FROM table1;
z	x	y
y	x	z
x	y	newvalue';DELETE FROM table1;

See that? Instead of executing the DELETE command, the Stored Procedure actually saves the value into the table table1! That is because it's treating the input specifically as a parameter rather than part of the command. More on that later.

However...

What if you do this?

CREATE PROC [sp_update]
    @x VARCHAR(50),
    @y VARCHAR(50),
    @z VARCHAR(50)
AS
    EXEC 'UPDATE table1 SET field3 = ''' + @z + ''' WHERE field1 = @x AND field2 = ''' + @y + '''';
GO

The above is also a Stored Procedure. But like any other SQL query, it can be compromised. Why? Because it's not parameterized. The entire string is executed as-is. It's on par with simply running the original SQL string.

So, the conclusion is...

Stored Procedures, by themselves, don't prevent SQL Injection. Parameterization does. Parameterized Stored Procedures prevent SQL Injection.

In fact, parameterized queries prevent SQL Injection as well. Try the following example in PHP (using the sqlsrv extension):

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="x";
$y="y";
$z="newvalue';DELETE FROM table1;";

$strsql="UPDATE table1 SET field3 =? WHERE field1 =? AND field2=?";

$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

field1	field2	field3
x	x	x
y	y	y
x	y	newvalue';DELETE FROM table1;
z	x	y
y	x	z
x	y	newvalue';DELETE FROM table1;

(You may want to read more about PHP's sqlsrv extension and prepared statements.)

Do you see what happened there? The same thing occurred with the parameterized Stored Procedure. It's actally parameterization that does the magic, not Stored Procedures per se.

Use both!

This may seem like overkill, but you can use both. Set up the Stored Procedure sp_update in your SQLServer database and then do the following:

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="x";
$y="y";
$z="newvalue';DELETE FROM table1;";

$strsql="CALL sp_update(?,?,?)";

$statement = sqlsrv_prepare($dbConn,$strsql,array(&$x,&$y,&$z));
sqlsrv_execute($statement);?>

Note that the parameters for the array are in a different order here (ie, xyz instead of zxy), because we're using the Stored Procedure sp_update and have to abide by its specifications.

So why use Stored Procedures at all?

Well, there are a lot of nice security features that come with Stored Procedures, not just prevention of SQL Injection. But that's out of scope. Perhaps another time!

Know your security measures. Otherwise some mischievous hacker will make you SQueaL!
T___T