创建安全的SQL语句作为字符串 [英] Creating safe SQL statements as strings

问题描述

我使用C＃和.NET 3.5。我需要生成并存储稍后将在远程服务器上执行一些T-SQL INSERT语句。

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.

例如，我有员工的数组：

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')"

我在看一些code，创建insert语句用的String.Format，但感觉不对。我考虑过使用的SqlCommand（希望做<一个href="http://stackoverflow.com/questions/265192/how-to-get-the-generated-sql-statment-from-a-sqlcommand-object">something这样的），但它不提供方式与参数命令文本相结合。

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.

是否足够，以取代单引号，并建立一个字符串？

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.

编辑：只是想指出的是，在这种情况下，我没有一个SqlConnection或任何方式直接连接到SQL Server。这种特殊的应用程序需要生成SQL语句和队列他们去别的地方执行 - 否则我会用SqlCommand.Parameters.AddWithValue（）

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

推荐答案

使用参数化的命令。传递参数以及到远程服务器的欢迎，并获得了调用到SQL Server，仍保持SQL本身和参数值之间的区别。

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.

只要你不混合治疗数据，code，你应该没问题。

As long as you never mix treat data as code, you should be okay.

这篇关于创建安全的SQL语句作为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！