How dynamically create WHERE statement...

  • Thread starter Thread starter Bobby Edward
  • Start date Start date
B

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 said:
UNDER NO CIRCUMSTANCES do this!!! Your solution is absolutely wide open to
SQL Injection:
http://www.google.co.uk/search?sour...rlz=1T4GPTB_en-GBGB298GB298&q="SQL+Injection"

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...
Click to expand...

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....
 
The url not super "dynamic". But it has a mechanism for parameters.

The previous post is very correct. SQL Injection will mess you up.
 
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
Click to expand...

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.replace("*","") ' strip more
strSearch = strSearch.replace("..... ' keep stripping them out
strSearch = strSearch.replace("DROP","") ' remove db type words
strSearch = strSearch.replace("DELETE","") ' remove db type words
strSearch = strSearch.replace("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...
 
Bobby said:
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.)
Click to expand...

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.VarChar);
param.Value = "%" + "plumber" + "%";
cmd.Parameters.Add(param);

param =
new SqlParameter("@Description2", SqlDbType.VarChar);
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.Text;
cmd.Connection = conn;
SqlDataReader sdr = cmd.ExecuteReader();
 
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! ;)
 
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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top