Dynamic SQL request and SQL injection
Let's say there's a procedure for the entry of the list of columns for which the sorting is necessary.
Can I send them here safely?
Declare @cmd nvarchar(255)='select * from someTable order by @columnList'
Clearly, I can make Replacement @columnList, but there might be an injection.
Can you do that?
sp_executesqlOr do you need to get your hands on the list and put it all in [column]?
It's not a fight, it's just, it's interesting.
Marcee last edited by
(1) Pro sp_executesql
Parameters that are sent to sp_execsql are placed in a dynamic request as a constant. I mean, SQL is impossible. If on request
SELECT * FROM T ORDER BY @param
We'll set the parameter value for something like "(SELECT 1) DELETE FROM ImportantTable."
Then we'll eventually get a request:
SELECT * FROM T ORDER BY '(SELECT 1) DELETE FROM ImportantTable'
I mean, a harmless sorting on a constant that the optimist will throw away.
As for what you're allowing to send SQL a code with a list of parameters. There's only a hand to fail that there's no SQL injection.
In the case of grading, it is safe to use, for example, a set of non-binding parameters that will give the user of your procedure the necessary flexibility to sort the withdrawal.
If, for some reason, you still accept some SQL code at the entrance-- be ready for his manual handling for undesirable actions for you.