current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
    
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 –  user3286430 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 . –  user3286430 Oct 23 '14 at 10:42

2 Answers 2

up vote 0 down vote accepted

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 ...
share|improve this answer
up vote 0 down vote

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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