[3.0]: Cannot install to database with `.` in it

[Daaaav]

Basic Information

(Split off from #8961)

If the database name contains a ., the installer will report an SQL syntax error.

Steps to reproduce

1. Run the 3.0 installer
2. Enter a database name with a . in it (such as testforum_smf_3.0_a2025)
3. Get to step 4: Forum Settings, and click Continue

Expected result

The database gets populated.

Actual result

The installer fails with You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`0_a2025`' at line 2

Version/Git revision

3.0 Alpha 4, 9d4dfaf

Database Engine

MySQL

Database Version

10.11.13-MariaDB-0ubuntu0.24.04.1

PHP Version

8.1.33

Logs

PHP Fatal error:  Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`0_a2025`' at line 2 in .../SMF3/Sources/Db/APIs/MySQL.php:219
Stack trace:
#0 .../SMF3/Sources/Db/APIs/MySQL.php(219): mysqli_query(Object(mysqli), 'SHOW TABLES\n\t\t\t...', 0)
#1 .../SMF3/Sources/Db/APIs/MySQL.php(1227): SMF\Db\APIs\MySQL->query('SHOW TABLES\n\t\t\t...', Array)
#2 .../SMF3/Sources/Maintenance/Tools/Install.php(688): SMF\Db\APIs\MySQL->list_tables()
#3 .../SMF3/Sources/Maintenance/Maintenance.php(294): SMF\Maintenance\Tools\Install->databasePopulation()
#4 .../SMF3/install.php(31): SMF\Maintenance\Maintenance->execute(1)
#5 {main}
  thrown in .../SMF3/Sources/Db/APIs/MySQL.php on line 219

Additional Information

No response

[jdarwood007]

Seems to be this code:

SMF/Sources/Db/APIs/MySQL.php

Lines 1221 to 1229 in b341825

	$request = $this->query(
	'SHOW TABLES
	FROM {identifier:db}
	{raw:filter}',
	[
	'db' => strtr($db, ['`' => '']),
	'filter' => $filter,
	],
	);

Which was changed in #8906

The problem is that we are stripping out the escapes around the identifier, which causes the period to not be accepted for ad database name.

@Sesquipedalian Your thoughts here. The original code didn't make much sense in that we strip out the backtick

But it has been this way for 14+ years:
https://github.com/SimpleMachines/SMF/blame/d6dbdbdc3f9fd101201b06e668d9fc21ed00a819/Sources/DbExtra-mysql.php#L242

I didn't test, but I suspect the problem has existed for all these years and only now that we are making full use of our database abstraction layer for installs, do we see the problem.

[Sesquipedalian]

I agree with your assessment, @jdarwood007. This looks like an old bug we are only finding now.

I suspect that similar issues might also exist in some or all of the following methods:

• MySQL::list_tables()
• MySQL::create_table()
• MySQL::drop_table()
• MySQL::rename_table()
• MySQL::table_structure()
• MySQL::list_columns()
• MySQL::list_indexes()

[Daaaav]

Thought I should mention in case it helps, I've used similar names containing "2.0" and "2.1" in the database name for other test installs, and never noticed any problems running and playing around with them - but I haven't installed any big mods in them that would create their own tables or anything like that.

[Sesquipedalian]

but I haven't installed any big mods in them that would create their own tables or anything like that.

Yeah, this issue wouldn't show up in 2.0 and 2.1 unless and until a mod tried to call list_tables().

[albertlast]

my pov, just remove or just not allow . in the name.

[jdarwood007]

If we change that line to allow it, things are still broken because of the identifier call here:

SMF/Sources/Db/APIs/MySQL.php

Lines 2776 to 2779 in a7d14f6

	case 'identifier':
	// Backticks inside identifiers are supported as of MySQL 4.1. We don't need them for SMF.
	return '`' . implode('`.`', array_filter(explode('.', strtr($replacement, ['`' => ''])), 'strlen')) . '`';

As such, I recommend we no longer support periods in the database name. Easy enough to do for the installer. The upgrader can warn that it is unsupported, and renaming the database is recommended.

Thoughts?

[Oldiesmann]

How common is it for people to use periods in the database name?