SQL Server 中的转义字符 [英] Escape Character in SQL Server

问题描述

我想使用带转义字符的引号.我该怎么办?

I want to use quotation with escape character. How can I do?

我在 SQL Server 中收到错误

I have received error in SQL Server

字符串后的非右引号.

我正在 varchar 变量中编写 SQL 查询，但我收到了该错误:

I'm writing SQL query in a varchar variable but I have received that error:

字符串后的非右引号.

我想使用引号作为转义字符.

I want to use quotation mark as an escape char.

推荐答案

要转义 ' 你只需要在前面加上一个:''

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'

结果是

it's escaped

如果您要将 SQL 连接到 VARCHAR 中执行(即动态 SQL)，那么我建议您对 SQL 进行参数化.这样做的好处是有助于防止 SQL 注入，另外意味着您不必担心像这样转义引号(您可以通过加倍引号来实现).

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).

例如而不是做

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

试试这个:

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

这篇关于SQL Server 中的转义字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！