Dynamic Search Queries Using XML (more info: https://eitanblumin.com/2018/10/28/dynamic-search-queries-versus-sql-injection/ )

ParseXMLFilterParameters_Example.sql

/*
	Fully Parameterized Search Query
	--------------------------------
	
	Copyright Eitan Blumin (c) 2014; email: [email protected]
	You may use the contents of this SQL script or parts of it, modified or otherwise
	for any purpose that you wish (including commercial).
	Under the single condition that you include in the script
	this comment block unchanged, and the URL to the original source, which is:
	http://www.madeiradata.com/author/eitan/
*/

--/*
-- Initialize configuration tables with demo data:

TRUNCATE TABLE FilterTables;
INSERT INTO FilterTables
(FilterTableAlias,FilterTableName,FilterTableJoinPredicate)
VALUES
 ('casts','MovieCasts','movies.Id = casts.MovieId AND ISNULL(casts.bExtra,0) = 0 AND casts.WorkingTitleId IN(1,2,6)')
,('directors','MovieCasts','movies.Id = directors.MovieId AND directors.WorkingTitleId = 5')
,('movielang','MovieLanguages','movies.Id = movielang.MovieId')
,('characteristics','MovieMainCharacteristicsFlat','movies.Id = characteristics.MovieId')

TRUNCATE TABLE FilterColumns;
INSERT INTO FilterColumns
(ColumnID,ColumnFilterTableAlias,ColumnRealName,ColumnSqlDataType,ColumnDisplayName,ColumnSortEnabled)
VALUES
 (1, NULL, 'Name', 'varchar(max)', 'Movie Name',1)
,(2, NULL, 'RunTime', 'int', 'Movie Run Time',0)
,(3, NULL, 'ImdbRating', 'decimal(18,2)', 'IMDB Rating',1)
,(4, NULL, 'ImdbRatingCount', 'decimal(18,2)', 'IMDB number of votes',0)
,(5, 'movielang', 'LanguageId', 'int', 'Movie Language',0)
,(6, 'characteristics', 'ViolenceGore', 'int', 'Violence & Gore',0)
,(7, 'characteristics', 'Profanity', 'int', 'Profanities',0)
,(8, NULL, 'MovieReleaseDate', 'date', 'Release Date',0)
,(9, 'casts', 'CelebId', 'int', 'Cast',0)
,(10, 'directors', 'CelebId', 'int', 'Directors',0)
--*/

/*
This is a sample report procedure which uses the parsing procedure created earlier.
The idea is that you can create many different report procedures like this,
each with a different hard-coded Inner SQL (the base query).

But all of these different procedures will use the same "engine" (the parsing procedure).
The GUI will be executing these secondary procedures, and never will execute directly
the parsing procedure itself.

The placeholder --{FilterPlaceHolder} can be used for choosing a specific place
where to put the parsed filtering expression.
This is for cases such as when you use a sub-query and you need to filter the table inside it,
but some of the columns are lost outside the sub-query (due to aggregations and such).
So you'd put the placeholder inside the WHERE clause of the sub-query,
thus affecting the inner table directly.

*/
IF OBJECT_ID('SampleReportProc') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('SampleReportProc'), 'IsProcedure') = 1
	DROP PROCEDURE SampleReportProc;
GO
/*
	Fully Parameterized Search Query
	--------------------------------
	
	Copyright Eitan Blumin (c) 2014; email: [email protected]
	You may use the contents of this SQL script or parts of it, modified or otherwise
	for any purpose that you wish (including commercial).
	Under the single condition that you include in the script
	this comment block unchanged, and the URL to the original source, which is:
	http://www.madeiradata.com/author/eitan/
*/
CREATE PROCEDURE SampleReportProc
	@XMLParams XML,
	@XMLOrdering XML = NULL,
	@PageSize INT = 50,
	@Offset INT = 1
AS

-- Init variables
DECLARE
	@InnerSQL NVARCHAR(MAX),
	@ParsedSQL NVARCHAR(MAX)

-- Prepare the inner SQL
SET @InnerSQL = N'
SELECT DISTINCT movies.*
FROM Movies AS movies
--{FilterTablesPlaceHolder}
WHERE 1=1
--{FilterPlaceHolder}'

EXEC FilterParseXMLParameters @InnerSQL, @XMLParams, @XMLOrdering, @PageSize, @Offset, @ParsedSQL OUTPUT

PRINT @ParsedSQL -- This is optional for debugging

/*
Note that the procedure FilterParseXMLParameters doesn't execute the SQL itself.
It only returns the final parsed SQL as an output parameter.
The secondary procedure is then responsible for performing the final execute.

The reason for this is security. The parsing procedure is meant to be a central "engine".
If it would have performed the execution itself, it would do it under a certain security context.
That security context is not neccessarily the same security context used by users.
For maximum security, I'd suggest having one user with access to the tables FilterPredicates
and FilterColumns, but no access to the user tables used in the Inner SQL queries.
And another user that has access to the user tables, but no access to the tables FilterPredicates
and FilterColumns. The parsing procedure will be executed under the context of the first user.
And the reporting procedures (e.g. "SampleReportProc") will be executed under the context of the second user.
*/
EXEC sp_executesql @ParsedSQL, N'@XMLParams XML', @XMLParams;
GO

-- Now that we created our reporting procedure, lets test it out
-- by executing it similar to as the GUI would:

DECLARE @XMLParams XML;
DECLARE @XMLOrdering XML;

PRINT 'Test #1'

SET @XMLParams = N'<Parameters>
	<ParameterValue columnId="1" operatorId="1">atrix</ParameterValue>
	<ParameterValue columnId="5" operatorId="9">2</ParameterValue>
	<ParameterValue columnId="9" operatorId="14">
		<Value>8</Value>
		<Value>6</Value>
	</ParameterValue>
	<ParameterValue columnId="10" operatorId="14">
		<Value>4</Value>
	</ParameterValue>
</Parameters>'

SET @XMLOrdering = N'<OrderingColumns>
	<ColumnOrder columnId="1" isAscending="1" />
	<ColumnOrder columnId="3" isAscending="0" />
</OrderingColumns>
'

EXEC SampleReportProc @XMLParams, @XMLOrdering;

PRINT 'Test #2'

SET @XMLParams = N'<Parameters>
	<ParameterValue columnId="1" operatorId="3">Testing</ParameterValue>
	<ParameterValue columnId="1" operatorId="1">Two</ParameterValue>
</Parameters>'
EXEC SampleReportProc @XMLParams;
GO

ParseXMLFilterParameters_Setup.sql

/*
	Fully Parameterized Search Query
	--------------------------------
	
	Copyright Eitan Blumin (c) 2014; email: [email protected]
	You may use the contents of this SQL script or parts of it, modified or otherwise
	for any purpose that you wish (including commercial).
	Under the single condition that you include in the script
	this comment block unchanged, and the URL to the original source, which is:
	http://www.madeiradata.com/author/eitan/
*/


/*
---------------- Tables ----------------

FilterTables
---------------

This table will provide meta-data information required for optional JOIN operators.
*/

IF OBJECT_ID('FilterTables') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterTables'), 'IsTable') = 1
	DROP TABLE FilterTables;
GO
CREATE TABLE FilterTables
(
	FilterTableAlias SYSNAME NOT NULL PRIMARY KEY,
	FilterTableName SYSNAME NOT NULL,
	FilterTableJoinPredicate VARCHAR(100) NOT NULL
)
-- Sample data
--INSERT INTO FilterTables
--(FilterTableAlias,FilterTableName,FilterTableJoinPredicate)
--VALUES
-- ('casts','MovieCasts','movies.Id = casts.MovieId AND ISNULL(casts.bExtra,0) = 0 AND casts.WorkingTitleId IN(1,2,6)')
--,('directors','MovieCasts','movies.Id = directors.MovieId AND directors.WorkingTitleId = 5')
--,('movielang','MovieLanguages','movies.Id = movielang.MovieId')
--,('characteristics','MovieMainCharacteristicsFlat','movies.Id = characteristics.MovieId')

/*

FilterColumns
----------------

This table will map column names from our target table to an ID and a data type.
Using this table, the GUI can identify columns that can be filtered,
and later the database back-end will use the same table for parsing.

*/
IF OBJECT_ID('FilterColumns') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterColumns'), 'IsTable') = 1
	DROP TABLE FilterColumns;
GO
CREATE TABLE FilterColumns
(
	ColumnID INT PRIMARY KEY,
	ColumnFilterTableAlias SYSNAME NULL,
	ColumnRealName SYSNAME NOT NULL,
	ColumnSqlDataType VARCHAR(50) NOT NULL,
	ColumnDisplayName NVARCHAR(200) NULL,
	ColumnSortEnabled BIT NOT NULL
);
-- Sample data
--INSERT INTO FilterColumns
--(ColumnID,ColumnFilterTableAlias,ColumnRealName,ColumnSqlDataType,ColumnDisplayName,ColumnSortEnabled)
--VALUES
-- (1, NULL, 'Name', 'varchar(max)', 'Movie Name',1)
--,(2, NULL, 'RunTime', 'int', 'Movie Run Time',0)
--,(3, NULL, 'ImdbRating', 'decimal(18,2)', 'IMDB Rating',1)
--,(4, NULL, 'ImdbRatingCount', 'decimal(18,2)', 'IMDB number of votes',0)
--,(5, 'movielang', 'LanguageId', 'int', 'Movie Language',0)
--,(6, 'characteristics', 'ViolenceGore', 'int', 'Violence & Gore',0)
--,(7, 'characteristics', 'Profanity', 'int', 'Profanities',0)
--,(8, NULL, 'MovieReleaseDate', 'date', 'Release Date',0)
--,(9, 'casts', 'CelebId', 'int', 'Cast',0)
--,(10, 'directors', 'CelebId', 'int', 'Directors',0)

GO
/*

FilterPredicates
-----------------

This table will contain the list of possible predicates and the template for each.
The templates use "placeholders" such as {Column} and {Parameter} which can later
be easily replaced with relevant values.
{Column}		= Placeholder for the column name to be filtered.
{Parameter}		= Placeholder for the local parameter that contains the filter data.
{FilterTable}	= Placeholder for `FilterTableName AS FilterTableAlias WHERE FilterTableJoinPredicate`.

*/
IF OBJECT_ID('FilterPredicates') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterPredicates'), 'IsTable') = 1
	DROP TABLE FilterPredicates;
GO
CREATE TABLE FilterPredicates
(
	PredicateID INT PRIMARY KEY,
	IsMultiValue BIT NOT NULL,
	PredicateName VARCHAR(50) NOT NULL,
	PredicateTemplate VARCHAR(4000) NOT NULL
);
INSERT INTO FilterPredicates VALUES(1, 0, 'Contains', '{Column} LIKE ''%'' + {Parameter} + ''%''')
INSERT INTO FilterPredicates VALUES(2, 0, 'NotContains', '{Column} NOT LIKE ''%'' + {Parameter} + ''%''')
INSERT INTO FilterPredicates VALUES(3, 0, 'StartsWith', '{Column} LIKE {Parameter} + ''%''')
INSERT INTO FilterPredicates VALUES(4, 0, 'EndsWith', '{Column} LIKE ''%'' + {Parameter}')
INSERT INTO FilterPredicates VALUES(5, 0, 'GreaterThan', '{Column} > {Parameter}')
INSERT INTO FilterPredicates VALUES(6, 0, 'GreaterOrEqual', '{Column} >= {Parameter}')
INSERT INTO FilterPredicates VALUES(7, 0, 'LessThan', '{Column} < {Parameter}')
INSERT INTO FilterPredicates VALUES(8, 0, 'LessOrEqual', '{Column} <= {Parameter}')
INSERT INTO FilterPredicates VALUES(9, 0, 'Equals', '{Column} = {Parameter}')
INSERT INTO FilterPredicates VALUES(10, 0, 'NotEquals', '{Column} <> {Parameter}')
INSERT INTO FilterPredicates VALUES(11, 1, 'In', '{Column} IN (SELECT Value FROM {Parameter})')
INSERT INTO FilterPredicates VALUES(12, 1, 'NotIn', '{Column} NOT IN (SELECT Value FROM {Parameter})')
INSERT INTO FilterPredicates VALUES(13, 0, 'Custom1', '{Column} = dbo.MyCustomFunction({Parameter})')
INSERT INTO FilterPredicates VALUES(14, 1, 'HasAll', 'NOT EXISTS (SELECT Value FROM {Parameter} EXCEPT SELECT {Column} FROM {FilterTable})')
GO


/*
---------------- Stored Procedure ----------------
This is the stored procedure that will perform the parsing itself.
It's not meant to be executed directly from within the application
because the @InnerSQL parameter can still cause SQL Injection.

Instead, you're supposed to create "secondary" procedures which will
use this procedure, each with a different hard-coded value for the @InnerSQL.

*/

IF OBJECT_ID('FilterParseXMLParameters') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('FilterParseXMLParameters'), 'IsProcedure') = 1
	DROP PROCEDURE FilterParseXMLParameters;
GO
/*
	Fully Parameterized Search Query
	--------------------------------
	
	Copyright Eitan Blumin (c) 2014; email: [email protected]
	You may use the contents of this SQL script or parts of it, modified or otherwise
	for any purpose that you wish (including commercial).
	Under the single condition that you include in the script
	this comment block unchanged, and the URL to the original source, which is:
	http://www.madeiradata.com/author/eitan/
*/
CREATE PROCEDURE FilterParseXMLParameters
	@InnerSQL			NVARCHAR(MAX),				-- the inner SQL query to use
	@XMLParams			XML,						-- the definition of the parameter values
	@XMLOrdering		XML = NULL,					-- the definition of the column ordering
	@PageSize			INT = 50,
	@Offset				INT = 1,
	@ParsedSQL			NVARCHAR(MAX) OUTPUT,		-- returns the parsed SQL command to be used for sp_executesql.
	@ForceRecompile		BIT = 1,					-- forces the query to do parameter sniffing using OPTION(RECOMPILE)
	@FilterPlaceHolder	NVARCHAR(200) = '--{FilterPlaceHolder}', -- if this is used, it must be placed after an existing WHERE predicate
	@FilterTablesPlaceHolder	NVARCHAR(200) = '--{FilterTablesPlaceHolder}'
AS
SET NOCOUNT ON;
-- Init variables
DECLARE 
	@PageOrdering NVARCHAR(MAX),
	@FilterString NVARCHAR(MAX), 
	@FilterTablesString NVARCHAR(MAX), 
	@FilterParamInit NVARCHAR(4000)

SET @FilterString = N'';
SET @FilterTablesString = N'';

-- Prepare the ORDER BY clause
SELECT
	@PageOrdering = ISNULL(@PageOrdering + N', ',N'') + FilterColumns.ColumnRealName + N' ' + CASE WHEN Q.IsAscending = 1 THEN 'ASC' ELSE 'DESC' END
FROM
(
	SELECT
		OrdinalIndex		= CONVERT(nvarchar(50),X.value('for $i in . return count(../*[. << $i]) + 1','int')),
		OrderingColumnID	= X.query('.').value('(/ColumnOrder/@columnId)[1]','int'),
		IsAscending			= X.query('.').value('(/ColumnOrder/@isAscending)[1]','bit')
	FROM
		@XMLOrdering.nodes('/OrderingColumns/ColumnOrder') AS T(X)
) AS Q
JOIN
	FilterColumns
ON
	Q.OrderingColumnID = FilterColumns.ColumnID
WHERE
	FilterColumns.ColumnSortEnabled = 1

IF @PageOrdering IS NULL
	SET @PageOrdering = '(SELECT NULL)'

-- Add JOIN clauses for relevant filter tables
SELECT
	@FilterTablesString = @FilterTablesString + N'
INNER JOIN ' + Q.FilterTableName + N' AS ' + Q.FilterTableAlias + N'
ON ' + Q.FilterTableJoinPredicate
FROM
(
	SELECT DISTINCT
		FilterTables.*
	FROM
		(
			-- This parses the XML into a relational structure
			SELECT
				DISTINCT FilterColumnID		= X.query('.').value('(/ParameterValue/@columnId)[1]','int')
			FROM
				@XMLParams.nodes('/Parameters/ParameterValue') AS T(X)
			WHERE
				X.query('.').value('(/ParameterValue/@operatorId)[1]','int')
				NOT IN (
					SELECT PredicateID
					FROM FilterPredicates
					WHERE PredicateName IN ('HasAll')
					)
		) AS ParamValues
	JOIN
		FilterColumns
	ON
		ParamValues.FilterColumnID = FilterColumns.ColumnID
	JOIN
		FilterTables
	ON
		FilterColumns.ColumnFilterTableAlias = FilterTables.FilterTableAlias
	AND FilterColumns.ColumnFilterTableAlias IS NOT NULL
) AS Q;

/*
Envelope the inner SQL with a CTE for easier usage.
This will help us reference column names directly
and avoid naming conflicts when the inner SQL uses JOIN
between different tables with identical column names.
*/
SET @ParsedSQL = N'
;
WITH Main
AS
(
' + @InnerSQL + N'
)
SELECT *
FROM
(
SELECT
	Main.*, RowNumber = ROW_NUMBER() OVER( ORDER BY ' + @PageOrdering + N' )
FROM Main'

-- Prepare WHERE clause
SET @ParsedSQL = @ParsedSQL + N'
WHERE 1=1
';

-- if there's no filter placeholder provided
IF ISNULL(CHARINDEX(@FilterPlaceHolder,@ParsedSQL,0),0) = 0
BEGIN
	-- add the placeholder manually at the end of the query
	SET @ParsedSQL = @ParsedSQL + @FilterPlaceHolder + N'
'
END

-- Parse filtering
SELECT
	@FilterParamInit = ISNULL(@FilterParamInit, '') + N'
DECLARE @p' + ParamIndex +

		-- If operand is multi-valued, declare local variable as a temporary table
		CASE WHEN FilterPredicates.IsMultiValue = 1 THEN
			N' TABLE (Value ' + FilterColumns.ColumnSqlDataType + N');
			INSERT INTO @p' + ParamIndex + N'
			SELECT X.query(''.'').value(''(/Value)[1]'',''' + FilterColumns.ColumnSqlDataType + N''')
			FROM @XMLParams.nodes(''(/Parameters/ParameterValue)[' + ParamIndex + N']/Value'') AS T' + ParamIndex + N'(X)'
		
		-- Otherwise, declare the local variable as a regular variable.
		ELSE
			N' ' + FilterColumns.ColumnSqlDataType + N';
			SET @p' + ParamIndex + N' = @XMLParams.value(''(/Parameters/ParameterValue)[' + ParamIndex + N']'',''' + FilterColumns.ColumnSqlDataType + N''');'
		END
		,
	-- Parse the operand template by replacing the placeholders
	@FilterString = @FilterString + N'
	AND ' + REPLACE(
			REPLACE(
			REPLACE(FilterPredicates.PredicateTemplate,'{Column}',ISNULL(FilterTables.FilterTableAlias + N'.',N'') + FilterColumns.ColumnRealName)
			, '{Parameter}', '@p' + ParamIndex)
			, '{FilterTable}', ISNULL(FilterTables.FilterTableName + N' AS ' + FilterTables.FilterTableAlias + N' WHERE ' + FilterTables.FilterTableJoinPredicate,N''))
FROM
	(
		-- This parses the XML into a relational structure
		SELECT
			ParamIndex			= CONVERT(nvarchar(50),X.value('for $i in . return count(../*[. << $i]) + 1','int')),
			FilterColumnID		= X.query('.').value('(/ParameterValue/@columnId)[1]','int'),
			FilterPredicateID	= X.query('.').value('(/ParameterValue/@operatorId)[1]','int')
		FROM
			@XMLParams.nodes('/Parameters/ParameterValue') AS T(X)
	) AS ParamValues
JOIN
	FilterColumns
ON
	ParamValues.FilterColumnID = FilterColumns.ColumnID
JOIN
	FilterPredicates
ON
	ParamValues.FilterPredicateID = FilterPredicates.PredicateID
LEFT JOIN
	FilterTables
ON
	FilterColumns.ColumnFilterTableAlias = FilterTables.FilterTableAlias
AND FilterColumns.ColumnFilterTableAlias IS NOT NULL


-- replace filter placeholder with the filter string
SET @ParsedSQL = ISNULL(@FilterParamInit,'') + REPLACE(REPLACE(@ParsedSQL,@FilterPlaceHolder,ISNULL(@FilterString,'')),@FilterTablesPlaceHolder,ISNULL(@FilterTablesString,''))

IF @ForceRecompile = 1
	SET @ParsedSQL = @ParsedSQL + N'
) AS Q
WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(50), @Offset) + N' AND ' + CONVERT(nvarchar(50), @Offset + @PageSize - 1) + N'
ORDER BY RowNumber
OPTION (RECOMPILE)'
GO