Avoid SQL injection in Devexpress Grid where\filter condidtion

I'm using Devexpress Grid to show my result for final users.

Since I'm using remote mode, I should filter my own result on server based on grid filter.

I made custom JS switch/case to build a SQL WHERE condition based on filter result like this:

   let whereClause = '';

        if (loadOptions['filter']) {
            const rawFilter = loadOptions['filter'];

            let filters = null;

            if (_.isArray(rawFilter[0])) {
                filters = _.map(rawFilter, (item) => {
                    if (_.isArray(item)) {
                        if (!_.isNumber(item[2])) {
                            switch (item[1]) {
                                case 'contains':
                                    return `(${item[0]} LIKE N'%${item[2]}%')`;
                                case 'notcontains':
                                    return `(${item[0]} NOT LIKE N'%${item[2]}%')`;
                                case 'startswith':
                                    return `(${item[0]} LIKE N'${item[2]}%')`;
                                case 'endswith':
                                    return `(${item[0]} LIKE N'%${item[2]}')`;
                                default:
                                    return `(${item[0]} ${item[1]} N'${item[2]}')`;
                            }
                        }
                        else {
                            return `(${item[0]} ${item[1]} ${item[2]})`;
                        }
                    }
                    else {
                        return item;
                    }
                });

                whereClause = _.join(filters, ' ');
            }
        }

And then passing the whereClause to the controller, like : "( ColumnA like N'ABC')"

After that, using it in a stored procedure like this (passing it as SearchParam):

CREATE OR ALTER PROCEDURE GetTestResult
    @Skip INT = 0,
    @Take INT = 400000,
    @SearchParam NVARCHAR(MAX) = null
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlCommand NVARCHAR(MAX)
    DECLARE @sqlCommandPagination  NVARCHAR(MAX)
    DECLARE @sqlCommandFinall  NVARCHAR(MAX)

    SET @sqlCommand = 'SELECT ColumnA,ColumnB,ColumnC FROM dbo.TestTable ' 

    SET @sqlCommandPagination = ' ORDER BY ColumnA ASC OFFSET ' + CAST(@Skip AS varchar(500)) + 
    ' ROWS FETCH NEXT ' + CAST(@Take AS varchar(500)) + ' ROWS ONLY;'

    IF (@SearchParam <> '' AND @SearchParam IS NOT NULL)
         SET @sqlCommandFinall = @sqlCommand + ' WHERE ' + @SearchParam + @sqlCommandPagination;

    IF (@SearchParam = '' OR @SearchParam IS NULL )
        SET @sqlCommandFinall = @sqlCommand +  @sqlCommandPagination;

        
    Execute SP_ExecuteSQL  @sqlCommandFinall;
END;
GO

Is there any way to make this method SQL injection proof?

I'm using Dapper/SqlMapper/Query function for calling my stored procedure from C#.

Solution

Not the way it's designed. Or at least not without carefully parsing the statement and then leveraging parameterized values and/or positively validating the components. And doing that is much more complex and prone to error than just passing the components.

In your current design, it's trivial to inject SQL into the where or order by. You should break this down into its components, pass those through and validate them at the backend. So, rather than passing ColumnA like N'ABC' (sic) you should pass ColumnA (sic) and ABC separately, and use parameterized queries for the values (bind them).

In the second case, many databases will not allow parameterizing order by values, so you should be sure to use positive validation in that case.