Add apostrophe to last name search

Question

I created a proc that will return a list of applicants by name. I have a problem finding applicants with a last name with an apostrophe (O'Connor example). Could you help find these applicants:

Below is my search code:

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)

Answer 1

apostrophes are escaped in T-SQL strings using double apostrophes, for example.

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

, SQL , , - SQL . , O'Connor, "", "O'; TRUNCATE TABLE Customers; --", .

, SQL , (, REPLACE (@LastName, '''', '''''')), SQL.

, , SQL- SQL . , API SQL / "" . SQL-. .

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 .

Answer 3

Sort of:

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

(yes, I know, this is a lot of quotation marks, but it works.)

and you need more quotes in the "x" syntax:

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

(yes, more and more quotes)

this will lead to the correct one:

and a.LastName 'like o''conor'