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:


Create PROC [sp_demo_injection02]
(
@tablename varchar(50),
@username varchar(50),
@lastname varchar (50)
)
AS
  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)
  set @cmd = N'SELECT * FROM '+ @tablename +' WHERE username = '+''''+ @username+''''
  set @cmd = @cmd+' and lastname= '+''''+@lastname+''''
  --print @cmd
  Exec @cmd
go

In runtime sql query for this
exec [sp_demo_injection02] 'tblUser','bkm','mahto'
generated like
SELECT * FROM tblUser WHERE username = 'bkm' and lastname= 'mahto'

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:

Create PROC [sp_demo_injection02](
@tablename varchar(50),
@username varchar(50),
@lastname varchar (50)
)
AS
  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)

  set @cmd = N'SELECT * FROM '+ quotename(@tablename)+' WHERE username = @username and @lastname=lastname' --Make use of quotename for sql object

 set @parameters = '@username varchar(50), @lastname varchar (50)' --Here goes all parameters except the sql object(table name, sp name)
  
EXEC sp_executesql @cmd, @parameters, @username = @username, @lastname = @lastname
go


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

3. I converted all my string parameter to sql parameter.

set @parameters = '@username varchar(50), @lastname varchar (50)'

4. Executing my dynamic query using sp_executesql which executes my dynamic sql query by accepting parameters.
For more Info: http://msdn.microsoft.com/en-us/library/ms188001.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.

1. Must do server side validation and escape or filter the special characters from user inputs.
2. Always use store procedures whenever possible.
3. Always use parameters with stored procedures or dynamic queries inside stored procedures.
4. Always use least privileged account (like read-only) to execute queries.
5. Avoid disclosing error (exception) details to the user, instead use customize error information for client. Because exception details may have critical information (like code logic, server info., etc)

Thanks.