ADO.NET 查詢語法陷阱全解析：通配符、跳脫字元、日期與識別碼分隔符

前言

ADO.NET (特別是 OleDb/Jet/ACE Provider) 在建構動態 SQL 時，有別於 T-SQL 或標準 ANSI SQL 的特殊語法規則。本文系統整理：通配符用法、單引號跳脫、日期常值格式、識別碼分隔符處理，並提供防注入的參數化查詢最佳實務。

一、通配符：% 與 * 的雙重標準

坑點：同一應用若支援多資料庫，**不可硬編碼通配符**，應在 DAL 層依 Provider 替換。

二、單引號跳脫：雙單引號 ''

// 錯誤：直接拼接
string sql = $"SELECT * FROM Users WHERE Name = '{userInput}'";
// 若 userInput = "O'Brien" → SQL 語法錯誤

// 正確：字串常值中單引號寫兩個
string safe = userInput.Replace("'", "''");
string sql = $"SELECT * FROM Users WHERE Name = '{safe}'";
// O''Brien → 正確

// 更好：參數化查詢 (完全避免跳脫問題)
using var cmd = new OleDbCommand("SELECT * FROM Users WHERE Name = @Name", conn);
cmd.Parameters.AddWithValue("@Name", userInput);

三、日期常值：#yyyy/MM/dd# (Jet/ACE 專用)

// Access / Excel 日期常值必須用 # 包圍，格式建議 ISO (yyyy/MM/dd)
string sql = "SELECT * FROM Orders WHERE OrderDate > #2024/12/25#";

// 參數化查詢自動處理格式
cmd.Parameters.Add("@Date", OleDbType.Date).Value = DateTime.Today;

• Jet/ACE **不支援** '2024-12-25' 或 '2024/12/25' (單引號)
• SQL Server 使用 '2024-12-25' (單引號) 或參數化
• 跨資料庫專案：統一用參數化，不要拼接日期字串

四、識別碼分隔符：方括號 [] 與跳脫

當資料表/欄位名稱包含空格、保留字、特殊字元時：

// 包含空格
SELECT [Order ID], [Customer Name] FROM [Order Details]

// 包含 ] 字元本身：在 ] 前加 \
SELECT [Sale\] Order] FROM Products  -- 欄位名實為 "Sale] Order"

// C# 字串中 \ 需再跳脫
string col = "[Sale\\] Order]"; // 實際 SQL: [Sale\] Order]

對照：SQL Server 也支援 []，標準 SQL 使用雙引號 "" (需 SET QUOTED_IDENTIFIER ON)，MySQL 使用反引號 ``。

五、參數化查詢：唯一正解

// OleDb 參數順序必須與 SQL 中 ? 出現順序一致 (不支援命名參數)
string sql = "INSERT INTO Logs (Level, Message, CreatedAt) VALUES (?, ?, ?)";
using var cmd = new OleDbCommand(sql, conn);
cmd.Parameters.Add("@Level", OleDbType.VarWChar).Value = level;
cmd.Parameters.Add("@Msg", OleDbType.LongVarWChar).Value = message;
cmd.Parameters.Add("@Time", OleDbType.DBTimeStamp).Value = DateTime.Now;
cmd.ExecuteNonQuery();

// SqlClient 支援命名參數，順序不拘
string sql = "INSERT INTO Logs (Level, Message, CreatedAt) VALUES (@Level, @Msg, @Time)";
using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Level", level); // 隱含類型推斷
// 建議明確指定類型避免推斷錯誤
cmd.Parameters.Add("@Msg", SqlDbType.NVarChar, -1).Value = message;
cmd.Parameters.Add("@Time", SqlDbType.DateTime2).Value = DateTime.Now;

六、OleDbType 對照表 (常用)

七、動態 SQL 建構器模式 (若必須拼接)

public static class SqlBuilder
{
    public static string EscapeIdentifier(string ident, char quote = '[') 
        => quote + ident.Replace("]", "]\]") + (quote == '[' ? "]" : quote.ToString());

    public static string EscapeLiteral(string literal) 
        => literal.Replace("'", "''");

    public static string FormatDate(DateTime dt, string provider)
        => provider switch
        {
            "OleDb" => $"#{dt:yyyy/MM/dd}#",
            "SqlClient" => $"'{dt:yyyy-MM-dd}'",
            _ => $"'{dt:yyyy-MM-dd}'" // ANSI 標準
        };
}

八、總結清單

• [ ] **永遠優先使用參數化查詢** — 防注入、自動處理類型/格式/跳脫
• [ ] 若必須拼接：字串用 Replace("'", "''")、日期用 Provider 專用格式、識別碼用 [] 並跳脫 ]
• [ ] 通配符依 Provider 切換：SqlClient 用 %，OleDb 用 *
• [ ] 明確指定參數類型 (OleDbType/SqlDbType)，避免隱含推斷導致計畫快取汙染
• [ ] 連線字串、命令逾時、交易隔離層級集中管理

結語

ADO.NET 的語法陷阱多源於不同 Provider 實作差異。現代開發建議：

1. 使用 Dapper / Entity Framework Core 等 Micro-ORM/ORM，完全隔離 SQL 方言差異。
2. 若維護舊系統，建立 IDbCommandBuilder 抽象層，封裝 Provider 差異。
3. 程式碼審查時，**任何字串拼接 SQL 皆視為缺陷**，除非有極強理由並經資安審核。