Add apostrophe to last name search

问题:

I created a proc that will return a list of applicants by lastname. I have a problem searching Applicants with last name that has apostrophe (Example O'Connor). Could you please help finding those 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)

回答1:

Your code looks like you try to build a dynamic SQL WHERE clause. Stop it right there and throw it away, your approach is dangerous and error-prone.

You might want to do something along the lines of this instead:

/* 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

Be sure to read the MSDN on sp_executesql for more explanation & samples.

回答2:

apostrophes are escaped within T-SQL strings using double apostrophes, e.g.

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

Note that the combination of assembling dynamic SQL statements from strings which may contain apostrophes is very dangerous because of the risk of SQL injection attacks. A normal user might have a name like O'Connor, but a savvy attacker might choose a "name" like "O'; TRUNCATE TABLE Customers; --" which could erase data.

At a minimum, if you're dynamically assembling SQL statements from strings, you should be replacing apostrophes with double-apostrophes (e.g. REPLACE (@LastName, '''', '''''')) before injecting that string into SQL.

But, if those strings are coming from users, you should really consider using parameterized queries instead of manually assembling your SQL queries by string concatenation with SQL and parameter strings. Parameterization means that the SQL client API and/or server takes care of turning parameters into "safe" strings. This is the best defense against SQL injection attacks. Take a look at this Jeff Atwood blog post for more details about this.

回答3:

Something like:

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

(yes, I know, those are lots of quotes, but it works.)

and you need more quotes in the like ' x ' syntax:

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

(yes, more and more quotes)

this will generate the correct:

and a.LastName 'like o''conor'