MySQL Bugs: #23683: SHOW DATABASES doesn't filter invalid directory names

Bug #23683	SHOW DATABASES doesn't filter invalid directory names
Submitted:	26 Oct 2006 19:33	Modified:	17 Jan 2014 15:28
Reporter:	Jose Luis Martinez	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.20	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Contribution, show databases dot name backslash

Description:
SHOW DATABASES doesn't filter out directories with invalid names in the mysql data directory.

I've observed this behaviour in mysql 4.1.11 too, so I suppose it has been there for some time now.

Although the server doesn't allow you to create a databases with invalid characters, they are displayed. This can cause automated tools to fail when trying to do operations with such databases.

MySQL manual says that database names can't contain '\' '/' '.' or characters that are not allowed in a directory name, and showing them is an inconsitent behaviour [http://dev.mysql.com/doc/refman/5.0/en/legal-names.html]. I have made mysql show databases with '.' and '\' in their name.

How to repeat:
$ mkdir [mysqldatadir]/test.one
$ mkdir [mysqldatadir]/test\\two
# set the privs so mysql can surely see the dir
$ chown mysql:mysql [mysqldatadir]/test.one
$ chown mysql:mysql [mysqldatadir]/test\\two

$ mysql -u root
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test.one           |
| test\two           |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test.one
ERROR 1102 (42000): Incorrect database name 'test.one'
mysql> use `test.one`
ERROR 1102 (42000): Incorrect database name 'test.one'
mysql> use `test\\two`
ERROR 1102 (42000): Incorrect database name 'test\two'

Suggested fix:
MySQL server should filter out all the database names that can't be "use"d when displaying them.

Thank you for the bug report.

Simple fix by calling check_db_name()

patch can be found at:
http://noslave.net/holman/devel/mysql/patches/show_databases_5_0_41.patch

or attached

it applies against 5.0.41

Attachment: show_databases_5_0_41.patch (application/octet-stream, text), 399 bytes.

Hi everybody,

I want to add a comment to this bug report. If lower_case_table_names system variable is set to 1 and a sub-directory is created in the data directory with the name that is not all lowercase, then this name is also should be treated as invalid and should be filtered out. For example, go to the data directory, and create the sub-directory FOO:

$ mkdir FOO

Then run SHOW DATABASES:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| FOO                | 
| mysql              | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

The name FOO shows up in the list. But an error happens if you try to "use" it:

mysql> use FOO;
ERROR 1049 (42000): Unknown database 'foo'

Regards,

Anatoly Pidruchny.

The problem with invalid symbols in db|table name is fixed in 5.1.

Example:
create database `test.one`;
create database `test\\two`;

show databases;
Database
information_schema
mysql
test
test.one
test\\two

use `test.one`;
select database();
database()
test.one
use `test\\two`;
select database();
database()
test\\two

The problem with lower_case_table_names and upper case db names will be fixed later(WL#922 Case-Sensitive Delimited Identifiers)