I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically.
I have never done anything similar, so I read the appropriate documentation and studied some examples. My code is the following:
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 ;
The error I get when I call the procedure is:
CALL lower10_avg_price('Αρχαία Αγορά ');
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Αγορά and current_Price >5000 ORDER BY current_Price ASC LIMIT ' at line 3
Can someone please help me understand what is causing this error?
Thank you for any suggestions!
Best Answer
Modify your SP for to look at the SQL text built:
fiddle
It is clear that you forget to wrap string literal value with single quotes. You must use
location
can be used for SQL injection. You could fix it either by using a bound parameter (preferred), or by usingQUOTE(location)
.