動態 sql 的核心在于平衡靈活性與安全性，避免 sql 注入的主要方法包括：1. 使用參數化查詢或預編譯語句，將 sql 結構與數據分離；2. 對輸入進行驗證和清理，優先采用白名單驗證；3. 遵循最小權限原則，限制數據庫用戶的權限；4. 使用 orm、模板引擎或鏈式構建器提升可讀性和可維護性；5. 避免直接字符串拼接以防止惡意代碼注入；不同場景如報表查詢、數據導入/導出、權限控制等需結合參數化查詢與相應工具策略來確保安全并優化開發效率。
動態 SQL 指的是在運行時根據條件或用戶輸入來構建 SQL 查詢語句。這既能提高靈活性，也可能引入安全風險，尤其是 SQL 注入。核心在于如何在靈活構建語句的同時，保障安全性。
動態 SQL 的編寫，核心在于平衡靈活性與安全性。
如何避免 SQL 注入？
SQL 注入是動態 SQL 中最需要警惕的風險。它指的是攻擊者通過在輸入中插入惡意的 SQL 代碼，從而改變原始查詢的意圖。
參數化查詢（Parameterized Queries）/ 預編譯語句（Prepared Statements）：
這是最有效的防御手段。使用參數化查詢，你可以將 SQL 語句的結構和數據分離開。數據庫會首先編譯 SQL 語句的結構，然后將數據作為參數傳遞進去。這樣，即使數據中包含 SQL 關鍵字，也不會被當做 SQL 代碼執行。
例如，在 Java 中使用 JDBC：
String sql = "SELECT * FROM users WHERE username = ? AND password = ?"; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setString(1, username); // 用戶提供的 username pstmt.setString(2, password); // 用戶提供的 password ResultSet rs = pstmt.executeQuery();
在這個例子中，username 和 password 是用戶提供的，但是它們會被作為參數傳遞給 SQL 語句，而不是直接拼接到 SQL 語句中。
輸入驗證和清理：
雖然參數化查詢是首選方法，但對輸入進行驗證和清理仍然很重要。你可以檢查輸入是否符合預期的格式和類型，并刪除或轉義任何可能有害的字符。
- 白名單驗證： 只允許已知的、安全的字符或模式。
- 黑名單過濾： 移除或轉義已知的危險字符，例如 ‘, ” , ; 等。但黑名單方法可能不夠全面，難以覆蓋所有潛在的攻擊。
最小權限原則：
確保你的數據庫用戶只擁有執行其任務所需的最小權限。如果一個用戶只需要讀取數據，那么就不應該賦予它寫入或刪除數據的權限。
const mysql = require('mysql'); const pool = mysql.createPool({ host: 'localhost', user: 'your_db_user', password: 'your_db_password', database: 'your_db_name' }); function getUser(username, password) { return new Promise((resolve, reject) => { pool.query('SELECT * FROM users WHERE username = ? AND password = ?', [username, password], (error, results) => { if (error) { return reject(error); } resolve(results); }); }); } // 使用示例 getUser('testuser', 'testpassword') .then(users => console.log(users)) .catch(err => console.error(err));
這個例子使用了 Node.js 的 mysql 模塊，并使用了參數化查詢來避免 SQL 注入。
如何優雅地構建動態 SQL？
除了安全性，動態 SQL 的可讀性和可維護性也很重要。
使用 ORM（對象關系映射）：
ORM 工具（例如 hibernate, mybatis, Sequelize）可以將數據庫表映射到對象，并提供一種更抽象的方式來構建 SQL 查詢。ORM 通常會自動處理參數化，從而減少 SQL 注入的風險。
模板引擎：
一些模板引擎（例如 Velocity, Freemarker）可以用于生成 SQL 語句。模板引擎允許你使用變量和條件語句來動態地構建 SQL 語句。
鏈式構建器（Fluent Builders）：
你可以使用鏈式構建器模式來構建 SQL 語句。這種模式允許你通過一系列的方法調用來逐步構建 SQL 語句。
代碼示例 (Java + MyBatis xml):
假設我們有一個需求：根據用戶名和/或郵箱查詢用戶。
<!-- MyBatis XML 映射文件 --> <select id="findUsersByUsernameAndEmail" parameterType="map" resultType="User"> SELECT * FROM users <where> <if test="username != null and username != ''"> AND username = #{username} </if> <if test="email != null and email != ''"> AND email = #{email} </if> </where> </select>
在這個例子中，MyBatis 的 XML 映射文件使用了
為什么避免字符串拼接？
直接使用字符串拼接來構建 SQL 語句是極其危險的。它不僅容易導致 SQL 注入，而且可讀性和可維護性都很差。例如：
String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"; // 極其危險！
這種方式會將用戶提供的 username 和 password 直接拼接到 SQL 語句中，如果用戶提供了包含惡意 SQL 代碼的輸入，那么這些代碼就會被執行。
動態 SQL 在不同場景下的應用策略？
不同場景下，動態 SQL 的應用策略會有所不同。
報表查詢：
報表查詢通常需要根據用戶的選擇來動態地構建 SQL 查詢。在這種情況下，可以使用參數化查詢和白名單驗證來確保安全性。同時，可以使用 ORM 或模板引擎來提高可讀性和可維護性。
數據導入/導出：
在數據導入/導出場景中，需要根據不同的數據格式來動態地構建 SQL 語句。在這種情況下，可以使用參數化查詢和輸入驗證來確保安全性。同時，可以使用鏈式構建器或模板引擎來提高可讀性和可維護性。
權限控制：
在權限控制場景中，需要根據用戶的權限來動態地構建 SQL 查詢。在這種情況下，可以使用參數化查詢和最小權限原則來確保安全性。同時，可以使用 ORM 或模板引擎來提高可讀性和可維護性。
總而言之，動態 SQL 的編寫需要在靈活性和安全性之間找到平衡。參數化查詢是首選的防御 SQL 注入的方法，而 ORM、模板引擎和鏈式構建器可以幫助你更優雅地構建動態 SQL 語句。同時，根據不同的場景選擇合適的應用策略也很重要。
