Posted by John on 06/08/06 14:36

Well, when I say I allow client to specify the where clause, that is a
just simple way to put it, actually I publish a list of client is
allowed to do search, which is different name than the database field
name, I have a table to map the field name the client is using to real
database field name ( or XML Query)
when I get the where clause I do the parsing and convert the client
supplied name into real database name (or XML Query expression), since
the parse is doing all the checks, I am not worry about the SQL
injection

You may ask why I am going through so much trouble, the reason it I am
writing a framework that needs to be extendable, which means I design
the basic infrastructure, other group could design application sitting
on top of the framework. For example, they could design their own
database, tables, as long as they following the general design, they
could reuse everything I am doing.

Erland Sommarskog wrote:
> John (johnxhc@yahoo.com) writes:
> > Unfortunately, the client supply the where clause, which could be
> > anything, such as
> > (Lastname='johnson' or firstname='smith') and (city='Los
> > Angeles') or productid in (234,23434,33) ....
> > So it is very hard for me to break the search criteria into different
> > part and do a union considering the different logical operator
> > combination
>
> And who got that bright idea?
>
> Sending WHERE clauses as parameters is a really bad idea. Either you
> compose the entire SQL statement in the client. Or you have all the
> SQL in the stored procedure. What you have now is the worst of both
> worlds.
>
> It will be very difficult to work well. And obvoiusly there is a risk
> with SQL injection. And the procedure cache will be a waste.
>
> Back to the drawing board, if you want to hear my opinion.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]