Prepared Statements with functions syntax

Question

I'm getting an error when I'm trying to run this:

mysql> prepare stmt1 from "UPDATE test.users SET password = password('?') WHERE usrid = ?";
mysql> prepare stmt1 from "UPDATE test.users SET password = password(?) WHERE usrid = ?";

How can I make a prepared statement where a function takes my variable as an argument?

Answer 1

You need to add some double quotes around your prepared statement:

mysql> PREPARE stmt_name FROM "SELECT name FROM Country WHERE code = ?";
Query OK, 0 rows affected (0.09 sec)
Statement prepared

mysql> SET @test_parm = "FIN";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_name USING @test_parm;
+---------+
| name    |
+---------+
| Finland |
+---------+
1 row in set (0.03 sec)

mysql> DEALLOCATE PREPARE stmt_name;
Query OK, 0 rows affected (0.00 sec)

Answer 2

Now that the gross syntax error (missing quotes around the prepared string) is fixed, the question resolves to "how to get an argument into a function in a prepared statement", I believe.

The first example passes the password string '?' to the password function - it probably is not what you had in mind. The second example passes the first value given to the statement when it is executed to the password function, and the second value is used to identify the relevant usrid.

This assumes a standard interpretation of SQL - I know of no reason to think it won't apply to MySQL too.