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 [:)]!
Month: April 2010
SQL Injection – the way I learnt it
I am writing some T-SQL code for one of the assignments and am told to watch out for SQL injection as a possible attack vector.
So I took a look around to see what it is and how it really works from a very high level. A good resource to start with SQL Injection (for that matter any attack) is the OWASP.
To try hands on with how dynamic SQL executed using the Execute statement, I created a dummy Database in my local SQL Server instance and created some tables on it. For the purpose of this write up, I will only use one of the tables in the DB that I call “ZipCodes”. There are three dummy records in the table and here is the snapshot:
I created a Stored procedure to get me the record count from this table. Here is the code:
As you see I do not do anything fancy. The Stored procedure takes some parameters and then constructs a sql statement, @SQL which is then executed.
I execute the stored procedure using the following statement to confirm that the procedure is working just fine:
To check whether the stored procedure is validating the input parameters, I inserted the following value as part of in execute statement:
| Exec [dbo].[StoredProcedureToCheckForSQLInjection] ””, ‘ABC’, 123, ‘ZipCodes’` |
Well that irritated my SQL Server and the Stored procedure cried out the following error:
Look at the query that the SP tried to execute (that’s why I used the Print statement in the stored procedure code).
Ok I am on the right track and this procedure is a possible candidate of an injection attacks. I as an attacker will know this looking at the result above which shows that:
1. The Stored procedure is NOT validating the inputs.
2. The stored procedure is doing something by concatenation (Remember that I as an attacker will not have access to the SP code and hence it will be an analysis of the result/error above that will give me these details.)
That’s good news. So can I get all the records in this table? Lets check out using a crafted input that looks like the one below:
Once this query is run, the result that is thrown back is below:
Well that is not what the SP is supposed to do.
Lets check the query that the SP executed to get to the result above:
This is a very very very simple scenario and hopefully all the smart developers out there are not writing code like this in there Stored Procedures. But since I just started and it took me a while to get my query going, I thought of putting this here for reference.
I will comeback to this with more tricky cases. Till then its Happy Learning to me!!
Random Thoughtz 