Passing parameters in MySql procedure with prepare execute

Question

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!

Answer 1

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

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 can be used for SQL injection. You could fix it either by using a bound parameter (preferred), or by using QUOTE(location).

Answer 2

You can't use prepared statements for LIMIT but you canuse it for location

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 ;  

A bill correctly mentioned you can use QUOTE to capsule the text in quotes, every other method is vulnerable to sql injection.

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 ;