如何为SqlServer转义c#中的简单SQL查询

I use an API that expects a SQL string. I take a user input, escape it and pass it along to the API. The user input is quite simple. It asks for column values. Like so:

我使用的API需要SQL字符串。我获取一个用户输入，转义它并将它传递给API。用户输入非常简单。它要求列值。像这样:

string name = userInput.Value;

Then I construct a SQL query:

然后构造一个SQL查询:

string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'",
                           name.replace("'", "''"));

Is this safe enough? If it isn't, is there a simple library function that make column values safe:

这是足够安全吗?如果不是，是否有一个简单的库函数可以使列值安全:

string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'",
                           SqlSafeColumnValue(name));

The API uses SQLServer as the database.

API使用SQLServer作为数据库。

7 个解决方案

#1

Since using SqlParameter is not an option, just replace ' with '' (that's two single quotes, not one double quote) in the string literals. That's it.

因为使用SqlParameter不是一个选项，所以只需在字符串常量中用“”(即两个单引号，而不是一个双引号)替换即可。就是这样。

To would-be downvoters: re-read the first line of the question. "Use parameters" was my gut reaction also.

对于潜在的选民:重读问题的第一行。“使用参数”也是我的本能反应。

EDIT: yes, I know about SQL injection attacks. If you think this quoting is vulnerable to those, please provide a working counterexample. I think it's not.

编辑:是的，我知道SQL注入攻击。如果你认为这句话容易被那些人引用，请提供一个有效的反例。我认为它不是。

#2

Simple:

简单:

const string sql = "SELECT * FROM SOME_TABLE WHERE Name = @name";

and add the @name parameter with value:

添加带有值的@name参数:

cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@name", name);

#3

I was using dynamic sql (I can hear the firing squad loading their rifles) for search functionality, but it would break whenever a user searched for somebody with a surname like "O'Reilly".

我使用动态sql(我能听到枪林弹雨的枪林弹雨)作为搜索功能，但当用户搜索一个姓“O'Reilly”的人时，它就会失效。

I managed to figure out a work-around (read "hack"):

我想出了一个变通的办法(读作“hack”):

Created a scalar-valued function in sql that replaced a single quote with two single quotes, effectively escaping the offending single quote, so
"...Surname LIKE '%O'Reilly%' AND..." becomes "...Surname LIKE '%O''Reilly%' AND..."

在sql中创建了一个标量值函数，用两个单引号替换一个单引号，有效地避免了出现问题的单引号，所以……像'%O' reilly '% '和…'变成…姓“% O " Reilly %”和……”

This function gets invoked from within sql whenever I suspect fields could contain a single quote character ie: firstname, lastname.

每当我怀疑字段可能包含一个引用字符ie: firstname、lastname时，这个函数就会从sql中调用。

CREATE FUNCTION [dbo].[fnEscapeSingleQuote]
    (@StringToCheck NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)
    SELECT @Result = REPLACE(@StringToCheck, CHAR(39), CHAR(39) + CHAR(39))
    RETURN @Result
END

Not very elegant or efficient, but it works when you're in a pinch.

不是很优雅或者高效，但是当你需要的时候，它可以工作。

#4

One may wish to replace ' with '' instead of parameterizing when needing to address the ' problem in a large amount of ad hoc sql in a short time with minimal risk of breakage and minimal testing.

当需要在短时间内以最小的破损风险和最小的测试处理大量特定sql中的问题时，我们可能希望用“with”代替参数化。

#5

It is best to use sql parameters, but then you have limitation for 2300 parameters for query. In most of the cases this will be more than enough. But in rare cases when you exceed this limit, i see this as an option.

最好使用sql参数，但是查询有2300个参数的限制。在大多数情况下，这就足够了。但在极少数情况下，当你超过这个限度时，我认为这是一个选择。

#6

SqlCommand and Entity Framework use exec sp_executesql....

SqlCommand和实体框架使用exec sp_executesql ....

So there really is an alternative to raw strings with your own escaping pattern presumably. With SqlCommand you are technically using parameterised queries but you're bypassing the ADO.Net abstraction of the underlying SQL code.

所以我们可以用自己的转义模式来替代原始字符串。使用SqlCommand，您在技术上使用的是参数化查询，但您绕过了ADO。底层SQL代码的净抽象。

So while your code doesn't prevent SQL Injection, the ultimate answer is sp_executesql not SqlCommand.

因此，虽然代码没有阻止SQL注入，但最终的答案是sp_executesql而不是SqlCommand。

Having said that, I'm sure there are special handling requirements for generating an SQL Injection-proof string which utilizes sp_executesql.

话虽如此，我确信对于生成使用sp_executesql的SQL注入证明字符串有特殊的处理需求。

see: How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

参见:如何将动态SQL存储过程中的值返回到实体框架?

#7

-11

If you need to escape a string for a MSSQL query try this:

如果需要为MSSQL查询转义字符串，请尝试以下操作:

System.Security.SecurityElement.Escape(Value)

#1