Bug #69083 Syntax error in preparing statement for SET PASSWORD
Submitted: 26 Apr 2013 18:10
Reporter: Todd Farmer (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.6.11 OS:Any
Assigned to: CPU Architecture:Any

[26 Apr 2013 18:10] Todd Farmer
Description:
The result of trying to prepare a statement using "SET PASSWORD = PASSWORD(?)" results in a syntax error:

mysql> PREPARE stmt FROM 'SET PASSWORD = PASSWORD(?)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1

This behavior is specific to SET PASSWORD = PASSWORD(), as using SET @var = PASSWORD(?) is handled fine:

mysql> PREPARE stmt FROM 'SET @test = PASSWORD(?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

So too is preparing the statement without a placeholder:

mysql> PREPARE stmt FROM 'SET PASSWORD = PASSWORD(\'test\')';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

This makes it impossible to effectively use prepared statements to issue SET PASSWORD.  If this is meant to be an explicit restriction on the ability to do so, it would be better to issue error 1295 (ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet) than a syntax error.

How to repeat:
PREPARE stmt FROM 'SET PASSWORD = PASSWORD(?)';

Suggested fix:
Either allow the statement to be prepared, or throw error 1295 instead of 1064.