在 SQL Server 中使用字符串转义

在 SQL Server 中，处理字符串是常见且重要的操作之一。字符串转义技术在处理特殊字符、避免SQL注入攻击、构建动态SQL语句等方面都至关重要。本文将详细介绍如何在 SQL Server 中进行字符串转义，涵盖不同场景和方法。

1. 字符串中的单引号转义

SQL Server 使用单引号 (') 来包围字符串值。如果字符串本身包含单引号，就需要对其进行转义，否则会导致语法错误。

示例

假设我们有一个字符串 "O'Reilly"，需要将其插入到数据库中：

INSERT INTO Authors (Name) VALUES ('O''Reilly');

在上例中，我们将字符串中的单引号用两个单引号 ('') 表示，这是 SQL Server 的转义方式。

2. LIKE 子句中的特殊字符转义

在 LIKE 子句中，百分号 (%) 和下划线 (_) 是通配符。要在 LIKE 子句中搜索这些字符，需要使用转义字符。

示例

假设我们要搜索包含百分号的字符串：

SELECT * FROM Products WHERE ProductName LIKE '%25%' ESCAPE '\';

在上例中，ESCAPE '\' 指定反斜杠 (\) 作为转义字符，因此 %25% 中的 % 被视为普通字符。

3. 动态 SQL 及防止 SQL 注入

构建动态 SQL 语句时，需要特别小心转义，以防止 SQL 注入攻击。使用参数化查询是最佳实践，但有时仍需要手动处理字符串转义。

示例

动态 SQL 查询：

DECLARE @ProductName NVARCHAR(50) = 'O''Reilly';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM Products WHERE ProductName = ''' + @ProductName + '''';
EXEC sp_executesql @SQL;

在上例中，我们手动转义了字符串中的单引号。

使用参数化查询防止 SQL 注入

参数化查询是一种更安全的处理方式：

DECLARE @ProductName NVARCHAR(50) = 'O''Reilly';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM Products WHERE ProductName = @ProductName';
EXEC sp_executesql @SQL, N'@ProductName NVARCHAR(50)', @ProductName;

这样做不仅简化了转义过程，还有效防止了SQL注入攻击。

4. QUOTENAME 函数

QUOTENAME 函数用于对字符串进行转义，使其适合用作数据库对象名称（如表名、列名）。

示例

SELECT QUOTENAME('MyTable[Name]');

结果是 [MyTable[Name]]，将字符串包裹在方括号内，并转义其中的方括号。

5. REPLACE 函数

在需要替换字符串中的某些字符时，REPLACE 函数非常有用。它可以用于手动转义字符串。

示例

假设我们需要替换字符串中的单引号：

DECLARE @Input NVARCHAR(100) = 'O''Reilly';
SET @Input = REPLACE(@Input, '''', '''''');
PRINT @Input;

在上例中，我们将字符串中的每个单引号替换为两个单引号。

6. XML 与 JSON 字符串转义

在处理 XML 或 JSON 格式的字符串时，特殊字符需要进行相应的转义。

XML 示例

在 XML 中，特殊字符如 <, >, & 等需要转义：

DECLARE @XML NVARCHAR(MAX) = '<root><name>O''Reilly & Associates</name></root>';
SET @XML = REPLACE(REPLACE(REPLACE(@XML, '&', '&amp;'), '<', '&lt;'), '>', '&gt;');
PRINT @XML;

结果将是 <root><name>O''Reilly & Associates</name></root>。

JSON 示例

在 JSON 中，常见的转义字符包括双引号、反斜杠等：

DECLARE @JSON NVARCHAR(MAX) = '{"name": "O\'Reilly"}';
SET @JSON = REPLACE(REPLACE(@JSON, '\', '\\'), '"', '\"');
PRINT @JSON;

结果将是 {\"name\": \"O'Reilly\"}。