SQL Injection Mitigations – Is SP_EXECUTESQL enough?

If you are writing Stored Procedures in SQL Server and your code got through a security code review, the chances are bright that you heard the terms “dynamic sql”, “sp_executesql” in the context of SQL Injection.
I found the details of what sp_executesql does in MSDN here. The msdn article starts with a Security note that reads “Run time-compiled Transact-SQL statements can expose applications to malicious attacks, such as SQL injection.”
This procedure, if used correctly, can help mitigate SQL Injection attacks to an extent.
From SQL Server books online, the syntax of sp_executesql looks like:
sp_executesql [ @statement = ] statement
    { , [ @params = ] N’@parameter_name data_type [ OUT | OUTPUT ][ ,…n ]’ }
     { , [ @param1 = ] ‘value1’ [ ,…n ] }
This method is useful ONLY when you use parameterization correctly and also that this method is no different from dynamic SQL if used with string concatenation. I wrote a quick set of code snippet (copied below) to check this:
The table I used is detailed in my blog entry here. As you can see a well crafted SQL query in the input can still get me the details not meant to be shown.
This can be avoided if I use a properly parameterized implementation of sp_executesql, detailed below.
As you can see when parameterization is used correctly even a crafted value cannot be used to manipulate the results returned by the query.
So use SP_EXECUTESQL does help mitigate the SQL Injection attacks only if parameterization is used properly.
I am trying to understand the internal workings of sp_executesql, for now [:)]!


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 )

Facebook photo

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

Connecting to %s