asked on

Creating a SQL Query Based on Filters Provided

I want to craft a sql lookup utility on a classic asp website that has one required field and several optional fields. How do i write a query that will look for the required field and then only the optional fields where a search filter has been supplied. The required filter is LastName and the optional filters are FirstName, Gender, City, State, and Email. Here is the query I have if only the LastName is provided:

    sql = "SELECT p.LastName, p.FirstName, ir.RaceID, ir.ChipTime, ir.ElpsdTime, ir.ChipStart FROM IndResults ir "
    sql = sql & "INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID WHERE p.LastName = '" & sLastName & "'"

Open in new window

Thank you very much!

Big Monty

Personally, I would turn this into a Stored Procedure using Parameterized Queries, but for simplicity's sake, I'll show you based off of the format you've given. This assumes all of your data has already been sanitized.

    sql = "SELECT p.LastName, p.FirstName, ir.RaceID, ir.ChipTime, ir.ElpsdTime, ir.ChipStart " _
           & "FROM IndResults ir " _
           & "INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID " _
           & "WHERE p.LastName = '" & sLastName & "'"

    if FirstName <> "" then sql = sql & " and FirstName = '" & FirstName & "'"
    if Gender <> "" then       sql = sql & " and Gender = '" & Gender& "'"
    if City <> "" then             sql = sql & " and City = '" & City& "'"
    if State <> "" then           sql = sql & " and State = '" & State & "'"
    if Email <> "" then          sql = sql & " and Email = '" & Email& "'"

Open in new window

Éric Moreau

you can conditionally add the optional parameters:

if sFirstName <> "" then
     sql = sql & " AND p.FirstName = '" & sFirstName & "'"
end if

Open in new window

slightwv (䄆 Netminder)

You should NEVER use string contatenation for dynamic SQL. Huge SQL Injection issue.

What if I enter this into the last name field: bob' or 'q'='q

Big Monty

agreed, I've preached this a lot to Bob (and others) in the past, but it's up to each developer how valuable security is to them

Bob Schneider

ASKER

Awesome! Thank you both!!

Bob Schneider

ASKER

Oops. My bad. So back to the drawing board. Suggestions?

Big Monty

stored procedures and PQ's, like I suggested, are the most secure

slightwv (䄆 Netminder)

>>Suggestions?

Oracle has bind variables. Not sure about the SQL Server equivalent.

ASKER CERTIFIED SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>ISNULL(p.FirstName,' ') Is Not Null

I'm not a SQL Server Expert so I have to ask: Will that allow index use if there was an index on FirstName?

In Oracle functions on an indexed column will disallow the use of the index.

Mark Edwards

Surely the great minds at SQL Server development have thought of this situation and provided a way to handle optional criteria in a non-dynamic T-SQL query.... or are we forced to build everything dynamically when optional filters are involved?

Mark Edwards

This topic is also discussed at:
https://stackoverflow.com/questions/1705634/proper-way-to-handle-optional-where-clause-filters-in-sql

Bob Schneider

ASKER

Thank you everyone! I guess I'll put together a stored procedure. Not sure of my chances of success with something this complex but might as well give it a shot.

Nitin Sontakke

I use following way of applying filters:

where 1 = 1
  and p.LastName = isnull(@LastName, p.LastName)		-- use this form when column is not null
  and 1 = case when @FirstName is null then 1 else case when @FirstName = p.FirstName then 1 else 0 end end  -- use this form when column is nullable.

Open in new window