A "static" query is one that remains the same at all times. For example, the "Tags" button on *, or the "7 days" button on Digg. In short, they always map to a specific database query, so you can create them at design time.
“静态”查询始终保持不变。例如，*上的“标签”按钮，或Digg上的“7天”按钮。简而言之，它们始终映射到特定的数据库查询，因此您可以在设计时创建它们。
But I am trying to figure out how to do "dynamic" queries where the user basically dictates how the database query will be created at runtime. For example, on *, you can combine tags and filter the posts in ways you choose. That's a dynamic query albeit a very simple one since what you can combine is within the world of tags. A more complicated example is if you could combine tags and users.
但我试图弄清楚如何进行“动态”查询，其中用户基本上规定了如何在运行时创建数据库查询。例如，在*上，您可以组合标记并以您选择的方式过滤帖子。这是一个动态查询，虽然是一个非常简单的查询，因为你可以组合在一起的标签世界。一个更复杂的例子是你可以组合标签和用户。
First of all, when you have a dynamic query, it sounds like you can no longer use the substitution api to avoid sql injection since the query elements will depend on what the user decided to include in the query. I can't see how else to build this query other than using string append.
首先，当你有一个动态查询时，听起来你不能再使用替换api来避免sql注入，因为查询元素将取决于用户决定包含在查询中的内容。除了使用字符串append之外，我无法看到如何构建此查询。
Secondly, the query could potentially span multiple tables. For example, if SO allows users to filter based on Users and Tags, and these probably live in two different tables, building the query gets a bit more complicated than just appending columns and WHERE clauses.
其次，查询可能跨越多个表。例如，如果SO允许用户基于用户和标签进行过滤，并且这些可能存在于两个不同的表中，那么构建查询比仅添加列和WHERE子句要复杂一些。
How do I go about implementing something like this?
我该如何实现这样的东西？
4 个解决方案
#1
2
The first rule is that users are allowed to specify values in SQL expressions, but not SQL syntax. All query syntax should be literally specified by your code, not user input. The values that the user specifies can be provided to the SQL as query parameters. This is the most effective way to limit the risk of SQL injection.
第一条规则是允许用户在SQL表达式中指定值，但不允许SQL语法。所有查询语法都应该由您的代码按字面指定，而不是用户输入。用户指定的值可以作为查询参数提供给SQL。这是限制SQL注入风险的最有效方法。
Many applications need to "build" SQL queries through code, because as you point out, some expressions, table joins, order by criteria, and so on depend on the user's choices. When you build a SQL query piece by piece, it's sometimes difficult to ensure that the result is valid SQL syntax.
许多应用程序需要通过代码“构建”SQL查询，因为正如您所指出的，某些表达式，表连接，按标准排序等取决于用户的选择。当您逐个构建SQL查询时，有时很难确保结果是有效的SQL语法。
I worked on a PHP class called Zend_Db_Select that provides an API to help with this. If you like PHP, you could look at that code for ideas. It doesn't handle any query imaginable, but it does a lot.
我参与了一个名为Zend_Db_Select的PHP类，它提供了一个API来帮助解决这个问题。如果你喜欢PHP，你可以查看代码以获取想法。它不处理任何可以想象的查询，但它做了很多。
Some other PHP database frameworks have similar solutions.
其他一些PHP数据库框架也有类似的解决方案。
#2
1
Though not a general solution, here are some steps that you can take to mitigate the dynamic yet safe query issue.
虽然不是一般解决方案，但您可以采取以下步骤来缓解动态但安全的查询问题。
Criteria in which a column value belongs in a set of values whose cardinality is arbitrary does not need to be dynamic. Consider using either the instr function or the use of a special filtering table in which you join against. This approach can be easily extended to multiple columns as long as the number of columns is known. Filtering on users and tags could easily be handled with this approach.
列值属于基数为任意的一组值的标准不需要是动态的。考虑使用instr函数或使用您加入的特殊过滤表。只要知道列数，这种方法就可以很容易地扩展到多个列。使用此方法可以轻松处理对用户和标记的过滤。
When the number of columns in the filtering criteria is arbitrary yet small, consider using different static queries for each possibility.
当过滤条件中的列数是任意的但很小时，请考虑对每种可能性使用不同的静态查询。
Only when the number of columns in the filtering criteria is arbitrary and potentially large should you consider using dynamic queries. In which case...
仅当过滤条件中的列数是任意且可能很大时，才应考虑使用动态查询。在这种情况下...
To be safe from SQL injection, either build or obtain a library that defends against that attack. Though more difficult, this is not an impossible task. This is mostly about escaping SQL string delimiters in the values to filter for.
为了安全地从SQL注入，要么构建或获得防御该攻击的库。虽然更难，但这不是一项不可能完成的任务。这主要是关于要过滤的值中的SQL字符串分隔符的转义。
To be safe from expensive queries, consider using views that are specially crafted for this purpose and some up front logic to limit how those views will get invoked. This is the most challenging in terms of developer time and effort.
为了避免昂贵的查询，请考虑使用专门为此目的而构建的视图和一些前期逻辑来限制这些视图的调用方式。就开发人员的时间和精力而言，这是最具挑战性的。
#3
0
If you were using python to access your database, I would suggest you use the Django model system. There are many similar apis both for python and for other languages (notably in ruby on rails). I am saving so much time by avoiding the need to talk directly to the database with SQL.
如果您使用python访问数据库，我建议您使用Django模型系统。 python和其他语言都有许多类似的api（特别是在rails上的ruby）。我通过避免使用SQL直接与数据库通信来节省大量时间。
From the example link:
从示例链接：
#Model definition
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __unicode__(self):
return self.name
Model usage (this is effectively an insert statement)
模型用法（这实际上是一个插入语句）
from mysite.blog.models import Blog
b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
b.save()
The queries get much more complex - you pass around a query object and you can add filters / sort elements to it. When you finally are ready to use the query, Django creates an SQL statment that reflects all the ways you adjusted the query object. I think that it is very cute.
查询变得更加复杂 - 您传递查询对象，并且可以向其添加过滤器/排序元素。当您最终准备好使用查询时，Django会创建一个SQL语句，该语句反映您调整查询对象的所有方法。我觉得它很可爱。
Other advantages of this abstraction
这种抽象的其他优点
- Your models can be created as database tables with foreign keys and constraints by Django
- 您的模型可以创建为具有外键和Django约束的数据库表
- Many databases are supported (Postgresql, Mysql, sql lite, etc)
- 支持许多数据库（Postgresql，Mysql，sql lite等）
- DJango analyses your templates and creates an automatic admin site out of them.
- DJango分析您的模板并创建一个自动管理站点。
#4
0
Well the options have to map to something.
那些选项必须映射到某些东西。
A SQL query string CONCAT isn't a problem if you still use parameters for the options.
如果您仍然使用选项的参数，则SQL查询字符串CONCAT不是问题。
#1
2
The first rule is that users are allowed to specify values in SQL expressions, but not SQL syntax. All query syntax should be literally specified by your code, not user input. The values that the user specifies can be provided to the SQL as query parameters. This is the most effective way to limit the risk of SQL injection.
第一条规则是允许用户在SQL表达式中指定值，但不允许SQL语法。所有查询语法都应该由您的代码按字面指定，而不是用户输入。用户指定的值可以作为查询参数提供给SQL。这是限制SQL注入风险的最有效方法。
Many applications need to "build" SQL queries through code, because as you point out, some expressions, table joins, order by criteria, and so on depend on the user's choices. When you build a SQL query piece by piece, it's sometimes difficult to ensure that the result is valid SQL syntax.
许多应用程序需要通过代码“构建”SQL查询，因为正如您所指出的，某些表达式，表连接，按标准排序等取决于用户的选择。当您逐个构建SQL查询时，有时很难确保结果是有效的SQL语法。
I worked on a PHP class called Zend_Db_Select that provides an API to help with this. If you like PHP, you could look at that code for ideas. It doesn't handle any query imaginable, but it does a lot.
我参与了一个名为Zend_Db_Select的PHP类，它提供了一个API来帮助解决这个问题。如果你喜欢PHP，你可以查看代码以获取想法。它不处理任何可以想象的查询，但它做了很多。
Some other PHP database frameworks have similar solutions.
其他一些PHP数据库框架也有类似的解决方案。
#2
1
Though not a general solution, here are some steps that you can take to mitigate the dynamic yet safe query issue.
虽然不是一般解决方案，但您可以采取以下步骤来缓解动态但安全的查询问题。
Criteria in which a column value belongs in a set of values whose cardinality is arbitrary does not need to be dynamic. Consider using either the instr function or the use of a special filtering table in which you join against. This approach can be easily extended to multiple columns as long as the number of columns is known. Filtering on users and tags could easily be handled with this approach.
列值属于基数为任意的一组值的标准不需要是动态的。考虑使用instr函数或使用您加入的特殊过滤表。只要知道列数，这种方法就可以很容易地扩展到多个列。使用此方法可以轻松处理对用户和标记的过滤。
When the number of columns in the filtering criteria is arbitrary yet small, consider using different static queries for each possibility.
当过滤条件中的列数是任意的但很小时，请考虑对每种可能性使用不同的静态查询。
Only when the number of columns in the filtering criteria is arbitrary and potentially large should you consider using dynamic queries. In which case...
仅当过滤条件中的列数是任意且可能很大时，才应考虑使用动态查询。在这种情况下...
To be safe from SQL injection, either build or obtain a library that defends against that attack. Though more difficult, this is not an impossible task. This is mostly about escaping SQL string delimiters in the values to filter for.
为了安全地从SQL注入，要么构建或获得防御该攻击的库。虽然更难，但这不是一项不可能完成的任务。这主要是关于要过滤的值中的SQL字符串分隔符的转义。
To be safe from expensive queries, consider using views that are specially crafted for this purpose and some up front logic to limit how those views will get invoked. This is the most challenging in terms of developer time and effort.
为了避免昂贵的查询，请考虑使用专门为此目的而构建的视图和一些前期逻辑来限制这些视图的调用方式。就开发人员的时间和精力而言，这是最具挑战性的。
#3
0
If you were using python to access your database, I would suggest you use the Django model system. There are many similar apis both for python and for other languages (notably in ruby on rails). I am saving so much time by avoiding the need to talk directly to the database with SQL.
如果您使用python访问数据库，我建议您使用Django模型系统。 python和其他语言都有许多类似的api（特别是在rails上的ruby）。我通过避免使用SQL直接与数据库通信来节省大量时间。
From the example link:
从示例链接：
#Model definition
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __unicode__(self):
return self.name
Model usage (this is effectively an insert statement)
模型用法（这实际上是一个插入语句）
from mysite.blog.models import Blog
b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
b.save()
The queries get much more complex - you pass around a query object and you can add filters / sort elements to it. When you finally are ready to use the query, Django creates an SQL statment that reflects all the ways you adjusted the query object. I think that it is very cute.
查询变得更加复杂 - 您传递查询对象，并且可以向其添加过滤器/排序元素。当您最终准备好使用查询时，Django会创建一个SQL语句，该语句反映您调整查询对象的所有方法。我觉得它很可爱。
Other advantages of this abstraction
这种抽象的其他优点
- Your models can be created as database tables with foreign keys and constraints by Django
- 您的模型可以创建为具有外键和Django约束的数据库表
- Many databases are supported (Postgresql, Mysql, sql lite, etc)
- 支持许多数据库（Postgresql，Mysql，sql lite等）
- DJango analyses your templates and creates an automatic admin site out of them.
- DJango分析您的模板并创建一个自动管理站点。
#4
0
Well the options have to map to something.
那些选项必须映射到某些东西。
A SQL query string CONCAT isn't a problem if you still use parameters for the options.
如果您仍然使用选项的参数，则SQL查询字符串CONCAT不是问题。