撇号问题

发布于 2024-08-10 07:08:30 字数 793 浏览 3 评论 0原文

我有一个在 Dynamic SQl 中为我的应用程序之一编码的 Proc。它用于通过姓氏搜索申请人。目前，它正在搜索申请人姓氏的前两位数字或全名。但我在搜索姓氏中带有撇号的申请人时遇到问题（例如奥康纳）。如果客户端尝试使用 O' 或 O'Connor 搜索申请人，则会抛出错误。他们想要搜索姓氏中带或不带撇号的每位申请人。请帮助我尝试了一切，但它不起作用。下面是我在 Proc 中使用的搜索代码来拉取申请人：

如果需要，添加通配符

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

现在根据输入参数动态构建过滤器

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

原文

I have a Proc that was coded in Dynamic SQl for one of my Application. It is using to search the Applicants with their last name. Right now it is searching applicants with either their first 2 digits of their last name or full last name. But i have a problem searching Applicants that have Apostrophe in their last name(Example O'Connor). If the client try to search applicant with O' or O'Connor it is throwing an error. They want to search every Applicant with or without Apostrophe in their last name. Please Help I tried everything, but its not working. Below is my search code that using in the Proc to pull applicants:

Add wildcards if necessary

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

Now build dinamically the filter base on input parameters

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灰色世界里的红玫瑰 2024-08-17 07:08:30

在构建 SQL 字符串时，您需要转义输入字符串中的撇号（基本上将单个 ' 替换为两个 ''）。在

选择将用户输入传递到 SQL Server 数据库的任何地方，作为安全性，您都需要注意这一点问题（SQL注入攻击）cf Bobby Tables

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

You need to escape the apostrophe in the input string (basically replace a single ' with two '') as you build your SQL string

You need to pay attention to this anywhere you choose to pass user input to a SQL server database as its a security issue (SQL Injection Attacks) c.f. Bobby Tables

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

回复收藏 0 原文

埋葬我深情 2024-08-17 07:08:30

我的建议是编写查询以包含用于过滤的备用列，并将撇号/单引号替换为任何特殊字符（例如 #）。这允许您完整保留原始列，以防您想要显示它。

要在 SQL Server 中执行此操作，您可以执行以下操作：

Select
    tbl.strName_Last,
    REPLACE(tblOrder.strName_Last, '''','#')) as StrLastNameForFilter
  ....

然后更改代码以根据此备用列进行筛选，并在用户提供的筛选字符串中，将撇号/单引号替换为特殊字符。

My suggestion is to write the query to contain an alternate column to be used for filtering with the apostrophe/single quote replaced by any special character such as a #. This allows you to leave the original column intact in case you want to display it.

To do that in SQL Server, you could do something like this:

Select
    tbl.strName_Last,
    REPLACE(tblOrder.strName_Last, '''','#')) as StrLastNameForFilter
  ....

Then change your code to filter based on this alternate column, and in the user-provided filter string, replace the apostrophe/single quote with the special character.

回复收藏 0 原文

~没有更多了~