C# 创建安全的 SQL 语句作为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/293254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating safe SQL statements as strings
提问by Jason Anderson
I'm using C# and .NET 3.5. I need to generate and store some T-SQL insert statements which will be executed later on a remote server.
我正在使用 C# 和 .NET 3.5。我需要生成并存储一些稍后将在远程服务器上执行的 T-SQL 插入语句。
For example, I have an array of Employees:
例如，我有一组员工：
new Employee[]
{
new Employee { ID = 5, Name = "Frank Grimes" },
new Employee { ID = 6, Name = "Tim O'Reilly" }
}
and I need to end up with an array of strings, like this:
我需要得到一个字符串数组，如下所示：
"INSERT INTO Employees (id, name) VALUES (5, 'Frank Grimes')",
"INSERT INTO Employees (id, name) VALUES (6, 'Tim O''Reilly')"
I'm looking at some code that creates the insert statements with String.Format, but that doesn't feel right. I considered using SqlCommand (hoping to do something like this), but it doesn't offer a way to combine the command text with parameters.
我正在查看一些使用 String.Format 创建插入语句的代码，但这感觉不对。我考虑过使用 SqlCommand（希望做这样的事情），但它没有提供将命令文本与参数组合的方法。
Is it enough just to replace single quotes and build a string?
仅替换单引号并构建字符串就足够了吗？
string.Format("INSERT INTO Employees (id, name) VALUES ({0}, '{1}')",
employee.ID,
replaceQuotes(employee.Name)
);
What should I be concerned about when doing this? The source data is fairly safe, but I don't want to make too many assumptions.
这样做时我应该注意什么？源数据相当安全，但我不想做太多假设。
EDIT: Just want to point out that in this case, I don't have a SqlConnection or any way to directly connect to SQL Server. This particular app needs to generate sql statements and queue them up to be executed somewhere else - otherwise I'd be using SqlCommand.Parameters.AddWithValue()
编辑：只是想指出，在这种情况下，我没有 SqlConnection 或任何直接连接到 SQL Server 的方法。这个特定的应用程序需要生成 sql 语句并将它们排队以在其他地方执行 - 否则我将使用 SqlCommand.Parameters.AddWithValue()
采纳答案by Jon Skeet
Use parameterised commands. Pass the parameters along to your remote server as well, and get that to call into SQL Server, still maintaining the distinction between the SQL itself and the parameter values.
使用参数化命令。也将参数传递到远程服务器，并将其调用到 SQL Server 中，同时仍保持 SQL 本身与参数值之间的区别。
As long as you never mix treat data as code, you should be okay.
只要您从不混合将数据视为代码，就应该没问题。
回答by Mitch Wheat
Create your SqlCommand object like so:
像这样创建你的 SqlCommand 对象：
SqlCommand cmd = new SqlCommand(
"INSERT INTO Employees (id, name) VALUES (@id, @name)", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = employee.ID;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@name";
param.Value = employee.Name;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
回答by Cade Roux
To avoid injection, you need to ship the data to the remote server (perhaps in XML) and then on the remote server, the data should be converted back to appropriate data types and used in parameterized queries or stored procs.
为避免注入，您需要将数据传送到远程服务器（可能是 XML 格式），然后在远程服务器上，应将数据转换回适当的数据类型并用于参数化查询或存储过程。
回答by JoshBerke
Hmm I agree with everyone else that you should be using parameterized queries and will leave it at that. But how are you going pass these sql statements to your remote server? Do you have some type of service like a web service which will accept and execute arbitrary Sql commands or is your client app going to hit the DB directly?
嗯，我同意其他人的看法，即您应该使用参数化查询，并将保留它。但是你如何将这些 sql 语句传递到你的远程服务器呢？您是否有某种类型的服务，例如可以接受和执行任意 Sql 命令的 Web 服务，或者您的客户端应用程序是否会直接访问数据库？
If your going through some sort of proxy then no matter how much you sanitize your data on the client, a hacker could just bypass your app and hit the service. In which case do what Cade recommends and pass the data as XML for example or whatever format you choose (JSON, Binary etc..) Then build your SQL right before you actually run the command.
如果您通过某种代理，那么无论您对客户端上的数据进行多少清理，黑客都可以绕过您的应用程序并攻击该服务。在这种情况下，按照 Cade 的建议，将数据作为 XML 或您选择的任何格式（JSON、二进制等）传递。然后在实际运行命令之前构建您的 SQL。
回答by KMX
Fix your replace quotes function this way:
以这种方式修复替换引号功能：
void string replaceQuotes(string value) {
string tmp = value;
tmp = tmp.Replace("'", "''");
return tmp;
}
Cheers!
干杯!
