[RESOLVED] Allow users to build WHERE clauses

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()

Code:

---

.
... 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!

Do you mean like providing a TextBox the user can enter "WHERE Salary>=2000"?

I'd rather first check, if the way i'd usually do something like this would be feasable (if i ever would get such an order):
Think Mac-Finder-App --> kinda like an Explorer with Miller-Columns

First column: available tables to choose from (if it's always the same table, then you don't need that one)
Next column: available columns
after selecting a column (move it from left ListBox to right Listbox?) offer a Dialogue/TextBox to enter the value, via Dropdown the operator ("=", "<", "LIKE" etc.), and optional the boolean operator for the next one (AND/OR/NOT)

It's just a concept-idea of mine. No Idea if it's practicable.
OTOH, the DMS at the company i work for provides exactly such a dialogue to the user if he's searching a document in the archive

Additionally, you wouldn't really need to think about injection (i take it you'll use parameters anyway)

Quote:

---

Originally Posted by Zvoni

Do you mean like providing a TextBox the user can enter "WHERE Salary>=2000"?

---

I could offer this as an immediate soluion - novice users would fail quickly.

Quote:

---

I'd rather first check, if the way i'd usually do something like this would be feasable (if i ever would get such an order):
Think Mac-Finder-App --> kinda like an Explorer with Miller-Columns

First column: available tables to choose from (if it's always the same table, then you don't need that one)
Next column: available columns
after selecting a column (move it from left ListBox to right Listbox?) offer a Dialogue/TextBox to enter the value, via Dropdown the operator ("=", "<", "LIKE" etc.), and optional the boolean operator for the next one (AND/OR/NOT)

---

I was headed in this direction - select the column, then enter the values. Currently when I do the client-side filtering I already have the data so the dialogue with the user is more clear (like Excel does when you filter a column with NOT SO MANY distinct values!).

Allowing the users to enter the "operator" - that's interesting. In my current client side filtering I only allow = or NOT =...and I force AND's which gets useless if you do some NOT's - looking to enhance this area soon as well.

Quote:

---

Additionally, you wouldn't really need to think about injection (i take it you'll use parameters anyway)

---

I'm in a STORED procedure, building a SQL select statement in code - in the SPROC. There are no parameters at this point.

We do that in our app... unfortunately we need to use dynamic SQL to build it. There are a lot of parameters that we pass into the SP (they can be null). The front end allows the user to select what they want to pass and the value for it. We then look at every parameter passed and build the where clause dynamically in the procedure (we also include a way to pick what column they want to order by). We then use EXECUTE sp_executeSQL to run the resulting query. It stinks for query plans but is is a requirement of our system.

Quote:

---

Originally Posted by GaryMazzone

We do that in our app... unfortunately we need to use dynamic SQL to build it. There are a lot of parameters that we pass into the SP (they can be null). The front end allows the user to select what they want to pass and the value for it. We then look at every parameter passed and build the where clause dynamically in the procedure (we also include a way to pick what column they want to order by). We then use EXECUTE sp_executeSQL to run the resulting query. It stinks for query plans but is is a requirement of our system.

---

That is kind of my exact situation.

How do you pass a random bunch of parameters to the SPROC?

Or do you just allow a series of SPROC params to be used - like @SelectCol1, @SelectValue1, @SelectCol2, @SelectValue2?

I was just pondering making a "selection value" temp table - and putting the "values" into that single row.

The linked server is a nightmare - just getting the "column names" and "data types" is slow with the supplied MS system sprocs.

Instead of using lots of Params you could create a User Defined Table Type, Stored procedures accept it as parameter.

From the code side your essentially sending a table object.

Quote:

---

Originally Posted by szlamany

Allowing the users to enter the "operator" - that's interesting. In my current client side filtering I only allow = or NOT =...and I force AND's which gets useless if you do some NOT's - looking to enhance this area soon as well.

---

I feel you.
I've had users trying to formulate such a WHERE-Clause running into a "deadlock" (at least i call it a deadlock --> basically the one filter excluding the other) returning an empty query.

On a sidenote: Since you mentioned Excel (and its Autofilter-Function):
Instead of "LIKE" you could offer "Starts with" (MyColumn LIKE 'AValue%'), "Ends with" (MyColumn LIKE '%AValue') or "Contains" (LIKE '%AValue%')
Of course you would have to do a sanity check on the value (stripping wildcards if a user thinks he's clever and enters %AValue% in that TextBox)

As for the boolean Operators AND/OR: Yeah

I could never explain why a WHERE SomeValue=1 AND SomeOtherValue=2 OR SomeOtherValue=5 failed for the Users (a.k.a. returning not expected results)
You try to teach them placing paranthesis at the correct position: WHERE SomeValue=1 AND (SomeOtherValue=2 OR SomeOtherValue=5)

Quote:

---

Originally Posted by NeedSomeAnswers

Instead of using lots of Params you could create a User Defined Table Type, Stored procedures accept it as parameter.

From the code side your essentially sending a table object.

---

This needs further investigation - thank you very much.

Wow - thanks to @GaryMazzone, I now know that sp_executeSQL can handle "parameters" in the string variable - very nice!

We also include a parameter value for AND name @OR as a BIT is it is 1 then we set all parameters to OR if 0 set to AND

Quote:

---

Originally Posted by szlamany

Wow - thanks to @GaryMazzone, I now know that sp_executeSQL can handle "parameters" in the string variable - very nice!

---

sp_executesql is all lower-case. Don’t get bitten by case-sensitive MSSQL instances because of wrong system names casing (like sysname data-type is all lower-case too).

I've had to do exactly this before. It was a long time ago but here's what I remember doing.

• I didn't want to allow pure free text, e.g. "WHERE Salary>=2000". It's not wrong as such but 1. It's too open to abuse and 2. difficult for non sql user to use. I wanted more control.
• I considered a filter to come in 3 parts: field, comparator and value.
• I wanted to control the comparators, mainly so I could give the user a "friendly" representation: "Greater Than", "Equals" etc rather than >, = and so on.
• So I created a user defined table type with 3 columns, roughly as NSA described and passed this into the sproc.
• If you're controlling the comparators as above, you put the acceptable values into a look up table which makes validation easier
• Similarly you can police Fields by checking against sys.columns (think I've got the name of that wrong but I'm sure you know what I mean)
• Just as I wanted to display friendly comparators to the user I also wanted to allow the user to select from a list of valid fields - this is easily supported by selecting from sys.columns when the user selected a table.
• Once you've got that in place it's reasonably easy to parse it into a query using sp_executesql which has the advantage of supporting explicit parameters in dynamic sql to protect against injection

A couple of wrinkles to consider:-
1. You can compare a field to a field or a value to a value so you may want to consider allowing either in both sides of the comparison. You would need some way of distinguishing them, e.g. you could require that column names be wrapped in quotes or square brackets.
2. You need to think about how multiple filters will combine - i.e. And/Or. I did it by adding a fourth column which worked in most cases but doesn't allow for nesting like parentheses would.
3. I remember Likes being a problem. It was reasonable to educate users on how to use a wildcard but they would insist on putting them at the front of the search string and making the query non-sargeable.

I remember being pretty happy with the above approach at the time but if I was doing it again I think I'd pass in some xml or JSON and keep my logic for how to build it up on the client/middle tier. You could still shred it out in the sproc but it would be much more flexible than the table parameter.

You also might want to take a look at Entity Framework or a similar ORM technology. Alot of them have tight way of expressing filers in code which might help surface alot of this up into the client/middle tier where you're likely to be able to write much simpler code.

This is relevant to the first post sproc .
I just saw an app I made for a company back in 2010, basically it re-created an SQL Server Table relationship Form.
What i see is that instead of writing an sp to get the columns, i do this:

queryString = "SELECT objtype, objname, name, value FROM fn_listextendedproperty ('Description', 'schema', 'dbo', 'table', '" & strTableName & "', default, default)"

It won't help with your question because what the app does is blending all the keys and relationship but I just saw it and though to write it down.

I am going to mark this thread resolved - lots of good ideas where given.

I've just finished all the basic plumbing and handshakes back and forth - utilized my "web-site local grid" filtering to create the filter and I've gotten it as far as arriving in the AJAX call to refresh the grid from the server. Testing VENDOR STATE = CT...