Escape Character in SQL Server

Escape Character in SQL Server

To escape ' you simly need to put another before: ''

As the second answer shows it's possible to escape single quote like this:

select 'it''s escaped'

result will be

it's escaped

If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).

e.g. instead of doing

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)

try this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'

 

https://www.freeformatter.com/

SQL Escape / Unescape

Escapes or unescapes a SQL string removing traces of offending characters that could prevent execution.

The following rules are applied:

• Escapes all single quote characters by doubling them. Ex: select * from table where value = 'a single quote '' is offensive';

 

=cmd|' /C calc'!A0@asdf.com

转义之后得到

=cmd|'' /C calc''!A0@asdf.com

 

作者:Chuck Lu    GitHub