SQL Server stored procedure select specific columns passed by arguments, and check other constraints

Question

I want to create a stored procedure in SQL Server 2017 and let it be called somewhere else (i.e., Python). It accepts three parameters, stkname - stock name, stktype - stock type, colname - output columns (only these columns are returned). @colname is a varchar storing all required column names separated by ,.

• If @colname is not specified, return all columns (*)
• If stkname or stktype is not specified, do not filter by it

This is my code so far:

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

The desired result is that

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

returns two columns - ticker and price, for all records with stktype = 'A'

I could imagine 'dynamic SQL' would be possible, but not that 'elegant' and I need to write 2*2*2 = 8 cases.

How can I actually implement it in a better way?

PS: This problem is not a duplicate, since not only I need to pass column names, but also 'generate a query by other parameters'

Answer 1

You need dynamic SQL, but you don't need to write a case for every permutation, and it's really not all that hard to prevent malicious behavior.

CREATE PROCEDURE dbo.demo -- always use schema prefix
  @stkname  varchar(max)  = NULL,  
  @colnames nvarchar(max) = NULL, -- always use Unicode + 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

It's not clear if the stkname and stktype columns really are varchar(max), I kind of doubt it, you should replace those declarations with the actual data types that match the column definitions. In any case, with this approach the user can't pass nonsense into the column list unless they somehow had the ability to add a column to this table that matched their nonsense pattern (and why are they typing this anyway, instead of picking the columns from a defined list?). And any data they pass as a string to the other two parameters can't possibly be executed here. The thing you are afraid of is probably a result of sloppy code where you carelessly append user input and execute it unchecked, like this:

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

For more on malicious behavior see:

• Protecting Yourself from SQL Injection in SQL Server - Part 1
• Protecting Yourself from SQL Injection in SQL Server - Part 2