Is Dynamic SQL more vulnerable to SQL Injection/hacking?

Question

Is Dynamic SQL more vulnerable to SQL Injection/hacking? If yes, how to prevent?

Answer 1

If you use parameters instead of string concatenation to specify your filter-criteria, then it should not be vulnerable for Sql injection.

For instance:

do this:

string sqlQuery = "SELECT * FROM Persons WHERE Persons.Name LIKE @name";

SqlCommand cmd = new SqlCommand ( sqlQuery );
...
cmd.Parameters.Add ("@name", SqlDbType.VarChar).Value = aName + "%";

instead of this:

   string sqlQuery = "SELECT * FROM Persons WHERE Persons.Name LIKE \'" + aName + "%\'";

The first example is not vulnerable for sql injection, but the 2nd example is very much vulnerable.

The same applies for dynamic SQL that you use in stored procedures for instance. There, you can create a dynamic sql statement that uses parameters as well; You should then execute the dynamic statement using sp_executesql which enables you to specify parameters.

Answer 2

Quick answer is yes, if you're building Sql on the fly within yiour app, you have to be aware of every little trick that the rogues will try. When you're using stored procedures most of that will have been taken care of by your vendor.

A good way of reducing the chance of sql injection is to use parameter queries as above, if that's not appropriate make sure that any user generated field is stripped of non alpha characters. Take out quotes, semicolons etc. Also make sure you're connection only has enough access to do what it needs, if you're only querying data, then create a user/security group whatever that only allows select, not update and especially not delete. It can also be good practise to write the sql to a log - that way you know what people are doing, and you can tune, and spot injection attempts.

Answer 3

Inside TSQL, you should use sp_ExecuteSql to execute any dynamic commands you need (for example, to provide flexible searching/sorting).

Note that unless you jump through some hoops with certificates, you still need direct SELECT (etc) permission to the table (unlike a SPROC which cab provide access implicitly), but it should be injection safe. For example:

DECLARE @command nvarchar(4000), @name varchar(50)

SELECT @command = 'SELECT * FROM [CUSTOMER] WHERE [Name] = @Name',
       @name = 'Fred'

EXEC sp_ExecuteSql @command, N'@Name varchar(50)', @name

There is obviously no need to use dynamic SQL in the above - this is for illustration only! The main times this is useful is when (inside a SPROC) you have multiple optional search conditions, or a flexibly ORDER BY clause.

In non-TSQL clients, you can do the same with parameters to the command.

Note also that sp_ExecuteSql also makes use of the procedure cache, so can be more efficient than raw EXEC (@command).

Answer 4

It depends on how dynamic your query is.

If you mean storing a dynamic value then that isn't a problem as long as you use parameters as Frederik suggests.

If you mean building queries accoring to dynamic criteria then you may be in trouble :-)

Say for example that you have a string dictionary of fields to update as the key and the new value as the item. Then you can build an update query dynamically using the dictionary. Now if a hacker manages to change one of your field names he may manage to insert a custom query and thereby hack your system.

To avoid this you may be able to do some clever verification of the field names. Maybe checking them against the tables columns. But the safer option would be to use a fixed query that updates all values and giving it the original value for all columns that didn't change. This way you can use parameters for the values, which is safe, and you are safe against sql injection in the field names.

Take a look here for an interesting discussion around this topic.