将安全的SQL语句创建为字符串

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:

例如，我有一个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创建insert语句的代码，但这感觉不对。我考虑使用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（）

5 个解决方案

#1

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.

只要您从不将处理数据作为代码混合，您应该没问题。

#2

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();

#3

Fix your replace quotes function this way:

以这种方式修复替换引号功能：

void string replaceQuotes(string value) {
     string tmp = value;
     tmp = tmp.Replace("'", "''");
     return tmp;
}

Cheers!

干杯!

#4

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语句传递给远程服务器？您是否有某种类型的服务，如Web服务，它将接受并执行任意Sql命令，或者您的客户端应用程序是否会直接命中数据库？

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，Binary等）。然后在实际运行命令之前构建SQL。

#5

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），然后在远程服务器上，数据应转换回适当的数据类型并用于参数化查询或存储过程。

#1