前言

在 T-SQL 字串常值中，單引號 ' 是字串界定符。若字串本身包含單引號 (如 O'Reilly、動態 SQL 參數)，必須正確轉義，否則語法錯誤或引發注入風險。本文說明標準轉義規則、動態 SQL 實例與參數化最佳實踐。

一、標準轉義規則：雙單引號 ''

SQL 標準規定：**在單引號字串內，用兩個連續單引號 '' 表示一個字面單引號**。

-- 正確
SELECT 'O''Reilly' AS Author;          -- 結果: O'Reilly
SELECT 'It''s a ''test'' string';      -- 結果: It's a 'test' string

-- 錯誤：嘗試用反斜線 \' (T-SQL 不支援)
SELECT 'O\'Reilly';  -- 語法錯誤

二、動態 SQL 中的巢狀轉義實例

場景：存儲過程接收條件參數，動態組裝 WHERE name LIKE '...'。

CREATE PROC dbo.SearchRooms
    @RoomTypePattern VARCHAR(50)  -- 例: '標準間%'
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    -- 1. 參數先做單引號轉義：將 ' 替換為 ''
    DECLARE @safePattern NVARCHAR(100) = REPLACE(@RoomTypePattern, '''', '''''');
    -- 2. 組裝動態 SQL，字串常值外層用單引號
    SET @sql = N'SELECT * FROM dbo.Room WHERE RoomType LIKE ''' + @safePattern + N'''';
    EXEC sp_executesql @sql;
END

關鍵點：REPLACE(@param, '''', '''''') —— 這裡有 4 個單引號：前兩個代表「搜尋單引號」，後兩個代表「替換為兩個單引號」。

三、更安全的替代方案：參數化動態 SQL

避免字串拼接，直接用 sp_executesql 參數化：

CREATE PROC dbo.SearchRooms_Safe
    @RoomTypePattern VARCHAR(50)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.Room WHERE RoomType LIKE @pattern';
    EXEC sp_executesql @sql, N'@pattern VARCHAR(50)', @pattern = @RoomTypePattern;
END

• 零轉義負擔，參數自動處理引號。
• 重用執行計畫，防 SQL 注入。
• **生產環境強制要求參數化**，嚴禁字串拼接構建動態 SQL。

四、其他語境的引號處理

語境	轉義方式	範例
字串常值	雙單引號 ''	'Don''t'
識別碼 (表名/欄位名)	方括號 [] 或雙引號 "" (需 SET QUOTED_IDENTIFIER ON)	[Order Details], "Order Details"
動態 SQL 字串拼接	參數化 (首選) / 雙單引號轉義	見上文
OPENQUERY / OPENROWSET	遠端引擎規則，通常同雙單引號	SELECT * FROM OPENQUERY(LinkedSrv, 'SELECT * FROM t WHERE c = ''val''')

結語

單引號巢狀轉義的鐵律是：**字串常值內，單引號加倍**。但現代開發應**盡量避免字串拼接**，改用參數化查詢 (sp_executesql、ORM 參數繫結、Table-Valued Parameters)，從根源消除轉義錯誤與注入風險。