SQL Injection, Part 5 of 8: Stored Procedures

This will be a very quick bit part, as I have already discussed most of the details here in earlier parts.  This is focusing only on stored procedures which are not run by calling sp_executesql or exec(@sql).

True or false:  static stored procedures (as opposed to dynamic SQL stored procedures) are invulnerable to SQL injection.  Answer:  it depends.  Well, kinda…

The thing is that static stored procedures are themselves invulnerable, but it is possible still to perform a SQL injection attack even with the procedure.  Let’s run through a few examples, still using AdventureWorks. First, we create a test procedure:

create procedure GetProductSubcategoryByName
	@ProductSubcategoryName nvarchar(max)
AS
select
	ProductSubcategoryID,
	ProductCategoryID,
	Name,
	rowguid,
	ModifiedDate
from
	Production.ProductSubcategory
where
	Name = @ProductSubcategoryName;

Run this and we have a procedure we can attempt to attack. So let’s hit it. The following examples will all fail to perform an attack.

exec GetProductSubcategoryByName N''' OR 1 = 1--';
exec GetProductSubcategoryByName N''' UNION ALL select 1, 2, 3, 4, 5--';
exec GetProductSubcategoryByName N'''declare @shmooi varchar(8000); set @shmooi = CAST([binary truncated so you can read this more easily] as varchar(8000)); exec(@shmooi);'

A SQL Profiler trace watching several attempts at SQL injection fail to return results.

A SQL Profiler trace watching several attempts at SQL injection fail to return results.

Instead, all we can do is perform searches. The reason is that there is no way to break out of the parameter and manipulate the query itself. The only way we can “break out” of the parameter causes the query to fail due to a syntax error.

A syntax error is returned after yet another attempt at breaking out of the SQL query inside the stored procedure.

A syntax error is returned after yet another attempt at breaking out of the SQL query inside the stored procedure.

This is not a trivial result—it means that we can replace ad hoc SQL in our data layer with calls to stored procedures. Now, people who read the last part know that it’s perfectly fine to write ad hoc SQL calls which are invulnerable to SQL injection attack: you simply use parameters. This is true, and is part of why I would not use SQL injection as a core reason for supporting stored procedures over ad hoc SQL in a .NET environment. Instead, I would argue that it is relatively easier to prevent bad habits from forming if you consistently use stored procedures. Parameterizing your queries is an essential part of protection against SQL injection, and stored procedures almost force you to use parameterization, or at least think about using parameters in your data layer. Unlike ad hoc SQL, they do not let you pass in unsanitized data all that easily. In fact, you have to do some pretty stupid things, like the following:

                //HINT:  If you are doing this, you're doing it wrong.
                using (SqlConnection conn = new SqlConnection("server=.;database=AdventureWorks;trusted_connection=yes"))
                {
                    //.NET 1.1 called--they want their data adapter back.
                    DataSet ds = new DataSet();
                    SqlDataAdapter sda = new SqlDataAdapter("dbo.GetProductSubcategoryByName '" + Name + "'", conn);
                    //Problem #1:  running a stored procedure as a regular SQL query.
                    //sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    //With this in place, at least the query would fail.
                    sda.Fill(ds);

                    gvGrid.DataSource = ds;
                    gvGrid.DataBind();
                }

In the above code, “Name” is a string which was retrieved from a form’s text field, like in our prior examples. If you are using anything newer than .NET 1.1, you should not be doing this; use SqlDataReaders (or something else) instead, and certainly parameterize your queries.

Also, in the code, I put in a comment noting that you need to be extra-careless in order to get this thing to fail the way we want it to: if you uncomment the code line, this will cause the SqlDataAdapter to throw an exception, as it expects a stored procedure, but actually get a stored procedure followed by some crafted attack SQL.

So I did want to show you that there is, in fact, a way to exploit even a system which uses nothing but static stored procedures. In the .NET world, this is a bit more difficult than doing it the right way, but having stored procedures does not, by itself, make you invulnerable to SQL injection attacks.

One thought on “SQL Injection, Part 5 of 8: Stored Procedures

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s