Search code examples
sqlsql-serversql-injection

How to prevent sql injection in dynamic SQL

This is a old topic, but I am still having problem with it, so want to get some new idea here.

I used to check | in parameter, but it seems now ; is a separator to check too.

It suggests to use sp_executesql with parameters to prevent SQL injection, but I am not sure if I can do that.

What I try to do is collect filters from client side and run dynamic SQL to get result, for example, from client side, I could send a request with the below filters to SQL:

id=1234 name=david date=2014/01/01

I will create dynamic sql like

select * 
from members 
where id = 1234 
  and name like 'david%' 
  and crea_date = '2014/01/01'

The search column could be any random list of field of a table, so I cannot run sp_executesql like

sp_executesql N'select * from members where id=@id and name like @name and crea_date=@crea_date',N'@id int,@name nvachar(100),@crea_date datetime', ....

Any suggestions?

Solution

  • You can use parameters, no problem. Make the SQL dynamic, but make the generated SQL refer to parameters. Unconditionally pass in a @name parameter if you need to, just make the name like @name part of your where clause dynamic. If you don't search on name, simply ignore the parameter in your SQL.

    If you do not want to hard code the parameter names, name them param1, param2 etc.

    Depending on how you are executing it, you may even be able to get rid of the dummy parameters when you aren't using them.