alternative to exec sql to avoid sql injection is SP_EXECUTESQL?

Question

i have to connect to an existing stored procedure which saves a line of text in the database. now i've seen the sp and it is a concatenated string (in which my text is placed) and the it is executed with exec(@sql).

Because the text is coming from a public website, i have to make sure no sql injection is possible.

The database guy had not heard of sql injection, so he doesn't know what to do, but as i don't want to expose the risk from my aplication, i would like to know what can be done to prevent the sql injection. I am however more programmer than sql guy.

Now i've found the 'SP_EXECUTESQL(@SQL)' stored procedure, will that help prevent sql injection, or is it still possible to make malicious calls?

thanks in advance for helping me.

Any chance of showing us the SQL statement (sanitised for public consumption of course)? If you are already using parameters, then you are probably safe. — Tom Chantler
it turned out the sql guy could easily replace the exec(@sql) by a normal stored proc. — Michel

Tom Chantler Tom Chantler · Accepted Answer · 2011-06-29T07:50:30

Look at the MSDN documentation for SP_EXECUTESQL(@SQL) here: http://msdn.microsoft.com/en-us/library/ms188001.aspx.

It warns that

Run time-compiled Transact-SQL statements can expose applications to malicious attacks, such as SQL injection.

Also have a look here: http://msdn.microsoft.com/en-us/library/ms175170.aspx

I would strongly advise against trusting ANY input from ANY user!

Can you change the query to be parameterised in some way?

alternative to exec sql to avoid sql injection is SP_EXECUTESQL?

1 Answers