逃避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屋!