Securing Dynamic SQL Where Clause with Dynamic Column

Question

I am working on a project where I need to create a dynamic web page consist of a table which column structure and data depends on a stored procedure select result.

Here is examples of a stored procedure to use:

CREATE PROCEDURE dbo.usp_CustomerView
    @Address VARCHAR(1000)
AS
    SELECT *
    FROM Customer
    WHERE CustomerAddress LIKE '%' + @Address + '%'

This procedure is working when user filter address column in the table.

The problem is, this procedure is static. If I want to filter other fields like CustomerName or CustomerPhone, I need to create another parameter and remake the procedure like this.

CREATE PROCEDURE dbo.usp_CustomerView
    @Address VARCHAR(1000),
    @Name VARCHAR(1000),
    @Phone VARCHAR(1000)
AS
    SELECT *
    FROM Customer
    WHERE CustomerAddress LIKE '%' + @Address + '%'
      AND CustomerName LIKE '%' + @Name + '%'
      AND CustomerPhone LIKE '%' + @Phone + '%'

I am trying to create a dynamic procedure to execute a dynamic SQL query based on string filter like this.

CREATE PROCEDURE dbo.usp_CustomerView
    @Filter VARCHAR(MAX)
AS
    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'SELECT * FROM Customer WHERE ' + @Filter
    EXEC @sql

And then dynamically generate a string filter from my website like this.

Dim filterString As String = ""
For Each filter As DataFilter In e.Filter   'Here Filter give Array of filtered user input
    If Not String.IsNullOrEmpty(filterString) Then filterString &= " AND "
    filterString &= filter.Property & " = '" & filter.Value & "'"
Next
Command.CommandText = "usp_CustomerView @Filter"
Command.Parameters.AddWithValue("@Filter", filterString)
'Read Return Value from Command

This is working but I am concern with the security because it can easily injected. Anyone ever tried to create a dynamic where clause like this with a good security? I think this is a common things to do. But I can't find any clue.

Any suggestion?

More explanatios:

The implementation of this is actually more complex. I want to make a dynamic web page where I only set a string name of a stored procedure in database and that page will automatically generate a html table with the structure is obtained from executing the stored procedure. So I can't put any static query in the page.

Its simple to build a safe SQL string, the column names are fixed & should not come from user input at all, only the search values should. If a user wants to search for X then add a clause AND X = @X_PARAM and simply provide a value for @X_PARAM. — Alex K.
– Alex K., Commented Aug 1, 2018 at 11:13
@AlexK. yes that will be secure but it will require me to build different procedure with multiple parameter for each column of selected table. I want to create a template procedure where it only have one parameter for every procedure regardless of selected column. — Viki Theolorado
– Viki Theolorado, Commented Aug 1, 2018 at 11:16

Thom A · Accepted Answer · 2018-08-01 11:40:00Z

1

This is a bit of a stab in the dark, but how about a Table Type? You can then build you WHERE clause off that. Like I said, a stab in the dark, however, this might at least put you on the right path:

USE Sandbox;
GO

CREATE TYPE dbo.WhereClause AS TABLE (ColumnName sysname NOT NULL,
                                      ColumnValue sql_variant NOT NULL);
GO

CREATE PROC dbo.SampleProc @WhereClause WhereClause READONLY AS
BEGIN

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'SELECT *' + NCHAR(10) + 
               N'FROM Customer'

    SET @SQL = @SQL + 
               ISNULL(NCHAR(10) +N'WHERE' +
                      STUFF((SELECT NCHAR(10) + N'  AND ' + QUOTENAME(WC.ColumnName) + N' LIKE ''%'' + ' + QUOTENAME(CONVERT(varchar(100),ColumnValue),'''') + ' + ''%'''
                             FROM @WhereClause WC
                             FOR XML PATH(N'')),1,6,N''),N'') + N';';
    PRINT @SQL; --Your best debugging friend (provided you don't go over an nvarchar(4000))
    --EXEC sp_executesql @SQL; --Uncomment this to actually run the Dynamic SQL

END

GO

DECLARE @Where WhereClause;
INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerAddress', '123 test street';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerName', 'Joe Bloggs';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerPhone', '01234 567890';

INSERT INTO @Where (ColumnName,
                    ColumnValue)
SELECT N'CustomerDOB', CONVERT(date,'19810507');

EXEC dbo.SampleProc @Where;
/*
Returns:
SELECT *
FROM Customer
WHERE [CustomerAddress] LIKE '%' + '123 test street' + '%'
  AND [CustomerName] LIKE '%' + 'Joe Bloggs' + '%'
  AND [CustomerPhone] LIKE '%' + '01234 567890' + '%'
  AND [CustomerDOB] LIKE '%' + 'May  7 1981' + '%';

Note that the date isn't ideal. This *could* be a problem.
*/

 DELETE 
 FROM @Where;

 EXEC dbo.SampleProc @Where;
 /*
Returns:
SELECT *
FROM Customer;
*/
GO

DROP PROC dbo.SampleProc;
DROP TYPE dbo.WhereClause;
GO

answered Aug 1, 2018 at 11:40

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Viki Theolorado Over a year ago

Waw, this is working.. I tried to change the column value to be something like '''; PRINT(''INJECTED'')--'. Some how it still working. Thanks..

Thom A Over a year ago

@VikiTheolorado QUOTENAME is an incredibly useful function with dynamic SQL, and far too often forgotten about (or not used).

Dan Guzman · Accepted Answer · 2018-08-01 11:11:27Z

0

Consider building and executing a parameterized query in the app code using column and parameter names from a trusted source (hard coded or meta-data table). There is little value using a proc here.

answered Aug 1, 2018 at 11:11

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

3 Comments

Viki Theolorado Over a year ago

But the procedure still need multiple parameter, one for every selected column. I need the procedure to receive only one parameter regardless of how many column it select.

Dan Guzman Over a year ago

@VikiTheolorado, sorry if I wasn't clear. I suggested not using a stored procedure at all. Why do you want to use a proc?

Viki Theolorado Over a year ago

sorry if i wasn't clear too. I edited the question to include more explanation. Hope it clarify my question.

Collectives™ on Stack Overflow

Securing Dynamic SQL Where Clause with Dynamic Column

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related