Question 293667 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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;
Tags:

2

Answers


  1. 0
    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.

    Login or Signup to reply.
  2. 0

    Select a distinct name for the procedure parameter, avoiding duplication with the column name. You can then use the parameter directly in the query:

    DELIMITER $$
CREATE PROCEDURE findUserByName(in_name VARCHAR(36))
BEGIN
  SELECT * FROM user WHERE name=in_name;
END
$$
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search