在姓氏搜索中添加撇号

Question

我创建了一个过程，它将返回按姓氏排序的申请人列表。我在查找具有撇号姓氏的申请人时遇到了问题（例如O'Connor）。您能否帮助查找这些申请人：

以下是我的搜索代码：

if Rtrim(@FirstName) <> ''
begin 
  If(Len(@FirstName) < 30) and (CharIndex('%', @FirstName) = 0) and @FirstName != ''
         Set @FirstName = char(39) + @FirstName + '%' + char(39)
end 

if Rtrim(@LastName) <> ''
begin 
   If(Len(@LastName) < 60) and (CharIndex('%', @LastName) = 0) and @LastName != ''
     Set @LastName = Char(39) + @LastName + '%' + char(39)
end 

#At the end  - --Now build dinamically the filter base on input parameters
if Rtrim(@FirstName) <> ''
    select @Where = @Where + ' and a.FirstName like '+ Rtrim(@FirstName) 

if Rtrim(@LastName) <> ''
  select @Where = @Where + ' and a.LastName like '+ Rtrim(@LastName)

- user203671

---

你到底想要做什么？你似乎正在构建一个动态SQL字符串，但是为什么呢？此外，有更好、更安全的方法来处理动态SQL。 - Tomalak

Answer 1

撇号在 T-SQL 字符串中使用双撇号进行转义，例如：

SELECT * FROM sometable where LastName LIKE '%''%'

请注意，从可能包含撇号的字符串组装动态SQL语句的组合非常危险，因为存在 SQL注入攻击的风险。普通用户的姓名可能是O'Connor，但熟练的攻击者可能会选择像"O'; TRUNCATE TABLE Customers; --"这样的“姓名”，这可能会删除数据。

最起码，如果您正在从字符串动态组装SQL语句，则应该在将该字符串注入SQL之前，用双撇号替换撇号（例如，REPLACE（@LastName，'''，''''''））。

但是，如果这些字符串来自用户，则应该真正考虑使用参数化查询，而不是通过字符串串联与SQL和参数字符串手动组装SQL查询。 参数化意味着SQL客户端API和/或服务器负责将参数转换为“安全”字符串。这是防范SQL注入攻击的最佳方法。请参阅Jeff Atwood博客文章以获取更多详细信息。

- Justin Grant

Answer 2

你的代码似乎试图构建一个动态的SQL WHERE 子句。请立即停止并丢弃它，你的方法是危险和容易出错的。

你可能想要尝试以下方式：

/* declare a few test variables */
DECLARE @FirstName varchar(30)
DECLARE @LastName  varchar(60)
SET @FirstName = 'First''Name'
SET @LastName = 'Last''Name'

/* these variables are for dynamic SQL execution */
DECLARE @IntVariable int
DECLARE @SQLString nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)

/* define a paramertized SQL query */
SET @SQLString =
 N'SELECT 
     UserId 
   FROM 
     UserTable
   WHERE 
     LastName LIKE ''%'' + @ln + ''%'' 
     AND FirstName LIKE ''%'' + @fn + ''%''
  '

/* define the used parameters and their types */    
SET @ParmDefinition = N'@ln varchar(30), @fn varchar(60)'

/* execute dynamic SQL, syntax- and code-injection safely */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ln = @LastName, @fn = @FirstName

请务必阅读MSDN关于sp_executesql的更多解释和示例。

- Tomalak

Answer 3

类似于：

...
select @Where = @Where + ' and a.LastName like ' + Replace(Rtrim(@LastName), '''', '''''')
...

是的，我知道，这些是很多引号，但它可以工作。

而且你需要在像 ' x '语法中添加更多引号：

select @Where = @Where + ' and a.LastName like ''' + Replace(Rtrim(@LastName), '''', '''''') + '''' 

（是的，越来越多的引号）

这将生成正确的：

and a.LastName 'like o''conor'

- j.a.estevan

---

我尝试了你给我的内容...但是在执行过程中出现了一个错误。 -- select @Where = @Where + ' and a.LastName like '+ Rtrim(@LastName) select @Where = @Where + ' and a.LastName like ' + Replace(Rtrim(@LastName), '''', '''''')服务器：Msg 170，级别15，状态1，第1行 第1行：'O'附近有语法错误。 - user203671