How dynamically create WHERE statement...

[Bobby Edward]

I have an advanced search box. The user can type in multiple words in the
box. Those words are then used in the WHERE clause against a Description db
field.

So these words: plumber carpenter electrician

Would essentially equate to: "WHERE (Description LIKE '%plumber%') OR
(Description LIKE '%carpenter%') OR (Description LIKE '%electrician%' )"

Is there any easy way to dynamically create this WHERE clasue? I know how
to do it manually by code, but I didn't know if I had to manually parse the
tokens and construct the clause or if there was an easier way...

(I'm using MySQL.)

Thanks.

[Mark Rae [MVP]]

"Bobby Edward" <bo***@nobody.c omwrote in message
news:um******** ******@TK2MSFTN GP02.phx.gbl...

I have an advanced search box. The user can type in multiple words in the
box. Those words are then used in the WHERE clause against a Description
db field.

So these words: plumber carpenter electrician

Would essentially equate to: "WHERE (Description LIKE '%plumber%') OR
(Description LIKE '%carpenter%') OR (Description LIKE '%electrician%' )"

Is there any easy way to dynamically create this WHERE clasue? I know how
to do it manually by code, but I didn't know if I had to manually parse
the tokens and construct the clause or if there was an easier way...

UNDER NO CIRCUMSTANCES do this!!! Your solution is absolutely wide open to
SQL Injection:
http://www.google.co.uk/search?sourc...L+Injection%22

Instead, allow users to select the occupation(s) they're interested in e.g.
by ticking checkboxes or some other technique - basically, anything to avoid
dynamic SQL...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[sloan]

You can read my take on it here:
http://www.sqlservercentral.com/arti...rproblem/2283/

The Zero to N Parameter Problem

"Bobby Edward" <bo***@nobody.c omwrote in message
news:um******** ******@TK2MSFTN GP02.phx.gbl...

>I have an advanced search box. The user can type in multiple words in the
box. Those words are then used in the WHERE clause against a Description
db field.

So these words: plumber carpenter electrician

Would essentially equate to: "WHERE (Description LIKE '%plumber%') OR
(Description LIKE '%carpenter%') OR (Description LIKE '%electrician%' )"

Is there any easy way to dynamically create this WHERE clasue? I know how
to do it manually by code, but I didn't know if I had to manually parse
the tokens and construct the clause or if there was an easier way...

(I'm using MySQL.)

Thanks.

[Bobby Edward]

"Mark Rae [MVP]" <ma**@markNOSPA Mrae.netwrote in message
news:us******** ******@TK2MSFTN GP03.phx.gbl...

>
UNDER NO CIRCUMSTANCES do this!!! Your solution is absolutely wide open to
SQL Injection:
http://www.google.co.uk/search?sourc...L+Injection%22

Instead, allow users to select the occupation(s) they're interested in
e.g. by ticking checkboxes or some other technique - basically, anything
to avoid dynamic SQL...

I appreciate that very much Mark. But, what if I want the user to search
for ANY kind of word? It may not be something that I can list.

Can't I just clean up the string, such as by IGNORING the following
words/special characters when I create the WHERE:
DELETE
REMOVE
DROP
SELECT
UPDATE
INSERT
WHERE
*
%
;
..
etc....

[Bobby Edward]

"sloan" <sl***@ipass.ne twrote in message
news:ei******** ******@TK2MSFTN GP06.phx.gbl...

>

You can read my take on it here:
http://www.sqlservercentral.com/arti...rproblem/2283/

The Zero to N Parameter Problem

Thanks. I'll check it out! ;)

[sloan]

The url not super "dynamic". But it has a mechanism for parameters.

The previous post is very correct. SQL Injection will mess you up.

"Bobby Edward" <bo***@nobody.c omwrote in message
news:et******** *******@TK2MSFT NGP05.phx.gbl.. .

"sloan" <sl***@ipass.ne twrote in message
news:ei******** ******@TK2MSFTN GP06.phx.gbl...

>>

You can read my take on it here:
http://www.sqlservercentral.com/arti...rproblem/2283/

The Zero to N Parameter Problem

Thanks. I'll check it out! ;)

[Mark Rae [MVP]]

"Bobby Edward" <bo***@nobody.c omwrote in message
news:%2******** **********@TK2M SFTNGP03.phx.gb l...

I appreciate that very much Mark. But, what if I want the user to search
for ANY kind of word? It may not be something that I can list.

Can't I just clean up the string, such as by IGNORING the following
words/special characters when I create the WHERE:
DELETE
REMOVE
DROP
SELECT
UPDATE
INSERT
WHERE

Absolutely not! Please please read some of the articles in the Google search
I posted.

1=1--;
DECLARE @strSQL nvarchar(100)
SET @strSQL = 'P'+'R'+'I'+'N' +'T ''H'+'E'+'L'+'L '+'O'''
EXEC sp_executesql @strSQL
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Bobby Edward]

>

Absolutely not! Please please read some of the articles in the Google
search I posted.

1=1--;
DECLARE @strSQL nvarchar(100)
SET @strSQL = 'P'+'R'+'I'+'N' +'T ''H'+'E'+'L'+'L '+'O'''
EXEC sp_executesql @strSQL

I'm using strongly typed XSD datasets with MySql. I thought that simply
replacing all special characters and db words with nothing would suffice,
such as...

strSearch = txtSearchString .text.replace(" +","") ' strip out special
characters
strSearch = strSearch.repla ce("*","") ' strip more
strSearch = strSearch.repla ce("..... ' keep stripping them out
strSearch = strSearch.repla ce("DROP","") ' remove db type words
strSearch = strSearch.repla ce("DELETE","" ) ' remove db type words
strSearch = strSearch.repla ce("SELECT","" ) ' remove db type words
etc etc etc

Then parse what's left using the remaining words/tokens.

Or, maybe I'm too simple minded and am not getting the point. I will do
some more research.

Thanks for your excellent input as usual Mark...

[Ben Amada]

Bobby Edward wrote:

I have an advanced search box. The user can type in multiple words in
the box. Those words are then used in the WHERE clause against a
Description db field.

So these words: plumber carpenter electrician

Would essentially equate to: "WHERE (Description LIKE '%plumber%') OR
(Description LIKE '%carpenter%') OR (Description LIKE '%electrician%' )"

Is there any easy way to dynamically create this WHERE clasue? I know
how to do it manually by code, but I didn't know if I had to manually
parse the tokens and construct the clause or if there was an easier
way...

(I'm using MySQL.)

This may not apply because you're using MySQL, but with SQL Server, you can
use parameterized queries. Parameterized queries allow you to build dynamic
SQL statements that are not susceptible to SQL Injection. You can add
multiple parameters to the command object allowing you to run queries such
as "where x or y or z". The code below is the basic idea ...

SqlCommand cmd = new SqlCommand();

SqlParameter param =
new SqlParameter("@ Description1", SqlDbType.VarCh ar);
param.Value = "%" + "plumber" + "%";
cmd.Parameters. Add(param);

param =
new SqlParameter("@ Description2", SqlDbType.VarCh ar);
param.Value = "%" + "carpenter" + "%";
cmd.Parameters. Add(param);

string Sql =
" SELECT SomeColumns " +
" FROM YourTable " +
" WHERE Description LIKE @Description1 " +
" OR Description LIKE @Description2; ";

SqlConnection conn =
new SqlConnection(" your connection string");

cmd.CommandText = Sql;
cmd.CommandType = CommandType.Tex t;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteRead er();

--
Ben
http://allben.net/

[Bobby Edward]

Thanks Ben. Nice code.

I have XSD strongly typed DataSets that I access thru my Business Layer
code. It accesses MySql but since it's strongly typed doesn't that mean
that I can use the same mechanism with MySql? I'll give it a try.

Thanks again! ;)