MySQL Bugs: #68175: Database names containing an underscore showing up escaped/ cause SQL Error

Bug #68175	Database names containing an underscore showing up escaped/ cause SQL Error
Submitted:	25 Jan 2013 0:37	Modified:	2 Aug 2013 14:16
Reporter:	Mike Reid	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S3 (Non-critical)
Version:	5.2.46 (Revision 10386)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Database name, grant, permissions, schema name, underscore

Description:
You can no longer assign schema privileges using the provided database list if the database name contains an underscore.

The database name shows underscores "escaped" (e.g. "sample\_database\_name\_here")

Trying to add permissions to said database throws an SQL error:

Error executing 'GRANT SELECT ON sample\_table.* TO 'user'@'localhost''
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
SQL Error: 1064

How to repeat:
1. Create a database with an underscore in the name (e.g. sample_database)
2. Open MySQL Administrator
3. Go to Users and Privileges, choose Schema Privileges tab, select a user.
4. Click "Add Entry..." button
5. Under the Schema section, choose "Selected schema:" option
6. Select a database name containing an underscore (It will be escaped like "sample\_database") and press OK.
7. Select some Object Rights. (e.g. SELECT, INSERT, etc)
8. Click "Save Changes"
9. See SQL Error: 1064 prompt 

Suggested fix:
Correctly account for underscores / prevent escaping. (\_ should be: _)

Hello Mike,

Thank you for the report.
Verified as described.

Thanks,
Umesh

Database names containing an underscore showing up escaped/ cause SQL Error

Attachment: 68175.png (image/png, text), 131.36 KiB.

Updated impacted version to latest 5.2.46 release. Also, changed OS from "Mac OS X" to "Any" since provided confirmation / screenshot appears to be from Linux.

This error also occurs on Windows 8 64bit platform.

Just checked and this bug still exists in MySQL Workbench version 5.2.47 CE Revision 10398

Temporary workaround (and how it should be fixed in Workbench):

Run SQL manually, using quotes around database name:

GRANT SELECT ON `sample\_table`.* TO 'user'@'localhost'

Also works with wildcards:

GRANT SELECT ON `sample\_%`.* TO 'user'@'localhost'

http://bugs.mysql.com/bug.php?id=68475 marked as duplicate of this one.

http://bugs.mysql.com/bug.php?id=68540 marked as duplicate.

http://bugs.mysql.com/bug.php?id=68627 marked as duplicate of this one.

Same thing happening to me on both Windows and Mac versions

http://bugs.mysql.com/bug.php?id=69056 duplicate of this one.

http://bugs.mysql.com/bug.php?id=69106 marked as duplicate of this one.

This bug looks a lot like a bug in oracle workbench ---->

The driver calls used to display the list of tables and views in a specific schema expects a wildcard expression. To avoid listing the objects for USERX1 when displaying the objects for USER_1 the underscore must be escaped. The driver will create an expression similar to AND onwer LIKE 'USER_1' ESCAPE '\' (which would return tables for USERA1, USERB1 and so on, including of course USER_1).

The character that is used to escape the wildcards is reported by the driver. SQL Workbench/J sends e.g. the value USER\_1 if the driver reports that a backslash is used to escape wildcards.

However some older Oracle drivers report the wrong escape character, so the value sent to the database results in AND onwer LIKE 'USER\_1' ESCAPE '/'. The backslash in the expression is the character reported by the driver, whereas the forward slash in the expression is the character actually used by the driver.

To fix this problem, the escape character reported by the driver can be overridden by setting a property in workbench.settings:

workbench.db.oracle.searchstringescape=/

------------------

Is there a workaround similar to the above fix that we can manually do on windows to get it working in the interim??

http://bugs.mysql.com/bug.php?id=69256 marked as duplicate of this one.

Bug #69404 is marked as duplicate of this one.

Fixed as of the upcoming MySQL Workbench 6.0.2 public beta release, and here's the changelog entry:

A database with a name that contained an underscore could not be assigned
schema privileges using the provided database list in the Server
Administrator, as the database name was not escaped properly.

Thank you for the bug report.

This bug still exist in 6.0.2.10924

http://bugs.mysql.com/bug.php?id=69742 marked as duplicate with this one.

http://bugs.mysql.com/bug.php?id=69792 marked as duplicate of this one.

Reopening this bug as it appears to have resurfaced in the new 6.0 BETA.

False alarm (closing) -- while the entry stored in mysql.db still contains escaped underscores, and so does the list of databases in Workbench, it appears the error is gone and permissions still work.

http://bugs.mysql.com/bug.php?id=69909 duplicate of this one.

This bug still exists in Windows Workbench 6.2.3.12312 against a Redhat Oracle Linux MySQL database version 5.5.40