Динамический SQL запрос и SQL-инъекция

#sql #база_данных #sql_server

Допустим есть процедура, которой на вход подается список столбцов по которым необходимо
выполнить сортировку.

Могу ли я их безопасно передать сюда ?

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


Понятное дело, я могу сделать Replace значения @columnList, но тут может быть инъекция.

Можно ли такое провернуть через sp_executesql или нужно парсить ручками список и
обрамлять все в [column]?

Пример не боевой, просто, интересно стало.
    

Ответы

Ответ 1

1) Про sp_executesql

Параметры, которые задаются в sp_execsql подставляются в динамический запрос как
константы. Т.е. SQL-инекция тут невозможна.
Если в запросе 

SELECT * FROM T
ORDER BY @param


Мы подставим значение параметра что-то вроде "(SELECT 1) DELETE FROM ImportantTable"

то в итоге получим запрос:

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


Т.е. безобидную сортировку по константе, которую оптимизатор выкинет.

2)

Что же касается того, что вы разрешаете присылать SQL код со списком параметров.
То тут только ручками нужно провалидировать, что там нет SQL-инъекций.

В случае с сортировкой, безопасно можно обойтись, например, набором необязательных
параметров, которые дадут пользователю вашей процедуры нужную гибкость сортировки вывода.

Если вы по каким-то причинам всё же принимаете на вход какой-то SQL код - будьте
готовы к его ручной обработке на наличие нежелательных для вас действий.


Ответ 2

Могу ли я их безопасно передать сюда ?


Безопасно будет в процедуру передавать для сортировки не SQL-выражения, а некие коды
столбцов, которым уже внутри процедуры сопоставлены столбцы таблицы (в общем случае
- выражения).

На практике это может выглядеть следующим образом.

Допустим есть таблица:

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);


И процедура должна выбирать из неё данные с возможностью указания различных сортировок:

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


Создаётся табличный тип для передачи кодов столбцов в процедуру:

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


Процедура, которая достаёт данные, строит динамический запрос, подставляя выражения,
соответствующие конкретному коду столбца:

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


Затем процедуру можно вызывать, задавая один или несколько кодов столбцов для сортировки
в нужном порядке, например:

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


или

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


и даже так

exec dbo.GetStaff;


Аналогично можно построить процедуру для динамического набора столбцов не в order
by, а например в select, или и то и другое.