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]]

Re: How dynamically create WHERE statement...

"Bobby Edward" <bobby@nobody.c omwrote in message
news:umLqMrHQJH A.4504@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?sourceid=navclient&hl=en-GB&ie=UTF-8&rlz=1T4GPTB_en-GBGB298GB298&q=%22SQL+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]

Re: How dynamically create WHERE statement...



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

The Zero to N Parameter Problem



"Bobby Edward" <bobby@nobody.c omwrote in message
news:umLqMrHQJH A.4504@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]

Re: How dynamically create WHERE statement...

"Mark Rae [MVP]" <mark@markNOSPA Mrae.netwrote in message
news:us4MFBIQJH A.1164@TK2MSFTN GP03.phx.gbl...

>
UNDER NO CIRCUMSTANCES do this!!! Your solution is absolutely wide open to
SQL Injection:

http://www.google.co.uk/search?sourceid=navclient&hl=en-GB&ie=UTF-8&rlz=1T4GPTB_en-GBGB298GB298&q=%22SQL+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]

Re: How dynamically create WHERE statement...

"sloan" <sloan@ipass.ne twrote in message
news:eiC5XEIQJH A.4680@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]

Re: How dynamically create WHERE statement...


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" <bobby@nobody.c omwrote in message
news:etZP%23RIQ JHA.728@TK2MSFT NGP05.phx.gbl.. .

"sloan" <sloan@ipass.ne twrote in message
news:eiC5XEIQJH A.4680@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]]

Re: How dynamically create WHERE statement...

"Bobby Edward" <bobby@nobody.c omwrote in message
news:%23JB%23RO IQJHA.1164@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]

Re: How dynamically create WHERE statement...

>

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]

Re: How dynamically create WHERE statement...

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]

Re: How dynamically create WHERE statement...

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! ;)

[Bobby Edward]

Re: How dynamically create WHERE statement...

Or should I just use a FilterExpressio n against my objectdatasourc e?

"Bobby Edward" <bobby@nobody.c omwrote in message
news:%23JB%23RO IQJHA.1164@TK2M SFTNGP03.phx.gb l...

"Mark Rae [MVP]" <mark@markNOSPA Mrae.netwrote in message
news:us4MFBIQJH A.1164@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....
>
>

[Vinay Khaitan]

Re: How dynamically create WHERE statement...

A sincere advice. Never use concatenation of strings. Always use
Parameterized query. It takes less line of code and peace of mind from
security viewpoint..
I think, mysql can also be used with parameterized query, but syntax would
be different.


--
Vinay Khaitan
[Windows Forms Layout Control]

http://www.smart-components.com/

----------------------------------------------------------------


"Bobby Edward" <bobby@nobody.c omwrote in message
news:umLqMrHQJH A.4504@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.
>

[Vinay Khaitan]

Re: How dynamically create WHERE statement...

Searched for you how to use parameterised query with Mysql.

http://forums.asp.net/t/470457.aspx

--
Vinay Khaitan
[Windows Forms Layout Control]

http://www.smart-components.com/

----------------------------------------------------------------


"Vinay Khaitan" <vkhaitan@gmail .comwrote in message
news:%23K8N9GOQ JHA.4144@TK2MSF TNGP06.phx.gbl. ..

>A sincere advice. Never use concatenation of strings. Always use
>Parameterize d query. It takes less line of code and peace of mind from
>security viewpoint..
I think, mysql can also be used with parameterized query, but syntax would
be different.
>
>
--
Vinay Khaitan
[Windows Forms Layout Control]

http://www.smart-components.com/

----------------------------------------------------------------
>
>
"Bobby Edward" <bobby@nobody.c omwrote in message
news:umLqMrHQJH A.4504@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.
>>

>
>