Using concat inside a stored procedure parameters

Question

I have a stored procedure and function that accepts a varchar as a parameter and i have this code where i am using concat function in the parameters of such function and stored procedure.

DELIMITER //
CREATE PROCEDURE ja_logan_proc()
BEGIN
SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1;
if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then
call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));
elseif((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'false')then
select 'nothing to do here';
end if;
END //
DELIMITER ;

This line is identified as the one with an error

call register_lookup_r4(CONCAT(''','@the_id','''),CONCAT(''','@the_number','''));

and this other line

if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then

What am i doing wrong in the parameters field?.

• James Anderson: What was the error?
• user46420: Says i have an error on line 6. This is line 6 if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then
• James Anderson: No error number or explanation?
• user46420: Hi this is the entire error #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 '@the_number','''))) = 'true')then call register_lookup_r4(CONCAT(''','@the_id' at line 6 .

Answer 1

The correct solution is not to try concatenating quotes around the argument. That is a very naive approach, and leads to SQL injection vulnerabilities.

The QUOTE() function solves this, when it's needed.

However, you do not need these things to be quoted at all in the queries you're running. Worse, adding literal quotes to these values is likely to get you wrong answers.

Additionally, you are doing unnecessary work by wrapping stored functions inside scalar subqueries.

IF 2 = (SELECT 2) is obviously a verbose way of saying IF 2 = 2 but in your examples, you're doing the same thing... IF(SELECT stored_function_call()) ... is an equivalent but less lightweight version of IF stored_function_call() ...

Corrected and simplified examples:

IF search_number_in_r4(@the_number) = 'true' THEN 
...
CALL register_lookup_r4(@the_id,@the_number);
...
ELSEIF search_number_in_r4(@the_number) THEN ...

Answer 2

This finally worked

DELIMITER //
CREATE PROCEDURE ja_logan_proc()
BEGIN
SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1;
if((select search_number_in_r4(CONCAT('`',@the_number,'`'))) = 'true')then
call register_lookup_r4(CONCAT('`',@the_id,'`'),CONCAT('`',@the_number,'`'));
elseif((select search_number_in_r4(CONCAT('`',@the_number,'`'))) = 'false')then
select 'nothing to do here';
end if;
END //
DELIMITER ;