0

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?.

  • What was the error? – James Anderson Oct 23 '14 at 10:34
  • Says i have an error on line 6. This is line 6 if((select search_number_in_r4(CONCAT(''','@the_number','''))) = 'true')then – user46420 Oct 23 '14 at 10:35
  • 1
    No error number or explanation? – James Anderson Oct 23 '14 at 10:41
  • 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 . – user46420 Oct 23 '14 at 10:42
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 ...
0

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 ;

Your Answer

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