Search code examples
phpmysqlstored-proceduresprepared-statement

How to use stored procedure parameters in the execute statement?

I have a simple stored procedure:

DELIMITER $$
CREATE PROCEDURE `findUserByName`(IN `name` VARCHAR(36))
BEGIN
  set @name = name;
  PREPARE findStmt FROM 'SELECT * FROM user WHERE name=?';
  EXECUTE findStmt USING @name;
END$$

I call it in PHP with the prepare method:

$stmt = $mysqli->prepare('CALL findUserByName(?)');
$stmt->execute([$inputName]);

I have 2 questions about the above code:

  1. This assignment statement set @name = name; appears somewhat redundant, can I directly use the parameter name? MySQL doesn't allow me to use USING name, I have also tried changing the parameter name to @name, but it will not be recognized.

  2. Since I have used the prepare method in PHP, is it safe to concatenate the parameter with SQL?

    set @sql = CONCAT('SELECT * FROM user WHERE name=''', name, '''');
PREPARE findStmt FROM @sql;
EXECUTE findStmt;
Solution
    1. No, you can't use local variables as arguments to EXECUTE. I agree this is weird and unsatisfying, because you must set a user-defined variable (the type with the @ sigil) that seems redundant.

    https://dev.mysql.com/doc/refman/8.0/en/execute.html says:

    Parameter values can be supplied only by user variables

    1. No, it's not safe to concatenate strings into your dynamic SQL, if the string may contain untrusted content. This is called SQL injection, and it's a common source of errors or data breaches.

      What happens in your example above, if someone enters their name as "O'Reilly"?

      You may see advice to "use prepared statements" to protect your code from SQL injection, but merely using PREPARE is not magic. It does not bless unsafe SQL to make it safe. The advice should be "use parameters." It is necessary to use PREPARE to use parameters, but it's the parameters, not the PREPARE, that is the important part to make it safe.

    Re your comment:

    ... if it's possible to directly use parameters or variables in queries, in what situations do I have to use prepare statements in stored procedures?

    If your query is fixed at the time you create the procedure, and the variables you combine with it are only substituting for scalar values, then you don't need to use PREPARE/EXECUTE.

    If your query's syntax is in part based on logic of the procedure, then you must use dynamic SQL. That is, not only do you need to combine scalar values with the query in the form of variables, but you need to modify the query itself, for example to modify expressions or keywords — anything besides simple scalar values.

    You may also be interested in my answer here: SQL Injection in Stored Procedures via MySQL Connector I wrote that to explain further the nature of PREPARE/EXECUTE with respect to when the query is parsed. This is important to know in order to understand how SQL injection is a risk.