C# 在 SQL 2005/2008 中转义双引号

Escape double quotes in SQL 2005/2008

提问by Russ

I have an international company that has recently been added, which is named "BLA "BLAHBLAH" Ltd. (The double quotes are part of the name. )

我最近已经增加了一个国际化的公司，它被命名为“BLA‘BLAHBLAH’有限责任公司（双引号是名称的一部分。）

Whenever a user tries to search for this company, by entering "Blah, or something to that affect, the search fails with a syntax error in SQL server.

每当用户尝试搜索这家公司时，通过输入“废话”或其他相关内容，搜索都会失败，并在 SQL 服务器中出现语法错误。

How can I escape this so the search will not fail?

我怎样才能逃避这个，这样搜索就不会失败？

Sample SQL:

示例 SQL：

SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect
    FROM RussoundGeneral.dbo.Company c  
         LEFT JOIN RussoundGeneral.dbo.CompanyMax cm
                ON (cm.companyId = c.companyId and cm.maxID is not null)  
    WHERE CONTAINS ( companyName,  '"BLAH*' )
    GROUP BY c.companyID, c.companyName, c.dateAdded  
    ORDER BY c.companyName ASC

采纳答案by Marc Gravell

Unfortunately, double-quotes have special meaning inside FTI, so even if you parameterize it, the FTI engine treats it as a phrase delimiter. I am not sure there is an easy way to include double-quotes in an FTI search. Brackets are also a special character, but can be encased in quotes to treat as a query term - but not AFAIK double-quotes.

不幸的是，双引号里面有FTI特殊的意义，所以即使你参数吧，FTI引擎把它当作一个短语分隔符。我不确定是否有一种简单的方法可以在 FTI 搜索中包含双引号。支架也是一个特殊的字符，但是可以用引号包裹治疗作为查询检索词 - 但不是AFAIK双引号。

Update

更新

A bit of searching suggests that doubling the quote to "" may fix it - worth a try. Personally, I'd do this inside the DB, since this is a TSQL implementation detail.

一些搜索表明，将引号加倍到 "" 可能会解决它 - 值得一试。就个人而言，我会在 DB 中执行此操作，因为这是一个 TSQL 实现细节。

Likewise, ' needs to be doubled to '' before passing to FTI (completely separate to TSQL escaping),

同样，在传递给 FTI（完全独立于 TSQL 转义）之前，' 需要加倍为 ''，

回答by Jon Skeet

I strongly suspect you're building the SQL dynamically - e.g.

我强烈怀疑您正在动态构建 SQL - 例如

// Bad code, do not use!
string sql = "SELECT * FROM Foo WHERE X LIKE '" + input + "%'";

That's a really, really bad idea for many reasons - most notably SQL injection attacks. Use parameterised SQL statements instead, where you specify the parameters separately.

出于多种原因，这是一个非常非常糟糕的主意 - 最显着的是SQL 注入攻击。请改用参数化 SQL 语句，在其中单独指定参数。

Look at various answers to questions with the sql-injection tagfor examples of how to do this properly.

查看带有sql-injection 标签的问题的各种答案，以获取有关如何正确执行此操作的示例。

回答by Robert C. Barth

Use a parameterized query and all your quoting woes will be gone.

使用参数化查询，您所有的引用问题都将消失。

Edit: If you're not letting them enter more than one word in the CONTAINS, sanitize the parameter by removing the quotes. Sanitizing the input by removing the quotes may work anyhow, regardless of the multi-word search.

编辑：如果您不允许他们在 CONTAINS 中输入多个单词，请通过删除引号来清理参数。不管多词搜索如何，通过删除引号来清理输入可能无论如何都有效。

回答by HLGEM

have you tried substituting the character with it's ASCII code?

你有没有试过用它的 ASCII 代码替换字符？

回答by user25623

Try Using the escape keyword:

尝试使用转义关键字：

SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect 
FROM RussoundGeneral.dbo.Company c          
LEFT JOIN RussoundGeneral.dbo.CompanyMax cm 
ON (cm.companyId = c.companyId and cm.maxID is not null )  
WHERE CONTAINS ( companyName,  '\"BLAH*' ) escape '\'
group by c.companyID, c.companyName, c.dateAdded  ORDER BY c.companyName ASC

回答by Russ Bradberry

should be something like

应该是这样的

string sqlCommand = "SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null ) WHERE CONTAINS ( companyName,  '@strVal' ) group by c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC"
SqlCommand command = new SqlCommand(strSQLCommand, conn); 
SqlCommand.Parameters.AddWithValue("@strval", SearchTextBox.Text); 

回答by Jonathan Leffler

This is a bit of a theoretical answer, but maybe it will help. The short version is "use parameters in the query", but it helps to understand the full details.

这是一个理论上的答案，但也许会有所帮助。简短版本是“在查询中使用参数”，但它有助于理解完整的细节。

In standard SQL, strings are enclosed in single quotes, and embedded single quotes are represented by two single quotes in a row:

在标准 SQL 中，字符串用单引号括起来，嵌入的单引号由一行中的两个单引号表示：

SELECT * FROM SomeWhere
    WHERE SomeThing = 'He said, "Don''t do it!"';

In some dialects of SQL, you can instead close strings in double quotes; you then need to double the double quotes to embed a single instance of a double quote:

在某些 SQL 方言中，您可以用双引号将字符串括起来；然后，您需要将双引号加倍以嵌入双引号的单个实例：

SELECT * FROM SomeWhere
    WHERE SomeThing = "He said, ""Don't do it!""';

It isn't clear from the question whether the company name includes the outer double quotes as well as the middle one, or if it just contains the middle one. However, in principle, the rules are the same. Assuming all three double quotes are required, and using single quotes in the SQL - much easier in this context:

从问题中不清楚公司名称是否包含外部双引号和中间的双引号，或者是否只包含中间的双引号。但是，原则上，规则是相同的。假设需要所有三个双引号，并在 SQL 中使用单引号 - 在这种情况下更容易：

SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect
    FROM RussoundGeneral.dbo.Company c  
         LEFT JOIN RussoundGeneral.dbo.CompanyMax cm
                ON (cm.companyId = c.companyId and cm.maxID is not null)  
    WHERE CONTAINS ( companyName,  '"BLAH "BLAHBLAH" Ltd.' )
    GROUP BY c.companyID, c.companyName, c.dateAdded  
    ORDER BY c.companyName ASC;

Using double quotes:

使用双引号：

SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect
    FROM RussoundGeneral.dbo.Company c  
         LEFT JOIN RussoundGeneral.dbo.CompanyMax cm
                ON (cm.companyId = c.companyId and cm.maxID is not null)  
    WHERE CONTAINS ( companyName,  """BLAH ""BLAHBLAH"" Ltd." )
    GROUP BY c.companyID, c.companyName, c.dateAdded  
    ORDER BY c.companyName ASC;

If you are building strings in a programming language, then you have to worry about getting these quotes past whatever evaluates strings in your programming language. For example, if you were building a string literal in C, you'd have to escape the double quotes with backslashes:

如果您正在用编程语言构建字符串，那么您必须担心让这些引号超过您的编程语言中对字符串求值的任何内容。例如，如果您在 C 中构建字符串文字，则必须使用反斜杠转义双引号：

static const char sql_stmt[] =
"SELECT c.companyID, c.companyName, c.dateAdded,\n"
"       COUNT(cm.maxID) AS NumDirect\n"
"    FROM RussoundGeneral.dbo.Company c\n"
"         LEFT JOIN RussoundGeneral.dbo.CompanyMax cm\n"
"                ON (cm.companyId = c.companyId AND cm.maxID IS NOT NULL)\n"  
"    WHERE CONTAINS(companyName,  \"\"\"BLAH \"\"BLAHBLAH\"\" Ltd.\")\n"
"    GROUP BY c.companyID, c.companyName, c.dateAdded\n"
"    ORDER BY c.companyName ASC";

On the other hand, if you read the data from the user - such as the company name, then you simply have to ensure what is read is correctly quoted.

另一方面，如果您从用户那里读取数据——例如公司名称，那么您只需确保读取的内容被正确引用。

Those who said "use parameters" are right - it is much easier and more reliable and less vulnerable to SQL injection attacks (see XKCDif you haven't already see it). But if you understand the fundamentals, you can adapt to the actual requirements of your system.

那些说“使用参数”的人是对的——它更容易、更可靠并且更不容易受到 SQL 注入攻击（如果你还没有看到它，请参阅XKCD）。但是，如果您了解基本原理，就可以适应系统的实际要求。

Final note: in standard SQL, double quotes enclose 'delimited identifiers'. That is, the double quotes surround a name which must be treated as the name of something in the database, not as a string literal. In MS SQL Server, the [square brackets] serve the same purpose; what is between the brackets is the name of a column or whatever inside the database. Many systems are more flexible than that; not all systems are the same in how they deviate from the standard.

最后一点：在标准 SQL 中，双引号将“分隔标识符”括起来。也就是说，双引号包围一个名称，该名称必须被视为数据库中某物的名称，而不是字符串文字。在 MS SQL Server 中，[方括号] 的作用相同；括号之间是列的名称或数据库内的任何名称。许多系统比这更灵活；并非所有系统在偏离标准的方式上都相同。

回答by Philippe Grondier

You will finally have to extract data from your database and have it displayed on screen or printed on reports. Manipulating double quotes or any extra character can then become very confusing.

您最终必须从数据库中提取数据并将其显示在屏幕上或打印在报告上。操作双引号或任何额外的字符可能会变得非常混乱。

By converting your strings to HTML before INSERTS or UPDATES, you are avoiding all the confusion linked to quotes management. At SELECT time, it will be easy to convert back from HTML. At reporting time (as reporting tools (such as Crystal Reports) propose an HTML formatting option), you will not even have to do anything to display data in a correct way.

通过在 INSERTS 或 UPDATES 之前将您的字符串转换为 HTML，您可以避免与引号管理相关的所有混淆。在 SELECT 时，很容易从 HTML 转换回来。在报告时（因为报告工具（如 Crystal Reports）提出了 HTML 格式选项），您甚至不需要做任何事情来以正确的方式显示数据。

By the way, do not forget to hang the guy that invented this company name.

顺便说一句，不要忘记挂掉发明这个公司名称的人。