Хранимая процедура SQL Server выбирает определенные столбцы, передаваемые аргументами, и проверяет другие ограничения

Question

Я хочу создать хранимую процедуру в SQL Server 2017 и позволить ей вызываться где-то еще (например, Python). Он принимает три параметра, stkname - название акции, stktype - тип акций, colname - выходные столбцы (возвращаются только эти столбцы). @colname это varchar хранение всех необходимых имен столбцов, разделенных ,,

• Если @colname не указан, вернуть все столбцы (*)
• Если stkname или же stktype не указано, не фильтровать по нему

Это мой код до сих пор:

create procedure demo 
     (@stkname varchar(max), 
      @colname varchar(max), 
      @stktype varchar(max)) 
as
begin
    ----These lines are pseudo code -----
    select 
        (if @colname is not null, @colname, else *) 
    from 
        Datatable 
    (if @stkname is not null, where stkname = @stkname)
    (if @stktype is not null, where stktype = @stktype)

    ---- pseudo code end here-----
end

Желаемый результат заключается в том, что

exec demo @colname= 'ticker,price', @stktype = 'A'

возвращает два столбца - тикер и цену для всех записей с stktype = 'A'

Я мог предположить, что "динамический SQL" был бы возможен, но не такой "элегантный", и мне нужно написать 2*2*2 = 8 случаев.

Как я могу на самом деле реализовать это лучше?

PS: эта проблема не является дубликатом, поскольку мне нужно не только передавать имена столбцов, но и "генерировать запрос по другим параметрам"

Answer 1

Вам нужен динамический SQL, но вам не нужно писать регистр для каждой перестановки, и на самом деле не так уж и сложно предотвратить злонамеренное поведение.

CREATE PROCEDURE dbo.demo -- always use schema prefix
  @stkname  varchar(max)  = NULL,  
  @colnames nvarchar(max) = NULL, -- always use Unicode and use a proper name
  @stktype  varchar(max)  = NULL
AS
BEGIN
   DECLARE @sql nvarchar(max);

   SELECT @sql = N'SELECT ' + STRING_AGG(QUOTENAME(LTRIM(RTRIM(x.value))), ',') 
     FROM STRING_SPLIT(@colnames, ',') AS x
     WHERE EXISTS 
     (
        SELECT 1 FROM sys.columns AS c 
        WHERE LTRIM(RTRIM(x.value)) = c.name
        AND c.[object_id] = OBJECT_ID(N'dbo.DataTable')
     ); 

   SET @sql += N' FROM dbo.DataTable WHERE 1 = 1'
     + CASE WHEN @stkname IS NOT NULL THEN N' AND stkname = @stkname' ELSE N'' END
     + CASE WHEN @stktype IS NOT NULL THEN N' AND stktype = @stktype' ELSE N'' END;

  EXEC sys.sp_executesql @sql,
     N'@stkname varchar(max), @stktype varchar(max)',
     @stkname, @stktype;
END

Не ясно, если stkname а также stktype столбцы действительно varchar(max)Я сомневаюсь, что вы должны заменить эти объявления фактическими типами данных, которые соответствуют определениям столбцов. В любом случае, при таком подходе пользователь не может передавать бессмыслицу в список столбцов, если у него нет какой-либо возможности добавить в эту таблицу столбец, который соответствует их образцу бессмысленности (и почему они все равно вводят это вместо того, чтобы выбирать столбцы). из определенного списка?). И любые данные, которые они передают в виде строки двум другим параметрам, не могут быть выполнены здесь. То, чего вы боитесь, вероятно, является результатом неаккуратного кода, когда вы небрежно добавляете пользовательский ввод и выполняете его без проверки, например так:

SET @sql = N'SELECT ' + @weapon_from_user + '...';

Подробнее о злонамеренном поведении смотрите:

• Защита от SQL-инъекций в SQL Server - часть 1
• Защита от внедрения SQL в SQL Server - часть 2