tableExists() cannot handle Database Name with dot

[mbnl]

PHP Version

8.1

CodeIgniter4 Version

4.4.5

CodeIgniter4 Installation Method

Manual (zip or tar.gz)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

MySQL 8.0

What happened?

Hello, I'm having a problem

My database name;

com.sitedb.web

The operation I intend to perform is tableExists('tableName').

When I run this code, I receive a Database Exception error. Upon detailed examination of the error, I found that CodeIgniter 4 (CI4) was sending the following MySQL command:

SHOW TABLES FROM `com`.`sitedb`.`web`

Steps to Reproduce

That should have been the right command;

SHOW TABLES FROM `com.sitedb.web`

Due to the dots . in my database name, CI4 was treating it as `.`, thus causing the issue. However, since I'm using a hosting service, I cannot change the database name. Upon inspecting the source code, I found that in system/Database/MySQLi/Connection.php, line 391, the code was modified as follows:

$sql = 'SHOW TABLES FROM ' . $this->escapeIdentifiers($this->database);

I identified that the problem stems from the escapeIdentifiers method. Instead of modifying this method, I updated the line as follows:

"$sql = 'SHOW TABLES FROM `' . $this->escapeLikeStringDirect($this->database).'`';"

so that the mysql command was correctly as follows

SHOW TABLES FROM `com.sitedb.web`

This solution temporarily resolved my issue. However, when I update to a new version of CI4, the old code will return, potentially causing the same error in the future. I would appreciate it if you could address this issue and provide a permanent solution.
Thank you.

Expected Output

SHOW TABLES FROM `com.sitedb.web`

Anything else?

No response

[datamweb]

CodeIgniter doesn’t support dots (.) in the database, table, and column names

[mbnl]

CodeIgniter doesn’t support dots (.) in the database, table, and column names

Yes, I have already noticed this. Since it is a hosting provider, I cannot provide a change as a database, so if a permanent solution is possible, not only me but also the errors that may occur in the future will be eliminated. I have already produced my own solution, I just wanted to report it.

[ddevsr]

@mbnl Whats a solution you have? Feel free to send PR

[kenjis]

This is clearly documented. So this is not a bug.

Note
CodeIgniter doesn’t support dots (.) in the database, table, and column names.
https://codeigniter4.github.io/CodeIgniter4/database/examples.html

Probably we cannot fix this.
This is because it is impossible to determine whether a dot is a part of a database name or an identifier delimiter.

[kenjis]

@mbnl If you have a good idea, feel free to send PRs.

[kenjis]

This is not a bug, but I am reopening it for reconsideration.

[kenjis]

@codeigniter4/database-team @codeigniter4/core-team
This issue is because of $this->escapeIdentifiers($this->database).

CodeIgniter4/system/Database/MySQLi/Connection.php

Lines 390 to 400 in 7109931

	$sql = 'SHOW TABLES FROM ' . $this->escapeIdentifiers($this->database);
	if ($tableName !== null) {
	return $sql . ' LIKE ' . $this->escape($tableName);
	}
	if ($prefixLimit !== false && $this->DBPrefix !== '') {
	return $sql . " LIKE '" . $this->escapeLikeStringDirect($this->DBPrefix) . "%'";
	}
	return $sql;

Is there possibility that $this->database is not only a database name?
That is, may it contain a hostname or schema name?

If $this->database is just a database name only, we can add a new escape method for database names, and can handle a database name with dots.

[mbnl]

@codeigniter4/database-team @codeigniter4/core-team This issue is because of $this->escapeIdentifiers($this->database).

CodeIgniter4/system/Database/MySQLi/Connection.php

Lines 390 to 400 in 7109931

	$sql = 'SHOW TABLES FROM ' . $this->escapeIdentifiers($this->database);
	if ($tableName !== null) {
	return $sql . ' LIKE ' . $this->escape($tableName);
	}
	if ($prefixLimit !== false && $this->DBPrefix !== '') {
	return $sql . " LIKE '" . $this->escapeLikeStringDirect($this->DBPrefix) . "%'";
	}
	return $sql;

Is there possibility that $this->database is not only a database name? That is, may it contain a hostname or schema name?

If $this->database is just a database name only, we can add a new escape method for database names, and can handle a database name with dots.

Yes, only the database name. There is no prefix or hostname. It is a mysql 8.0 database created through the hosting panel.

[kenjis]

The $this->database comes from Config\Database:

CodeIgniter4/app/Config/Database.php

Line 34 in d8e4de2

'database' => '',

and it is just a database name only.
We also have hostname and schema in the config array.

So we can support database name with dots.

@mbnl I sent a PR #8664
Check it if you can.

[kenjis]

Closed by #8664