使用可變數量的要綁定的參數執行準備好的語句

我使用的一些儲存過程需要根據是否提供了過程輸入參數來插入 WHERE 標準。為了避免潛在的注入點，我想對將成為插值標準一部分的值使用參數綁定。

由於添加到準備好的語句的標準以及要綁定的參數數量可能會因使用者輸入而異，因此我設計了以下方法來確定將哪些變數傳遞給 EXECUTE 語句。這行得通，但似乎不優雅。

CREATE PROCEDURE foo (IN mandatory INT, IN optional INT, IN optional2 VARCHAR(20))
 BEGIN

   SELECT
     0, '', '', mandatory, optional, optional2
   INTO 
     @params, @sql, @where, @m, @o1, @o2;

   IF (@o1 > '' AND @o1 IS NOT NULL) THEN
     SET @where = CONCAT(@where, ' AND field = ?');
     SET @params = @params + 1;
   END IF;
   IF (@o2 > '' AND @o2 IS NOT NULL) THEN
     SET @where = CONCAT(@where, ' AND field2 = ?');
     SET @params = @params + 3;
   END IF;

   SET @sql = CONCAT('
     SELECT id, bar FROM table
     WHERE
       baz = ?
       ', @where
   );
   PREPARE STMT FROM @sql;
   CASE @params
     WHEN 0 THEN EXECUTE STMT USING @m;
     WHEN 1 THEN EXECUTE STMT USING @m, @o1;
     WHEN 3 THEN EXECUTE STMT USING @m, @o2;
     WHEN 4 THEN EXECUTE STMT USING @m, @o1, @o2;
   END CASE;
   DEALLOCATE PREPARE STMT;

 END$$

我知道替代方案：

• 呼叫這些儲存過程的二進製文件有一個函式，該函式通過正則表達式傳遞使用者提供的字元串來嘗試辨識潛在的 SQL 注入。
• 給定動態數量的輸入，使用者定義的函式可用於動態構造 EXECUTE 語句。

但是，我想知道是否有其他人遇到過這種純粹使用 SQL 來處理 EXECUTE 語句的動態構造的願望。

至少在這個例子中，有比這更簡單的方法。請記住，優化器總是試圖以這樣一種方式來計劃查詢執行，即涉及檢索有效結果集所需工作量最少的路徑將是選擇的路徑。

SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1);

伺服器將始終返回所有行，因為 1 = 1 包含得出結論“是的，此行匹配WHERE子句”所需的所有真實性……並且它不會費心掃描 col1 中的值以查看它們是否包含 ‘foo ’ 因為優化器找到了一種更明顯、更簡單、“成本更低”的方法……(any expression) OR (TRUE)總是正確的……而通往真理的最短路徑是優化器應該找到並採用的路徑。它不需要1 = 1逐行評估，因為這是一個常量表達式。

您甚至不需要準備好的語句，只需要過程中的查詢：

SELECT id, bar 
 FROM table
WHERE baz = mandatory
  AND (optional IS NULL OR field = optional)
  AND (optional2 IS NULL OR field2 = optional2);

如果程序變數“可選”為空，優化器將意識到整個表達式（在該行的括號中）對於每一行都將為真，無論如何，因為在執行查詢的上下文中，“可選”是一個常量價值，並且optional is NULL永遠是真的，就像1 = 1永遠是真的一樣。優化器意識到這也是一個常量表達式。“欄位”的內容永遠不需要計算，因為OR表達式已預先確定為真。optional2 也是如此。

另一方面，如果“可選”不為空，則被optional IS NULL優化掉，因為該表達式不可能為真，而您只剩下AND (field = optional). 同樣，對於“可選2”。

在每種情況下，優化器都留下了仍然可以適當使用索引的查詢。

由於查詢在過程體中，而不是在準備好的語句中，SQL 注入是不可能的，因為伺服器沒有機會模糊變數中的數據和文字查詢之間的區別。

引用自：https://dba.stackexchange.com/questions/57661