Pseudoremedy 2: Use Stored Procedures

Pseudoremedy #2: Use Stored Procedures

Many developers believe that calling stored procedures from an application also makes the application immune to SQL injection attacks. Wrong! Doing so prevents some kinds of attacks and not others. Here's some sample code that calls a stored procedure named sp_GetName:

string name = ...; // name from user SqlConnection sql= new SqlConnection(...); sql.Open(); sqlstring=@"exec sp_GetName '" + name + "'"; SqlCommand cmd = new SqlCommand(sqlstring,sql);

Attempting to enter Blake' or 1=1 -- will fail because you cannot perform a join across a stored procedure call. The following is illegal SQL syntax:

exec sp_GetName 'Blake' or 1=1 -- '

However, performing data manipulation is perfectly valid:

exec sp_GetName 'Blake' insert into client values(1005, 'Mike') -- '

This SQL command will fetch data about Blake and then insert a new row into the client table! As you can see, using stored procedures doesn't make your code secure from SQL injection attacks.

I have to admit, the scariest example of using stored procedures for security reasons is a stored procedure that looks like this:

CREATE PROCEDURE sp_MySProc @input varchar(128) AS     exec(@input)

Guess what this code does? It simply executes whatever the user provided, even though the code is calling a stored procedure! Luckily, I've seen this only a couple of times.

As you can see, you need to be aware of pseudo remedies they might help a little, but none of them are safe. Now let's switch tactics and look at real remedies.