Bug: `spark db:table` causes an error with table name including special chars

[kenjis]

PHP Version

8.1

CodeIgniter4 Version

develop (30258d5099a2a93cd2dd1f66b0746ecd314c779b)

CodeIgniter4 Installation Method

Git

Which operating systems have you tested for this bug?

macOS

Which server did you use?

cli

Database

MySQL 5.7

What happened?

$ php spark db:table

CodeIgniter v4.2.7 Command Line Tool - Server Time: 2022-10-26 20:54:55 UTC-05:00

Here is the list of your database tables:
  [0]  , CONCAT('',`password`) AS `email`
  [1]  migrations
  [2]  zip_address

Which table do you want to see? [0, 1, 2]: 0

Data of Table ", CONCAT('',`password`) AS `email`":


[mysqli_sql_exception]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CONCAT('',`password`) AS `email`' at line 1

at SYSTEMPATH/Database/MySQLi/Connection.php:292

Backtrace:
  1    SYSTEMPATH/Database/MySQLi/Connection.php:292
       mysqli()->query('SHOW COLUMNS FROM , CONCAT(\'\',`password`) AS `email`', 0)

  2    SYSTEMPATH/Database/BaseConnection.php:666
       CodeIgniter\Database\MySQLi\Connection()->execute('SHOW COLUMNS FROM , CONCAT(\'\',`password`) AS `email`')

  3    SYSTEMPATH/Database/BaseConnection.php:593
       CodeIgniter\Database\BaseConnection()->simpleQuery('SHOW COLUMNS FROM , CONCAT(\'\',`password`) AS `email`')

  4    SYSTEMPATH/Database/BaseConnection.php:1451
       CodeIgniter\Database\BaseConnection()->query('SHOW COLUMNS FROM , CONCAT(\'\',`password`) AS `email`')

  5    SYSTEMPATH/Commands/Database/ShowTableInfo.php:162
       CodeIgniter\Database\BaseConnection()->getFieldNames(', CONCAT(\'\',`password`) AS `email`')

  6    SYSTEMPATH/Commands/Database/ShowTableInfo.php:143
       CodeIgniter\Commands\Database\ShowTableInfo()->showDataOfTable(', CONCAT(\'\',`password`) AS `email`', 10, 15)

  7    SYSTEMPATH/CLI/Commands.php:63
       CodeIgniter\Commands\Database\ShowTableInfo()->run([])

  8    SYSTEMPATH/CLI/CommandRunner.php:65
       CodeIgniter\CLI\Commands()->run('db:table', [])

  9    SYSTEMPATH/CLI/CommandRunner.php:51
       CodeIgniter\CLI\CommandRunner()->index([])

 10    SYSTEMPATH/CodeIgniter.php:920
       CodeIgniter\CLI\CommandRunner()->_remap('index', [...])

 11    SYSTEMPATH/CodeIgniter.php:482
       CodeIgniter\CodeIgniter()->runController(Object(CodeIgniter\CLI\CommandRunner))

 12    SYSTEMPATH/CodeIgniter.php:347
       CodeIgniter\CodeIgniter()->handleRequest(null, Object(Config\Cache), false)

 13    SYSTEMPATH/CLI/Console.php:48
       CodeIgniter\CodeIgniter()->run()

 14    ROOTPATH/spark:98
       CodeIgniter\CLI\Console()->run()

Steps to Reproduce

Create a table named

, CONCAT('',`password`) AS `email`

create table `, CONCAT('',``password``) AS ``email``` (id int, name varchar(10));

Run php spark db:table.

Expected Output

No error.

Anything else?

No response

[sclubricants]

I think its the protectIdentifiers() method.

[kenjis]

Related #8647

[kenjis]

@codeigniter4/database-team I sent #8695 Comments are welcome.

[kenjis]

I sent PR #8696 for a different approach.

[michalsn]

TBH, I have never created or worked with table names that contain special characters.

Will the same problem occur if we try to create a normal query with our Model class?

It seems like we're producing a lot of additional code that will be used only in theory - but this is only my experience.

[kenjis]

The command can list table names even if there is a table name that contains special characters, but when showing the table meta data, an mysqli_sql_exception occurs. This is a bug in which the escaping process is forgotten. So I would like to fix the error if possible.

Yes, usually there are no such tables, so the occurrence is infrequent.

However, protectIdentifiers() is too complex to maintain, so I would like to reduce its use if possible. Despite its name, protectIdentifiers() protects identifiers only if it seems to be able to protect, and in many cases it does not protect.

It just so happens that the error occurred with this command, but the real issue may be what to do with protectIdentifiers().