使用準備執行在 MySql 過程中傳遞參數

我正在嘗試編寫一個具有位置參數的過程，該參數稍後在過程內部的查詢中使用。另外，我希望能夠動態限制查詢結果。

我從來沒有做過類似的事情，所以我閱讀了相應的文件並研究了一些例子。我的程式碼如下：

DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$    
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);

SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM 
(SELECT current_Price as price FROM UNION_SALES WHERE location = ', loc,' and current_Price >5000
ORDER BY current_Price ASC LIMIT ?) as prices;');

PREPARE STMT FROM @quer;
EXECUTE STMT USING @a;

END $$
DELIMITER ;   

呼叫該過程時出現的錯誤是：

CALL lower10_avg_price('Αρχαία Αγορά            ');

錯誤程式碼：1064。您的 SQL 語法有錯誤；檢查與您的 MySQL 伺服器版本相對應的手冊，以在第 3 行的“Αγορά and current_Price >5000 ORDER BY current_Price ASC LIMIT”附近使用正確的語法

有人可以幫我了解導致此錯誤的原因嗎？感謝您的任何建議！

修改您的 SP 以查看建構的 SQL 文本：

小提琴

很明顯，您忘記用單引號包裹字元串文字值。你必須使用

SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM ( SELECT current_Price as price 
       FROM UNION_SALES 
       WHERE location = ''', loc,''' 
         and current_Price >5000
       ORDER BY current_Price ASC LIMIT ?) as prices;');

location可用於 SQL 注入。您可以使用綁定參數（首選）或使用QUOTE(location).

您不能將準備好的語句用於 LIMIT 但您可以將其用於位置

DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$    
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);
SET @loc := loc;
SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM 
(SELECT current_Price as price FROM UNION_SALES WHERE location = ? and current_Price >5000
ORDER BY current_Price ASC LIMIT ', @a,') as prices;');

PREPARE STMT FROM @quer;
EXECUTE STMT USING @loc;

END $$
DELIMITER ;  

正確提到您可以使用 QUOTE 將文本封裝在引號中的法案，其他所有方法都容易受到 sql 注入的影響。

DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$    
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);

SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM 
(SELECT current_Price as price FROM UNION_SALES WHERE location = ', QUOTE(loc), ' and current_Price >5000
ORDER BY current_Price ASC LIMIT ?) as prices;');

PREPARE STMT FROM @quer;
EXECUTE STMT USING @a;

END $$
DELIMITER ;  

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