基于用户输入的动态查询构建 [英] Dynamic Query Building on the basis of User input

问题描述

我有一个连接到SQLServer数据库的Windows窗体应用程序。

我有一个搜索屏幕，用户可以在其中输入任何搜索条件并提交信息。

收集信息并将其传递给SQL Server Stored Proc。基于我想要的搜索citerion的动态查询形成。

对于Ex

如果用户输入了emp no



形成的动态查询将是

I have a Windows Form application connecting to a SQLServer DB.
I have a search screen where user can enter any search criterion and submit the information.
The information is collected and passed to an SQL server Stored Proc. where a dynamic query formation based on the search citerion I want to make.
For Ex
if user entered emp no then

the dynamic query formed would be

select * from emp where empNo = p_empno;

如果用户输入了emp no和工资然后

形成的动态查询将是

If the user entered emp no and salary then
the dynamic query formed would be

select * from emp where empNo = p_empno and sal = p_salary;

但如果用户使用随机运算符选择随机列（不同列），我该怎么办？ （和/或）??

but what will i do if user select random column(different columns) using random operators(and /or)??

推荐答案

我不确定随机运算符是什么意思。如果你想形成一个动态的SQL查询，就这样做吧！

I am not sure what do you mean by random operators. If you want to form a dynamic sql query do it this way!

var sql = "select * from emp where 1 = 1";
if(p_empno <> "")
    sql = sql & " AND  empNo = " + p_empno
if(sqlTextBox.Text <> "")
    sql = sql & " AND sal = " + p_salary

检查这个

SelectQueryBuilder ：从C＃构建复杂而灵活的SQL查询/命令 [ ^ ]

用户可选的复杂查询构建器是一件很棒的事情。我希望你不考虑的是让用户在一个文本框中键入搜索逻辑，然后将该文本作为SQL的一部分。这种方法非常容易出错，并且会让您遇到各种SQL注入攻击。



假设您为用户提供了动态配置的一组和/或/ not逻辑，字段名称（您提供，在下拉列表中，可能）和用户实际过滤器值的值输入字段（这些应该是用户唯一的文本输入机会）然后，实际上，您已经控制了列名和运算符 - 您只需要将UI控件树转换为where子句中的一组适当条件。



对于每个文本（或数字，bool或枚举）用户提供的值应该在生成的SQL中放置一个参数占位符，并在SQL命令对象中添加一个参数，在参数上设置值。

A user-selectable, complex query builder is a wonderful thing. What I hope you're not considering is letting the user type search logic into a text box and then using that text as part of your SQL. That approach would be very error prone and would expose you to all sorts of SQL injection attacks.

Assuming you're giving the user a dynamically configured set of and/or/not logic, field names (that you provide, in a drop-down list, perhaps) and value-entry fields for the user's actual filter values (these should be the only text entry opportunities for the user) then, in fact, you are already in control of column names and operators - you just need to translate the tree of UI controls into an appropriate set of conditions in the where clause.

For each text (or numeric, bool or enum) value the user supplies you should put a parameter placeholder in your generated SQL, and add a parameter to the SQL command object, setting the value on the parameter.

这篇关于基于用户输入的动态查询构建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！