Dynamic SQL request and SQL injection

[meriah]

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_executesql Or 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]

(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.

(2)

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.