如何动态创建WHERE语句... [英] How dynamically create WHERE statement...
问题描述
我有一个高级搜索框。用户可以在
框中输入多个单词。然后在WHERE子句中使用这些单词对描述db
字段。
所以这些话：管道工木匠电工
基本上等同于：WHERE（描述LIKE''％管道工％''）或
（描述LIKE''％carpenter％''）或（描述LIKE''％电工％''）"
有没有简单的方法来动态创建这个WHERE clasue？我知道如何通过代码手动执行它，但是我不知道是否必须手动解析
令牌并构造该子句或者是否有更方便...
（我正在使用MySQL。）
谢谢。
" Bobby Edward" < bo *** @ nobody.comwrote in message
news：嗯************** @ TK2MSFTNGP02.phx.gbl ...
我有一个高级搜索框。用户可以在
框中输入多个单词。这些单词然后在WHERE子句中用于描述
db字段。
所以这些话：水管工木匠电工
基本上等同于：WHERE（描述LIKE''％管道工％''）或
（描述LIKE''％carpenter％''）或（描述LIKE''％电工％''）"
有没有简单的方法来动态创建这个WHERE clasue？我知道如何通过代码手动执行它，但我不知道是否必须手动解析代币并构造子句或者如果有的话更简单的方法...
在没有任何情况下这样做!!!您的解决方案绝对是开放的。
SQL注入：
http://www.google.co.uk/search?sourc .. .L +注入％22
相反，允许用户选择他们感兴趣的职业，例如通过滴答作响来支付
复选框或其他一些技巧 - 基本上，任何要避免的东西
动态SQL ...
-
Mark Rae
ASP.NET MVP
http://www.markrae.net
你可以在这里阅读我的看法：
http://www.sqlservercentral.com/arti...rproblem/2283/
零到N段仪表问题
Bobby Edward < bo *** @ nobody.comwrote in message
news：um ************** @ TK2MSFTNGP02.phx.gbl ...
>我有一个高级搜索框。用户可以在
框中输入多个单词。这些单词然后在WHERE子句中用于描述
数据库字段。
所以这些话：管道工木匠电工
本质上等同于：WHERE（描述LIKE''％管道工％''）或
（描述LIKE''％carpenter％''）或（描述LIKE''％electrician％''' ）
有没有简单的方法来动态创建这个WHERE clasue？我知道如何通过代码手动执行它，但我不知道是否必须手动解析代币并构造子句或者如果有的话更简单的方式...
（我正在使用MySQL。）
谢谢。
" Mark Rae [MVP]" < ma ** @ markNOSPAMrae.netwrote in message
news：us ************** @ TK2MSFTNGP03.phx.gbl ...
< blockquote class =post_quotes>
>
在没有任何情况下这样做!!!您的解决方案绝对是开放的。
SQL注入：
http://www.google.co.uk/search?sourc .. .L +注入％22
相反，允许用户选择他们感兴趣的职业
例如通过勾选复选框或其他一些技巧 - 基本上，任何东西
以避免动态SQL ...
我非常感谢Mark。但是，如果我希望用户搜索
以获取任何类型的单词，该怎么办？它可能不是我可以列出的东西。
我不能只是清理字符串，例如通过忽略以下
字/特殊我创建WHERE时的字符：
DELETE
删除
DROP
SELECT
更新
INSERT
WHERE
*
％
;
..
等....
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" <bo***@nobody.comwrote in message
news:um**************@TK2MSFTNGP02.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
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.comwrote in message
news:um**************@TK2MSFTNGP02.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.
"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:us**************@TK2MSFTNGP03.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....
这篇关于如何动态创建WHERE语句...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
