Issue With Apostrophe

Question

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)

Answer 1

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),'''','''''') + ''