MySQL Bugs: #13510: Setting password local variable changes current password

Bug #13510	Setting password local variable changes current password
Submitted:	27 Sep 2005 1:09	Modified:	13 Oct 2005 2:15
Reporter:	Brian Morin	Email Updates:
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.10a-beta/5.0 BK	OS:	nt/Linux
Assigned to:	Bugs System	Target Version:

Description:
DECLAREing a variable called password and setting it in a stored procedure appears to
change the password of the account it's running under.

Run the given script replacing YourDatabaseName with a database.

How to repeat:
USE YourDatabaseName

DELIMITER //

DROP PROCEDURE IF EXISTS blank_pw //

CREATE PROCEDURE blank_pw() 
NOT DETERMINISTIC 
SQL SECURITY DEFINER
BEGIN
  DECLARE password varchar(65);
  SET password = "";
END //

CALL blank_pw(); //

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30917

Pushed to bk 5.0.15.

When declaring a local variable (or parameter) named 'password' or 'names', and
setting it with SET, e.g. "set password = ''", the new error message:
  ERROR 42000: Variable 'nnn' must be quoted with `...`, or renamed
is returned (where 'nnn' is 'password' or 'names'). This means there is syntax conflict
with special sentences like "set password = password(...)" (for setting a user's
password)
and "set names default" (for setting charset and collation).

This must be resolved by either quoting the variable name: set `password` = ...,
which will set the local variable `password`, or by renaming the variable
to something else (if setting the user's password is the desired effect).

Technical rationale: Because 'password' and 'names' are handled by special rules
in the parser, the "set" grammar is ambiguous w.r.t. a LALR(1) parser in a way that
can't be easily resolved (without breaking backwards compatibility).

Added to 5.0.15 chhangelog (thanks PEM):

  <listitem>
        <para>
          When declaring a local variable (or parameter) named
<literal>password</literal> or <literal>name</literal>', and
          setting it with SET, e.g. "<literal>set password = ''</literal>", the new error
message:
          <literal>ERROR 42000: Variable 'nnn' must be quoted with `...`, or
renamed</literal>
          is returned (where 'nnn' is 'password' or 'names'). This means there is syntax
          conflict
          with special sentences like <literal>set password = password(...)</literal>
(for setting a user's
          password)
          and <literal>set names default</literal> (for setting charset and collation).
          </para><para>
          This must be resolved by either quoting the variable name: <literal>set
`password` =
          ...</literal>,
          which will set the local variable <literal>`password`</literal>, or by renaming
the variable
          to something else (if setting the user's password is the desired effect).
        </para>
      </listitem>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32421