[RESOLVED] Allow users to build WHERE clauses

[szlamany]

I need to allow users to build a WHERE clause.

Basically I am in a STORED PROCEDURE that allows any table to be selected from - by specifying the table name in a parameter to the SPROC. The SPROC builds the SELECT statement and then does an EXEC()

.
... lots of sql removed here ...
.
Declare @ES nvarchar(max)

Declare @KW varchar(max)
If @Kiosk=1
      Set @KW=' Where 1=2'
Else  If IsNull(@RowKey,'')<>''
            Set @KW=' Where '+@RKF+'='+@RowKey
      Else  Set @KW=''

Declare @FT varchar(max)
If @Filter=1
      Set @FT='Top 1000 '
Else  Set @FT=''

Set @ES='Select ' + @FT + @SL + @KS + ','''' "awcRowDirty" From '+@TblName+@KW+' Order by 1,2'
--select @ks,@es
Exec (@ES)

This has been in use for a while - returning all the rows of the table - and allowing client-side filtering in a web app.

I am now encountering tables with insane numbers of rows - 8 million in a payroll history table for instance. And to make matters worse, the web server is hosted and the "database" I am pulling from is hidden - for security purposes - behind a "linked server" entry in the local web server database (that linked server DB resides on a client machine somewhere else that is firewall "open" to my web host).

Talk about convoluted!

So - my approach so far, limit the initial pull to TOP 1000, for instance - so I can at least expose the web app to what columns are available and potential data types for those columns.

I now need to allow the user to enter the column to "filter" on and the data values to use.

Simple ones - no problem. Fiscal Year - enter a number like 2020.

Vendor Name - more complex. Need to allow for wildcards - so they could do %Verizon%.

I am concerned about sql injection - not malicious - more accidental. Entering free text that could contain delimiters that I need to detect and remove.

Anyone ever go down a path like this?

TIA!