Double escaping underscore in privileges check process

[vasilevrv]

Hello,

phpmyadmin/libraries/classes/Util.php

Line 2311 in 583e61f

$tbl = str_replace(['%', '_'], ['\%', '\_'], $tbl);

We get incorrect result SQL query (and triggers unavailable, user has privileges for table and does not have full database privileges):

SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` WHERE GRANTEE='''myyser''@''localhost''' AND PRIVILEGE_TYPE='TRIGGER' AND 'TESTDB' LIKE `TABLE_SCHEMA` AND TABLE_NAME='**posts\\\\_description**';

If comment this line - it working perfectly.
Version with confirmed problem - 5.0.4, line 4042:
https://github.com/phpmyadmin/phpmyadmin/blob/RELEASE_5_0_4/libraries/classes/Util.php#L4042

But current version have the same code.

This function called from libraries/classes/Menu.php and "Triggers" menu item is not visible and in Triggers page all functional unavailable.

Please check and fix it. Thanks in advance!

[williamdes]

Hi @vasilevrv
I added tests to cover this function in 29c31be
I can confirm that there is some escaping and It seems to be intentional.
See: #9961

[vasilevrv]

Thanks, @williamdes! Will you fix it in future release?

[williamdes]

Thanks, @williamdes! Will you fix it in future release?

Sure, if this is a real bug. I am currently investigating to find out why this fix was done

[williamdes]

SELECT * FROM `TABLE_PRIVILEGES`

I just tried a MySQL 5.5 and it seems to be the same, TABLE_PRIVILEGES seem not to have an escaping
Investigating further

[williamdes]

Some time ago

phpmyadmin/libraries/Util.php

Lines 4152 to 4153 in a303eb8

	// need to escape wildcards in db and table names, see bug #3518484
	$tbl = str_replace(array('%', '_'), array('\%', '\_'), $tbl);

The first time it was added it was in 3.5.1 by eca291c

phpmyadmin/libraries/common.lib.php

Lines 3772 to 3773 in 5ff7adb

	// need to escape wildcards in db and table names, see bug #3518484
	$tbl = str_replace(array('%', '_'), array('\%', '\_'), $tbl);

[vasilevrv]

@williamdes, the problem was found in MariaDB 10.5.8, maybe everything is different in this version?

[williamdes]

@williamdes, the problem was found in MariaDB 10.5.8, maybe everything is different in this version?

I tried with MariaDB 10.5 🤔

Maybe you can try on my deployed server that has a bunch of different servers and versions: https://phpmyadmin-dev.wdes.eu.org/
(root/public public/public)

[williamdes]

For now I can not find any documentation that says that escaping is needed or not. But it could make sense that for TABLE_PRIVILEGES no escaping is needed. Maybe you just found a very very old bug 🤔
If you find out something please post it here, I will continue to research

[vasilevrv]

Hello again, @williamdes!

In MariaDB 10.5.8 result for your query:

MariaDB [information_schema]> SELECT * FROM `TABLE_PRIVILEGES` LIMIT 10;
+------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE    | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------+---------------+--------------+------------+----------------+--------------+
| 'devuser'@'%' | def           | DEV          | action_log | SELECT         | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | INSERT         | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | UPDATE         | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | DELETE         | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | CREATE         | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | DROP           | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | REFERENCES     | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | INDEX          | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | ALTER          | NO           |
| 'devuser'@'%' | def           | DEV          | action_log | CREATE VIEW    | NO           |
+------------+---------------+--------------+------------+----------------+--------------+

Underscores is not escaped.

For example,

MariaDB [information_schema]> SELECT * FROM `TABLE_PRIVILEGES` WHERE TABLE_NAME="action_log" AND PRIVILEGE_TYPE="SELECT";
+--------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE            | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+--------------+------------+----------------+--------------+
| 'devuser'@'%'         | def           | DEV          | action_log | SELECT         | NO           |
| 'devuser'@'localhost' | def           | DEV          | action_log | SELECT         | NO           |
+--------------------+---------------+--------------+------------+----------------+--------------+
2 rows in set (0.008 sec)

And with escaping:

MariaDB [information_schema]> SELECT * FROM `TABLE_PRIVILEGES` WHERE TABLE_NAME="action\_log" AND PRIVILEGE_TYPE="SELECT";
Empty set (0.012 sec)

Sure, I think the problem is that the TABLE_SCHEMA is escaped (maybe, I haven't tested), but the TABLE_NAME is not

[vasilevrv]

That sounds about right!

https://dev.mysql.com/doc/refman/8.0/en/request-access.html

The wildcard characters % and _ can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (_) as part of a database name, specify it as _ in the GRANT statement.

as a result, need escape only Host and Db, as I understand

[williamdes]

https://web.archive.org/web/20130510164857/http://dev.mysql.com/doc/refman/5.1/en/request-access.html

The Db, Table_name, Column_name, and Routine_name columns cannot contain wildcards or be blank.

Thank you @shucon for the link