Database: Mysqli: Provide context on query exceptions

[element-code]

Description Provide a context to the error in the error log

Checklist:

• [ ] Securely signed commits
• [ ] Component(s) with PHPDoc blocks, only if necessary or adds value
• [ ] Unit testing, with >80% coverage
• [ ] User guide updated
• [ ] Conforms to style guide

[element-code]

The CS Fixer pre commit hook right now runs for the complete project on my machine (which leads to many errors) instead of just running for the changed file.

Is that how it's supposed to be? Might be something with autocrlf on my Windows machine tho?

[kenjis]

Thank you for sending a PR.

First, we need GPG sign for commits. https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/pull_request.md#signing

Second, we need PHPUnit testing to prove the implementation. https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/pull_request.md#unit-testing

[kenjis]

Is that how it's supposed to be?

Yes, it checks all files. But the existing files have no errors.

Don't use autocrlf.

[MGatner]

I run my own checks then skip the hook with --no-verify and let the GitHub Actions have the final check. I think the hook is good for people who aren't familiar with the dev tools (?) but it is annoying.

[kenjis]

It seems this PR is an enhancement, if so, you need to change the base branch to 4.3, not develop.

[paulbalandan]

So the {query} context will be provided in the exception message itself?

[kenjis]

So the {query} context will be provided in the exception message itself?

It does not seem.

ERROR - 2022-06-28 06:50:11 --> mysqli_sql_exception: Table 'ci4_test.table_does_not_exist' doesn't exist in /Users/kenji/work/codeigniter/CodeIgniter4/system/Database/MySQLi/Connection.php:292
Stack trace:
#0 /Users/kenji/work/codeigniter/CodeIgniter4/system/Database/MySQLi/Connection.php(292): mysqli->query('SELECT * FROM t...', 0)
#1 /Users/kenji/work/codeigniter/CodeIgniter4/system/Database/BaseConnection.php(691): CodeIgniter\Database\MySQLi\Connection->execute('SELECT * FROM t...')
#2 /Users/kenji/work/codeigniter/CodeIgniter4/system/Database/BaseConnection.php(618): CodeIgniter\Database\BaseConnection->simpleQuery('SELECT * FROM t...')
#3 /Users/kenji/work/codeigniter/CodeIgniter4/tests/system/Database/Live/BadQueryTest.php(49): CodeIgniter\Database\BaseConnection->query('SELECT * FROM t...')
#4 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestCase.php(1545): CodeIgniter\Database\Live\BadQueryTest->testBadQueryDebugFalse()
#5 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestCase.php(1151): PHPUnit\Framework\TestCase->runTest()
#6 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestResult.php(726): PHPUnit\Framework\TestCase->runBare()
#7 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestCase.php(903): PHPUnit\Framework\TestResult->run(Object(CodeIgniter\Database\Live\BadQueryTest))
#8 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestSuite.php(670): PHPUnit\Framework\TestCase->run(Object(PHPUnit\Framework\TestResult))
#9 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/Framework/TestSuite.php(670): PHPUnit\Framework\TestSuite->run(Object(PHPUnit\Framework\TestResult))
#10 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/TextUI/TestRunner.php(673): PHPUnit\Framework\TestSuite->run(Object(PHPUnit\Framework\TestResult))
#11 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/TextUI/Command.php(143): PHPUnit\TextUI\TestRunner->run(Object(PHPUnit\Framework\TestSuite), Array, Array, true)
#12 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/src/TextUI/Command.php(96): PHPUnit\TextUI\Command->run(Array, true)
#13 /Users/kenji/work/codeigniter/CodeIgniter4/vendor/phpunit/phpunit/phpunit(98): PHPUnit\TextUI\Command::main()
#14 {main}

[element-code]

In my logger there are more hacks than i anticipated. log_message('error', $e->getMessage() . ' in query: {query}', ['query' => $sql]); does the job but is dirty.

What i do in my logger:

• \CodeIgniter\Log\Logger::log does not only interpolate the $context in the message but also
• the handler \CodeIgniter\Log\Handlers\FileHandler::handle does accept the argument $context and can do with it whatever it wants..

Specific on the DB exceptions would be a nice thing to have $query available in the exception itself.. General its very helpful if you can provide a context to log_message.

[kenjis]

If all SQL statements are automatically logged when DB errors occur, this can be a security issue. If the SQL statement contains sensitive information, the log itself may become confidential and developers may not be able to access the log files at will by the security policies.

The ability to include SQL statements in the logs is useful, but I would prefer to be able to change it in the configuration.

[paulbalandan]

I think we can have a single QueryException class wrapping the exceptions thrown by the different drivers, which can add additional context like the SQL query executed. Then in the config file, we'll set up an array containing which info cannot be flashed or displayed in the logs, something close to the functionality of #[SensitiveParamete].

[MGatner]

r👈 you dropped this

[kenjis]

See https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/workflow.md#if-you-sent-to-the-wrong-branch

[MGatner]

@element-code I think this is a great feature to have. Are you able to address the PR logistics mentioned above so we can get this in?

[sclubricants]

The ability to include SQL statements in the logs is useful, but I would prefer to be able to change it in the configuration.

A configuration is a must. Some queries with *Batch() methods can get quite lengthy.

I quite often do a data dump from the execute method. It would be nice to have a config setting for this but also be able to set it in code when testing.

[kenjis]

No feedback. Closing.

If you still find this change useful, please submit another PR following the guidelines. https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/pull_request.md

Thank you.