Results 1 to 14 of 14

Thread: [RESOLVED] Allow users to build WHERE clauses

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Resolved [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!
    Last edited by szlamany; Apr 22nd, 2021 at 06:28 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Allow users to build WHERE clauses

    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)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Allow users to build WHERE clauses

    Quote Originally Posted by Zvoni View Post
    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.

    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.

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Allow users to build WHERE clauses

    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.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Allow users to build WHERE clauses

    Quote Originally Posted by GaryMazzone View Post
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Allow users to build WHERE clauses

    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.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Allow users to build WHERE clauses

    Quote Originally Posted by szlamany View Post
    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)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Allow users to build WHERE clauses

    Quote Originally Posted by NeedSomeAnswers View Post
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Allow users to build WHERE clauses

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Allow users to build WHERE clauses

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Allow users to build WHERE clauses

    Quote Originally Posted by szlamany View Post
    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).

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Allow users to build WHERE clauses

    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.
    Last edited by FunkyDexter; Apr 22nd, 2021 at 01:49 PM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Allow users to build WHERE clauses

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  14. #14

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Allow users to build WHERE clauses

    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...
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width