在ADO.NET中，是否有限制，其中SQL参数可以在SQL查询中使用？ [英] In ADO.NET, are there restrictions where SQL parameters can be used in the SQL query?

问题描述

这个问题仅仅是用于教育目的，因为我目前没有构建一个版本的SQL查询，用户输入的任何应用程序。

This question is merely for educational purposes, as I'm not currently building any application that builds SQL queries with user input.

这是说，我知道，在ADO.NET你可以做这样的事情prevent SQL注入：

That said, I know that in ADO.NET you can prevent SQL Injection by doing something like this:

OleDbCommand command = new OleDbCommand("SELECT * FROM Table WHERE Account = @2", connection);
command.Parameters.AddWithValue("@2", "ABC");

不过，假设您的应用程序的设计是这样一种方式，用户可以实际输入表的名称，你可以做以下？ （我不在乎，如果这是一个坏主意，让用户提供表的名字，我只是想知道，如果下面是可能的......）

But assuming that your application is designed in such a way that the user can actually enter the name of the table, can you do the following? (I don't care if it's a bad idea to allow the users to supply the name of the table, I just want to know if the following is possible...)

OleDbCommand command = new OleDbCommand("SELECT * FROM @1 WHERE Account = @2", connection);
command.Parameters.AddWithValue("@1", "Table");
command.Parameters.AddWithValue("@2", "ABC");

我不断收到一个例外，当我运行第二个code，称该SQL查询是不完整的，我还是想知道，问题是什么，我试图做根本不能做，如果我可以俯瞰什么的。

I keep getting an exception when I run the second code, saying that the SQL query is incomplete, and I was wondering if the problem is that what I am trying to do simply cannot be done or if I am overlooking something.

推荐答案

没有，查询参数可以substitue对于一个标量值在SQL语句中。
例如，一个字符串，日期文字或数字字符。

No, a query parameter can substitue for one scalar value in your SQL statement.
For example, a single string literal, a date literal, or a numeric literal.

有不必是WHERE子句中。任何地方，你可以有一个前pression在SQL中，可以包括标量值，因此参数。例如，在连接条件，或在选择列表或ORDER BY或GROUP BY子句。

It doesn't have to be in the WHERE clause. Anywhere you can have an expression in SQL, you can include a scalar value, and therefore a parameter. For example, in join conditions, or in the select-list, or in ORDER BY or GROUP BY clauses.

您的不能的使用查询参数：

• 表标识符
• 列标识符
• SQL关键字
• 在SQL EX pressions
• 值（例如，在一个IN（）predicate）名单

如果您需要任何查询用户自定义的这些地方，那么你需要建立通过插入或串联应用程序变量到字符串的SQL查询字符串。这使得它很难对SQL注入防御。

If you need to make any of these parts of your query user-definable, then you need to build the SQL query string by interpolating or concatenating application variables into the string. This makes it difficult to defend against SQL injection.

在这种情况下，最好的防御就是白名单的是安全插值到SQL字符串，例如一组您在code定义表名的特定值。让用户选择从这些pre认可值的表，但不使用他们逐字输入在SQL code，你再执行。

The best defense in that case is to whitelist specific values that are safe to interpolate into your SQL string, for instance a set of table names that you define in your code. Let the user choose a table from these pre-approved values, but don't use their input verbatim in SQL code that you then execute.

用户输入可以提供值，但是不应该提供code。

您可能会发现我的presentation SQL注入神话和谬误帮助。我盖白名单中的presentation（我的例子是在PHP中，但这个想法适用于任何编程语言）。

You may find my presentation SQL Injection Myths and Fallacies helpful. I cover whitelisting in that presentation (my examples are in PHP, but the idea applies to any programming language).

这篇关于在ADO.NET中，是否有限制，其中SQL参数可以在SQL查询中使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！