CODEWITHSUNDEEP
mysql
ToolmakerSteve
ToolmakerSteve

Reputation: 21321

Correctly quoting stored procedure parameter for value in where clause

Consider the following stored procedure and its usage:

DROP PROCEDURE IF EXISTS ShowMIHoles;
DELIMITER $$
CREATE PROCEDURE ShowMIHoles(IN CourseID VARCHAR(255))
BEGIN
    select * from tblcourses where id=CourseID;
END $$
DELIMITER ;


call ShowMIHoles(1299)

That works, and returns the row of table tblcourses with id 1299.
However, it isn't protected from SQL injection.
So, I read that quote() should be used to make a value safe.

This is my attempt to use quote:

DROP PROCEDURE IF EXISTS ShowMIHoles;
DELIMITER $$
CREATE PROCEDURE ShowMIHoles(IN CourseID VARCHAR(255))
BEGIN
    select * from tblcourses where id=quote(CourseID);
END $$
DELIMITER ;

call ShowMIHoles(1299)

That results in "0 rows returned". No error message. MySQL 5.7.28.

I tried various tests to see what was going wrong. The ones that don't use CourseID parameter, I tested both inside procedure, and as a stand-alone query.

select quote(1299);

=> '1299'

select * from tblcourses where id='1299';

=> The expected row with id 1299.

select * from tblcourses where id=quote(1299);

=> 0 rows returned.

It is possible to make this work, via prepared statement:

...
BEGIN
    SET @sql = CONCAT('select * from tblcourses where id=', quote(CourseID));
    prepare stmt from @sql;
    execute stmt;
END $$
...

=> The expected row with id 1299.

Question:

Is there any way to safely use this parameter as an expression value in the where clause, without dynamically preparing a statement?

Upvotes: 0

Views: 115

Answers (1)

slaakso
slaakso

Reputation: 9080

You do not need to worry about SQL injection inside a stored procedure unless you are using dynamic SQL. Strings will always be treated like whole string and numbers as numbers.

So, the first version you are showing is perfectly fine. Just make sure that when you call the procedure, your code is safe.

Upvotes: 1

Related Questions