Issue With Apostrophe

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开发者_JS百科 filter base on input parameters

if Rtrim(@LastName) <> ''
 select @Where = @Where + ' and a.LastName like '+ 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),'''','''''') + ''

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.