Possible SQL injection dropping a table with stored procedure?

Question

Given this MySQL stored procedure:

CREATE PROCEDURE customer.`getCustomers5`(
sdf varchar(1000)
)
BEGIN

set @se  = concat('select * from customer.customertbl where id=', sdf);


PREPARE stm1 from @se;

EXECUTE  stm1;

END;

Is it possible to do SQL injection into this store procedure even if the front end that called this stored procedure uses PDO parameter/data binding?

I need to build a query dynamically (dynamic where clause) before calling it.

if it's possible to do SQL injection, is there any method to counter this problem?

stackoverflow.com/questions/60174/…. in short with PDOs you should be safe. aaand php.net/manual/en/pdo.prepared-statements.php — Sergey Benner
-1 for asking a vague question that turns out "no, I didnt mean that, the code is totally different". — Your Common Sense
In your example code, if you do this instead CREATE PROCEDURE customer.getCustomers5(sdf varchar(1000)) BEGIN select * from customer.customertbl where id=sdf; END; No SQL Injection would be possible. But yes, dynamic queries need to be parameterized, else SP alone wouldn't provide any protection from sql injection. — Allen King

Your Common Sense Your Common Sense · Accepted Answer · 2012-02-11T12:01:30

You are just using prepared statements wrong.
You have to bind parameters, not concatenate them.

DELIMITER // 
CREATE PROCEDURE customer.`getCustomers5`(sdf varchar(1000)) 
BEGIN 
  PREPARE stm1 from 'select * from customer.customertbl where id=?'; 
  SET @a = sdf;
  EXECUTE stm1 using @a; 
END//
DELIMITER ;

Possible SQL injection dropping a table with stored procedure?

2 Answers