SQL Query - Best Practice for potential BIG query

Dr.Silly

Hi,

Just wondering what the best practices & methods used are for the following.

This is all asp.net and SQL Server btw.
Let's say I have a search page, which could have up to 20 - 30 filters for searching for a customer, some are drop down lists, some are free text.

I want to have a stored procedure which will return the results to the user.

How do I differntiate between AND's and OR's....
for instance.

If 10 fields are left blank, but the other ten fields are populated, would I have to build a query on the fly or could I have some type of stored proc already written ..

Jesus, reading back on this am I making any sense !!!! ????

John_Mc

You would have to add logic to the SP to decide whether to use a field or not, what logical operator to use etc.

I'd approach this by constructing a SQL query based on a collection of filters to use. You'd need to be extra careful doing it like this as it leaves you open to SQL injection attacks. Be sure to scrub the input before using!

Dr.Silly

John_Mc wrote: »

You would have to add logic to the SP to decide whether to use a field or not, what logical operator to use etc.

I'd approach this by constructing a SQL query based on a collection of filters to use. You'd need to be extra careful doing it like this as it leaves you open to SQL injection attacks. Be sure to scrub the input before using!

Thanks for that John,
So would you have some type of interface for the user so they can decide what fields they'd like to search by, and maybe have a drop down box of "=. AND, not like, etc etc ", and then build the query from that ?

Also, when you mention "scrub" the input, what do you mean here ?

Thanks again

John_Mc

Dr.Silly wrote: »

Thanks for that John,
So would you have some type of interface for the user so they can decide what fields they'd like to search by, and maybe have a drop down box of "=. AND, not like, etc etc ", and then build the query from that ?

Also, when you mention "scrub" the input, what do you mean here ?

Thanks again

Yeah I'd create a collection of some sort, populate it with the values from the UI, and then iterate through it to construct the SQL query.

By scrubbing I mean wrapping ' with '' and removing dangerous characters from the values before using them. A malicious user could manipulate the DOM to use your code to access the database. See more about what is required to prevent this here

Dr.Silly

John_Mc wrote: »

Yeah I'd create a collection of some sort, populate it with the values from the UI, and then iterate through it to construct the SQL query.

By scrubbing I mean wrapping ' with '' and removing dangerous characters from the values before using them. A malicious user could manipulate the DOM to use your code to access the database. See more about what is required to prevent this here

Excellent, thanks for the heads up.
Will start giving it a go now.
Cheers

DeVore

He means you need to check your user text for naughty SQL that someone might try to have you run for them. Imagine if your user adds this text to a search for shoes...

size = 9 " ; drop table sys_tables ;

or similar. You blithely add that to your WHERE clause string and when you execute the code... bye bye database.


The answer to your UI question depends very much on the IQ and IT experience of your users. If they have some understanding of logic or are technically minded then you can go for the idea of drop downs with AND, OR, NOT in them. If not, then you might want to think about giving them some standard reports they are most commonly likely to want with an option to create their own if they feel up to it.

The benefit of the that is that they get the standard report they like.


I would approach this by sending the SP the variables and let it work it out. Having a stored procedure that accepts a where clause blindly kinda defeats the purpose of having a SP in the first place.

DeV.

Dr.Silly

DeVore wrote: »

size = 9 " ; drop table sys_tables ;

Well this wouldn't be so pretty would it :eek:

cheers for the info

DeVore

Nope, and its amazing how many people try it. Even on us. Every day. Silly billies...

DeV.

Ithaca

If you don't want to construct the SQL query you could do something like this:

Have default value against parameters into stored procedure, e.g. -1

Write your select per normal with the following logic:

Select *
From table
Where (field1 = param1 or param1 = -1)
and (field2 = param2 or param2 = -1)
etc.

The effect of the ors above is to not filter on the field if the default -1 is passed in but to do the filter if anything other than -1 is passed in.

P.S. I work with Oracle these days but to avoid that SQL injection you're talking about you should always use bind variables. Can't remember if there is something similar in SQL Server.

Dr.Silly

Ithaca wrote: »

If you don't want to construct the SQL query you could do something like this:

Have default value against parameters into stored procedure, e.g. -1

Write your select per normal with the following logic:

Select *
From table
Where (field1 = param1 or param1 = -1)
and (field2 = param2 or param2 = -1)
etc.

The effect of the ors above is to not filter on the field if the default -1 is passed in but to do the filter if anything other than -1 is passed in.

P.S. I work with Oracle these days but to avoid that SQL injection you're talking about you should always use bind variables. Can't remember if there is something similar in SQL Server.

Hi Ithaca,

Just tried this on an int column,

select * from testTBL
where (testID = 1 or testID = -1)

and am getting
Operand type clash: ntext is incompatible with tinyint

I guess I need some type of conversion ?

DeVore

tinyint cant be negative... its an unsigned type (max value 255, min 0)... afaicr....



DeV.

forbairt

Reminds me of xkcd http://xkcd.com/327/

(sorry had to butt in with that after reading the earlier drop tables bit)

km991148

Ithaca wrote: »

P.S. I work with Oracle these days but to avoid that SQL injection you're talking about you should always use bind variables. Can't remember if there is something similar in SQL Server.

Think this is the same as parameterised sql (not sure what bind is), but you should use parameterised sql anyway. Plus dont make any of the SP dynamic (i.e. sending in parts of where etc) - loose almost all benefits of using them..

Ithaca

Dr.Silly wrote: »

Hi Ithaca,

Just tried this on an int column,

select * from testTBL
where (testID = 1 or testID = -1)

and am getting
Operand type clash: ntext is incompatible with tinyint

I guess I need some type of conversion ?

Is the testID field a ntext field? It should be a numberic type and the field and parameter should be the same type.

Bind variables and parameterized SQL are the same concept, they should always be used:

http://en.wikipedia.org/wiki/SQL_injection

Dr.Silly

Ithaca wrote: »

Is the testID field a ntext field? It should be a numberic type and the field and parameter should be the same type.

Hi,
It's an INT

amen

if you are just using one SP with multiple parameters you may want to watch your SP. If the parameters used are constanlychangin you may see a lot of SP recompiles.