APOSTROPHE DYNAMIC SQL

Question

DECLARE @SQL Varchar(Max)
DECLARE @DESCR Varchar(Max)

-- Customer enters description into @Descr
SET @SQL = 'Update TableName SET FieldName='''
+ @DESCR
+ ''' WHERE ID=123'

The problem is when the customer enters an apostrophe into the @Descr variable.

Q: In Microsoft SQL Server 2005, how do I replace all apostrophies with double apostrophe?

Answer 1

If this even needs to be dynamic SQL at all (the code you have shown doesn't) then use parameterised SQL and sp_executesql for this to avoid SQL injection possibilities.

DECLARE @SQL NVarchar(Max)
DECLARE @DESCR NVarchar(Max)

-- Customer enters description into @Descr


SET @SQL = 'Update TableName SET FieldName=@DESCR WHERE ID=123'

exec sp_executesql @SQL, N'@DESCR NVarchar(Max)', @DESCR =@DESCR

Answer 2

Not recommended for production, but will work.

SET @DESCR = REPLACE(@DESCR, '''', '''''')