MySQL Bugs: #34520: Late decoding of C-style escape sequences and privilege obfuscation

Bug #34520	Late decoding of C-style escape sequences and privilege obfuscation
Submitted:	13 Feb 2008 13:30	Modified:	18 Feb 2008 18:19
Reporter:	Gleb Shchepa	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
GRANT and REVOKE queries use C-style escaping to inline _ and % as regular characters (not wildcards) into their patterns: "\_" and "\%".
Also other common escape sequences may be used: "\\" as backslash character itself, "\n" as linefeed char, "\t" as tabulation etc.
Moreover, server decodes any unexpected escape-sequences like "\q" as "q" (just ignores backslash).

I.e. both database names `db` and `\db` should be recognized as `db`, and
both names `name\t1` and `name 1` (space in the middle of the name is a tabulation
character) should be interpreted as `name 1`.

However, the server decodes such things too late, it unescapes `\db' or `name\t1' only when matching these patterns with real database/table names:

How to repeat:
# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE db.t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

# `\db` should be decoded as `db` ...

mysql> GRANT SELECT ON `\db`.* TO 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

# ... but it is not:

mysql> SHOW GRANTS FOR 'user'@'%';
+---------------------------------------+
| Grants for user@%                     |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%'      | 
| GRANT SELECT ON `\db`.* TO 'user'@'%' | 
+---------------------------------------+
2 rows in set (0.00 sec)

# this should duplicate previous grant ...

mysql> GRANT SELECT ON db.* TO 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

# ... but it doesn't, there are 2 different privileges:

mysql> SHOW GRANTS FOR 'user'@'%';
+---------------------------------------+
| Grants for user@%                     |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%'      | 
| GRANT SELECT ON `db`.* TO 'user'@'%'  | 
| GRANT SELECT ON `\db`.* TO 'user'@'%' | 
+---------------------------------------+
3 rows in set (0.00 sec)

# this should revoke all grants on both `db` and `\db` if any

mysql> REVOKE ALL ON db.* FROM 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

# ... but it removes only grants on `db`:

mysql> SHOW GRANTS FOR 'user'@'%';
+---------------------------------------+
| Grants for user@%                     |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%'      | 
| GRANT SELECT ON `\db`.* TO 'user'@'%' | 
+---------------------------------------+
2 rows in set (0.00 sec)

# mysql -uuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

# here should be an access error:

mysql> SELECT * FROM db.t1;
Empty set (0.00 sec)

Suggested fix:
GRANT, REVOKE and acl_load() should decode escape-sequences (excluding \_ and \%).

test case (corrected)

Attachment: 34520.test (application/octet-stream, text), 809 bytes.

Escape sequences are not possible for identifiers. They are only possible for data strings.

You can create identifiers with or without quotes (`My:Identifier` or using ANSI_QUOTS: "My:Identifier").

Without quotes, you are only allowed to use signs, that are possible and not reserved.
You will get an Error, if you use something wrong here.

With quotes means: let it as it is.
When you use quotes, it means, you want to have signs like \, % or whatever at your identifier name.

15:43 < gleb> susanne: about #34520: this bug is not about escapes in identifiers, but it is about escapes in templates.
15:44 < susanne> ??
15:44 < gleb> susanne: GRANT and REVOKE use same string format as LIKE sentence
15:44 < gleb> salle: escapes are allowed too
15:44 < gleb> oops
15:44 < gleb> susanne: escapes are allowed too

Because of the database name example, I miss understood this.

I could reproduce this by using 5.0 bk-tree.

Verified as described.

I also request aa clarification in the documentation on this "GRANT and REVOKE use same string format as LIKE sentence".

I understand that servers up to 4.1 behave as Gleb expect?

> I also request aa clarification in the documentation on
> this "GRANT and REVOKE use same string format as LIKE sentence".

Information is available at http://dev.mysql.com/doc/refman/5.0/en/request-access.html

However, decoding of escape-sequences other than \_ and \% is an undocumented feature. 
Actually, the server doesn't decode them, it simply ignores backslashes before any character other than _ and %, so it "decodes" '\n' to 'n' etc - that is a bug:

> CREATE DATABASE name;
> CREATE TABLE name.t1 (a INT);
> CREATE USER 'user'@'%';
# `\name` should be decoded as `name` ...
> GRANT SELECT ON `\name`.* TO 'user'@'%';
# ... but it is not:
> SHOW GRANTS FOR 'user'@'%';

Grants for user@%
GRANT USAGE ON *.* TO 'user'@'%'
GRANT SELECT ON `\name`.* TO 'user'@'%'

# this should duplicate previous grant ...
> GRANT SELECT ON name.* TO 'user'@'%';
# ... but it doesn't, there are 2 different privileges:
> SHOW GRANTS FOR 'user'@'%';

Grants for user@%
GRANT USAGE ON *.* TO 'user'@'%'
GRANT SELECT ON `\name`.* TO 'user'@'%'
GRANT SELECT ON `name`.* TO 'user'@'%'

# this should revoke all grants on both `name` and `\name` if any
> REVOKE ALL ON name.* FROM 'user'@'%';
# ... but it removes only grants on `name`:
> SHOW GRANTS FOR 'user'@'%';

Grants for user@%
GRANT USAGE ON *.* TO 'user'@'%'
GRANT SELECT ON `\name`.* TO 'user'@'%'

# connect with username "user"
# here should be an access error:
> SELECT * FROM name.t1;
a

Why it's a bug ?
According to you, the manual doesn't say that GRANT should decode \xxx escapes
besides \_ and \%, the server does not decode them. Where's the bug ?

> Why it's a bug ?
> According to you, the manual doesn't say that GRANT should
> decode \xxx escapes besides \_ and \%, the server does not
> decode them. Where's the bug ?

The bug is here: after revoking all privileges on some database/table from some user, that user still be able to do everything with this database/table:

under root:
   GRANT ALL ON `\name`.* TO 'user'@'%';
   ...
   REVOKE ALL ON name.* FROM 'user'@'%';

under user@%:
   SELECT * FROM name.sometable;
   UPDATE name.othertable ...

"However, decoding of escape-sequences other than \_ and \% is an undocumented feature."

Undocumented features should not exist!  They should either be documented or disabled!  How can users know?  I also thing the MySQL development team has grown to a size where developers can only consider what is documented.

I request appropriate documentation for this LIKE-behaviour on this page:
http://dev.mysql.com/doc/refman/5.0/en/grant.html

> I request appropriate documentation for this LIKE-behaviour
> on this page: http://dev.mysql.com/doc/refman/5.0/en/grant.html

Looking at http://dev.mysql.com/doc/refman/5.0/en/grant.html:

"
Note

The “_” and “%” wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a “_” character as part of a database name, you should specify it as “\_” in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....
"

About other escape sequences: unexpected (and undocumented) processing of such sequences is a subject of this bug #34520.

See also bug #52326