Friday, 14 December 2012

Avoid SQL Injection in dynamic SQL query inside stored procedure.

Frnds,

I had a requirement where I had to write a stored procedure which will give the data back to the user. Looks very simple but the interesting part was that, Inside SP the table name was coming as a parameter with other parameters.
I went ahead created stored procedure something like shown below here:

but when I run the same SP with below parameter I was shocked:[sp_demo_injection02] 'tblUser;Drop table tblUser;--','bkm','mahto'
Output generated query:SELECT * FROM tblUser;Drop table tblUser;-- WHERE username = 'bkm' and lastname= 'mahto'

Let's see here what I did, Basically I have given a way to a hacker to delete/or do anything serious using my query. Above query will drop my table and this is a big security risk.

Above query is just an example, In real time it could be a big disaster. So this is called SQL Injection.

Now I must need to re-write my stored procedure to avoid this sql injection, so this is what I did:
I changed my above SP like this:

Let me explain what i did here:
1. I removed concatenation for table name and parameters which was the biggest culprits here.

2. Also I used quotename(@tablename) : quotename is a sql function which will convert my @tablename string value to this [tblUser]. So any string withing bracket is considered as a sql object (table, stored proc, view...). this method can be apply to a string for 128 char long.for more info:http://msdn.microsoft.com/en-us/library/ms176114.aspx

In this way you can be sure of any sql attacks for your dynamic sql query. But for best practices you also must do following things:
Implement strong server side validation for all user inputs including cookie values.

Must do server side validation and escape or filter the special characters from user inputs.