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

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

string name = userInput.Value;

然后我构造一个 SQL 查询:

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

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

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

API 使用 SQLServer 作为数据库。

77023 次浏览
小开

Simple:

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

and add the @name parameter with value:

cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@name", name);
小开
最佳答案

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.

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.

小开

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

System.Security.SecurityElement.Escape(Value)
小开

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".

I managed to figure out a work-around (read "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..."

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

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.

小开

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.

小开

SqlCommand and Entity Framework use 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.

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

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

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


提交答案