Bug #52326 escapes have no effect when inserting to usertables
Submitted: 24 Mar 2010 10:35 Modified: 24 Mar 2010 11:37
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.45 OS:Microsoft Windows (probably any)
Assigned to: CPU Architecture:Any

[24 Mar 2010 10:35] Peter Laursen
Description:
The description here
http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html
.. does not seem correct for inserting to specific system/user tables.  The escape character ( " \ " ) is treated as a literal.

Maybe category for this report should rather be 'parser' or 'docs' .. I cannot judge. Change as you like. 

How to repeat:
INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv,               update_priv, delete_priv,  create_priv, drop_priv, reload_priv, shutdown_priv,               process_priv,  file_priv, grant_priv, references_priv, index_priv, alter_priv, show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv,                   execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv, event_priv, trigger_priv ) VALUES ( 'my\_host', 'peter\_me', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );

INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, event_priv, trigger_priv) VALUES('my\_host', 'information_schema', 'peter\_me',         'Y','N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');

SELECT * FROM mysql.user WHERE `host` = 'my\_host';
/*
Host      User       Password  Select_priv  Insert_priv  Update_priv  Delete_priv  Create_priv  Drop_priv  Reload_priv  Shutdown_priv  Process_priv  File_priv  Grant_priv  References_priv  Index_priv  Alter_priv  Show_db_priv  Super_priv  Create_tmp_table_priv  Lock_tables_priv  Execute_priv  Repl_slave_priv  Repl_client_priv  Create_view_priv  Show_view_priv  Create_routine_priv  Alter_routine_priv  Create_user_priv  Event_priv  Trigger_priv  ssl_type  ssl_cipher  x509_issuer  x509_subject  max_questions  max_updates  max_connections  max_user_connections
--------  ---------  --------  -----------  -----------  -----------  -----------  -----------  ---------  -----------  -------------  ------------  ---------  ----------  ---------------  ----------  ----------  ------------  ----------  ---------------------  ----------------  ------------  ---------------  ----------------  ----------------  --------------  -------------------  ------------------  ----------------  ----------  ------------  --------  ----------  -----------  ------------  -------------  -----------  ---------------  --------------------
my\_host  peter\_me            N            N            N            N            N            N          N            N              N            
*/

SELECT * FROM mysql.db WHERE `host` = 'my\_host';
/*
Host      Db                  User       Select_priv  Insert_priv  Update_priv  Delete_priv  Create_priv  Drop_priv  Grant_priv  References_priv  Index_priv  Alter_priv  Create_tmp_table_priv  Lock_tables_priv  Create_view_priv  Show_view_priv  Create_routine_priv  Alter_routine_priv  Execute_priv  Event_priv  Trigger_priv
--------  ------------------  ---------  -----------  -----------  -----------  -----------  -----------  ---------  ----------  ---------------  ----------  ----------  ---------------------  ----------------  ----------------  --------------  -------------------  ------------------  ------------  ----------  ------------
my\_host  information_schema  peter\_me  Y            N            N            N            N            N          N           N                N           N           N                      N                 N                 N               N                    N                   N             N           N           
*/

(SQLyog used, but checked with command line too).

Suggested fix:
I am not sure.  Not even sure if anything should be fixed - maybe only documented.  Touching this part of code will always create problems.

Also there is an old discussion here :
http://bugs.mysql.com/bug.php?id=34520

It is rather important to know if '_' in 'my_host' is a wildcard (and will need to be escaped to be understood as a literal) or not.  Besides I think that "\" is not valid in a hostname.

I think there is an undocumented and recent change of server behavior here (we have among other a fresh report by a user using 5.0.51 describing another behavior than what I do here). I did not check the behavior with GRANT syntax.
[24 Mar 2010 10:50] Peter Laursen
fixed typos in synopsis
[24 Mar 2010 10:58] Sveta Smirnova
Thank you for the report.

At the same page link to you provided there is: "If you use “\%” or “\_” outside of pattern-matching contexts, they evaluate to the strings “\%” and “\_”, not to “%” and “_”. " So behavior is correct.

Also same behavior exists in version 5.0.45, so this is not recent changes. GRANT syntax inserts same values with \_. So I mark this report as "Not a Bug", but link with old bug about GRANT syntax.
[24 Mar 2010 11:37] Peter Laursen
Our user confirms that 5.0.32 (for Debian/Lenny) handles the "\" cahracter as an escape also outside 'pattern matching'. In his has it is the PLESK server administrative software that creates the user. Specifies "my\_user" in an INSERT statement what inserts "my_user".

So this is just to be considered an early 5.0x bug that we should forget about?
[24 Mar 2010 18:44] Sveta Smirnova
This even more like PLESK bug or feature. At least I can not repeat it with our old versions like 5.0.27.