Are following MySql stored procedure vulnerable to sql injection?

Question

Read almost all the post on this but could not judge whether my procedures are vulnarable or not? Any help is appreciated.

1)calling Procedure as:

$query = ($is_mine?'call dispatch.dis_get_my_assigned_tasks("'.$username.'");'

Procedure :

CREATE DEFINER=`test`@`localhost` PROCEDURE `dis_get_all_assigned_tasks`()
BEGIN    
    select distinct at_id, at_issues, at_location, at_room_number, user_fname, 
        from dispatch.dis_assigned_tasks 
        left outer join dispatch.dis_users 
        on user_id  = at_user
    order by at_location, at_user_pickup_timestamp  desc; 
END

2) calling Procedure as :

$query = "call dispatch.dis_get_user_info('".$username."');";

Procedure :

CREATE DEFINER=`test`@`localhost` PROCEDURE `dis_get_user_info`(IN username VARCHAR(45))
BEGIN    
    select * from dispatch.dis_users where user_username = username;
END

This is no security review service. If there is a specific point in those queries you worry about, let us know which one and what your thoughts are. — hakre, Aug 28 '13 at 19:18
possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — hakre, Aug 28 '13 at 19:18
@hakre : Thanks a lot for reply. I just wanted to confirm calling procedure this way is vulnerably or not? — S52, Aug 28 '13 at 20:06
@RaymondNijland : Thanks, will use prepare statement to call procedure.Hopefully that will solve my problem. — S52, Aug 28 '13 at 20:07
that point exactly is missing in your question: you do not yet call them with a stored procedure. which makes me wonder a little because this is normally the first suggestion to prevent SQL injection and you wrote you read about the topic. Perhaps review with the OWASP link I've added in my answer. — hakre, Aug 28 '13 at 20:08
@sach see my comment by hakre answer, OWASP is not always right and every hole is an risk.... — Raymond Nijland, Aug 28 '13 at 20:10

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

The procedures are not vulnerable to SQL injection, but your queries are.

To prevent (and learn about) SQL Injection, please see our reference question:

How can I prevent SQL injection in PHP?

For "almost all the posts on this" you say you've read it looks like that they didn't cover the topic well. I suggest you to select better material to get education from.

SQL Injection Prevention Cheat Sheet (You need to be able to read code written in different languages)

edited May 23 '17 at 12:31

Community

1
1

answered Aug 28 '13 at 19:31

hakre

193,403
52
435
836

Still the procedure is vulnerable to an SQL injection, assume you are using a procedure as an API layer so that customers can call the procedure. There should be un SQL injection check within the procedure it self. – Raymond Nijland Aug 28 '13 at 20:08
@RaymondNijland: The parameter of the procedure are typed (in fact there is one parameter, the first procedure has not parameter), so there is no injection. At least by the book the procedure is not vulnerable to an SQL injection (No unsafe dynamic SQL generation code inside the procedure as far as I can see). – hakre Aug 28 '13 at 20:11
don't trust books and always try it your self. There was an mysql bug with id 68903‎ that would allow SQL injections (that bug is removed by oracle...) on an procedure (https://www.google.nl/search?q=mysql+bug+68903) mysql could still be vulnerable to this dont know i need to analyse the source code off mysql to find out. – Raymond Nijland Aug 28 '13 at 20:21
there were similar bugs in the past, too. however, not to VARCHAR. Perhaps you know more, mysql bug #68903‎ is still hidden, so you are probably right and this is a deep hole (OWASP does not suggest in the book you should use stored procedures, in fact it's written to choose tools based on criteria, e.g. while looking for criterias, Mysql is most likely not a good tool for stored procedure and you would opt to prepared statement anyway which then are most likely broken on PHP's end and as well probably weak in Mysql again so you just use a different RDBMS altogether). – hakre Aug 28 '13 at 20:28

Are following MySql stored procedure vulnerable to sql injection?

1 Answers1