Suppose there is a procedure whereby a list of columns by which you need to perform sorting is input.

Can I transfer them safely here?

Declare @cmd nvarchar(255)='select * from someTable order by @columnList'

Understandably, I can replace the @columnList values, but there may be an injection.

Can this be done with sp_executesql or do I need to parse the list with handles and frame everything in [column]?

An example of not fighting, just became interesting.

    2 answers 2

    1) About sp_executesql

    Parameters that are set in sp_execsql are substituted into a dynamic query as constants. Those. SQL injection is not possible here. If in request

     SELECT * FROM T ORDER BY @param

    We substitute the value of the parameter with something like "(SELECT 1) DELETE FROM ImportantTable"

    then in the end we get the request:

     SELECT * FROM T ORDER BY '(SELECT 1) DELETE FROM ImportantTable'

    Those. innocuous sorting by a constant that the optimizer will throw out.

    2)

    As for the fact that you are allowed to send SQL code with a list of parameters. Then you just need to check with pens that there are no SQL injections there.

    In the case of sorting, you can safely manage, for example, with a set of optional parameters that will give the user of your procedure the necessary flexibility to sort the output.

    If for some reason you still accept some kind of SQL code as input, be ready to manually process it for unwanted actions.

      Can I transfer them safely here?

      It will be safe to pass in the procedure for sorting not SQL expressions, but certain column codes that already have table columns associated within the procedure (in the general case, expressions).

      In practice, it may look like this.

      Suppose there is a table:

       create table dbo.Staff (id int, name varchar(20), groupNo int, skill int); insert into dbo.Staff values (1, 'Peter', 1, 30), (2, 'Ann', 1, 25), (3, 'Jim', 2, 20), (4, 'Steven', 2, 15), (5, 'Nicole', 3, 10);

      And the procedure should select data from it with the possibility of specifying various sorts:

       select name, groupNo, skill from dbo.Staff order by ... ;

      A table type is created to pass the column codes to the procedure:

       create type dbo.OrderList as table ( columnNo int not NULL primary key, columnCode varchar(100) not NULL unique, isDesc bit NULL default(0) );

      A procedure that retrieves data builds a dynamic query, substituting expressions corresponding to a specific column code:

       create procedure dbo.GetStaff ( @orderBy dbo.OrderList readonly ) as begin set nocount on; declare @sql nvarchar(max) = ' select s.name, s.groupNo, s.skill from dbo.Staff s '; declare @sqlOrderBy nvarchar(max); set @sqlOrderBy = stuff(( select ', ' + c.expr + iif(o.isDesc = 1, ' desc', '') from @orderBy o join (values ('Name', 's.name'), ('Group', 's.groupNo'), ('Skill', 's.skill') ) c(code, expr) on c.code = o.columnCode order by columnNo for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, ''); set @sql += isnull('order by ' + @sqlOrderBy + ';', ';'); exec sp_executesql @sql; end

      Then the procedure can be called by specifying one or more column codes to sort in the desired order, for example:

       declare @orderBy dbo.OrderList insert into @orderBy (columnNo, columnCode) values (1, 'Name') exec dbo.GetStaff @orderBy;

      or

       declare @orderBy dbo.OrderList insert into @orderBy values (1, 'Group', 0), (2, 'Skill', 1) exec dbo.GetStaff @orderBy;

      and even so

       exec dbo.GetStaff;

      Similarly, you can build a procedure for a dynamic set of columns not in order by , but for example in select , or both.