0

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!

0
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).

0
0

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 ;  
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.