逃避SQL 2005/2008双引号 [英] Escape double quotes in SQL 2005/2008

问题描述

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

当用户试图寻找这家公司，通过输入胡说，或东西的影响，搜索失败，并在SQL Server中的语法错误。

我

如何才能摆脱这种所以搜索不会失败？

SQL示例：

  SELECT c.companyID，c.companyName，c.dateAdded，计数（cm.maxID）作为NumDirect
    从RussoundGeneral.dbo.CompanyÇ
         LEFT JOIN RussoundGeneral.dbo.CompanyMax厘米
                ON（cm.companyId = c.companyId和cm.maxID不为null）
    WHERE CONTAINS（公司名称，'BLAH *'）
    GROUP BY c.companyID，c.companyName，c.dateAdded
    ORDER BY c.companyName ASC
 

解决方案

不幸的是，双引号里面有FTI特殊的意义，所以即使你参数吧，FTI引擎把它当作一个短语分隔符。我不知道有一个简单的办法，包括在FTI搜索双引号。支架也是一个特殊的字符，但是可以在引号当作一个查询词被包裹 - 但据我所知没有双引号

更新

搜索的一点建议，加倍报价为可以解决这个问题 - 值得一试。就个人而言，我会做这个数据库里面，因为这是一个TSQL实​​现细节。

同样，必须加倍到'传递给FTI（完全独立于TSQL转义）

在'

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

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.

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

Sample 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

解决方案

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.

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.

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

这篇关于逃避SQL 2005/2008双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！