动态sql和如何转义特殊字符？

I have a stored procedure which searches for names based on a string.

我有一个存储过程,它根据字符串搜索名称。

if I pass in @SearchTerm as the following value: o'clock

如果我传入@SearchTerm作为以下值:o

SET @NameSearch = ' (CONTAINS(lmc.Name,''"*' + REPLACE(@SearchTerm,'''','''''') + '*"'')) '

@NameSearch would be set to:

@NameSearch将设置为:

"*o''clock*"

this would return no rows.

这将不返回任何行。

however if I just pass in 'clock' then I will get all the results which have a name that contains the word 'clock'.

但是,如果我只传入'clock',那么我将得到所有结果,其名称包含'clock'这个词。

could someone explain to me how I would be able to escape the ' properly.

有人可以向我解释我是如何能够逃脱'正确的。

2 个解决方案

#1

You should use parametrized query. Here's an example:

您应该使用参数化查询。这是一个例子:

DECLARE @sql nvarchar(max), @paramlist nvarchar(max)

SELECT @sql= 'SELECT Test_Name
              FROM [Test]
              WHERE (1 = 1)'

SELECT @sql = @sql + ' AND (Test_Name LIKE (@Name + ''Toto''))'

SELECT @paramlist = '@Name nvarchar (256)'
EXEC sp_executesql @sql, @paramlist, @SearchTerm

#2

1. You wrote if @NameSearch would be set to: "*o''clock*" , but I guess you mean @SearchTerm

你写的如果@NameSearch被设置为:“* o''clock *”,但我想你的意思是@SearchTerm

2. What is the result of

2.结果是什么?

SELECT * FROM sys.dm_fts_parser ('"*o''clock*" ', 1033, 0, 0)

One, two or three rows? May be you have problems with wordbreakers. Setup your language first, possibly it is not English (1033).

一排,两排或三排?可能是你有关wordbreakers的问题。首先设置您的语言,可能不是英语(1033)。

3. If I would need to run it dynamically, then I would double apostrophes once more:

3.如果我需要动态运行它,那么我会再次加倍撇号:

DECLARE @sql nvarchar(max)= 'SELECT * FROM sys.dm_fts_parser (''"*o''''clock*" '', 1033, 0, 0)'
exec(@sql)

That is ok, but since you are going to automatically double apostrophes, then you could put extra apostrophes just by error.. Possibly you should dig this direction or present us clear code snippet.

没关系,但是因为你要自动加倍撇号,那么你可能只是错误地放入额外的撇号。可能你应该挖掘这个方向或者给我们提供清晰的代码片段。

#1