When building queries, DBAL doesn't consider reserved keywords

[morozov]

Bug Report

When building queries, DBAL doesn't consider reserved keywords

Q	A
BC Break	no
Version	2.7.1

Summary

Assuming 'FILE' is a reserved keyword on the Oracle platform, I can create a table with the 'file' column using the schema manager but I can't execute an INSERT or UPDATE statement using the Connection API. I also cannot SELECT from the table using QueryBuilder.

How to reproduce

$conn = \Doctrine\DBAL\DriverManager::getConnection([
    'driver' => 'oci8',
    'host' => 'localhost',
    'user' => 'system',
    'password' => 'oracle',
    'dbname' => 'XE',
]);

$table = new Doctrine\DBAL\Schema\Table('test_table');
$table->addColumn('file', 'string');

$conn->getSchemaManager()
    ->createTable($table);

$conn->insert('test_table', ['file' => 'foo']);

// ORA-00928: missing SELECT keyword

$conn->createQueryBuilder()
    ->select('file')
    ->from('test_table')
    ->execute()
    ->fetchAll();

// ORA-00936: missing expression

Expected behavior

The keywords are handled consistently by the Schema Manager and Query Builder.

[morozov]

The same is reproducible with 'select' as the column name on other platforms.

[morozov]

@Ocramius we can use this issue as a starting point for discussion of whether/how identifiers should be quoted in the DBAL.

[leoqbc]

Hi guys I'm facing the same problem in MYSQL, I have a field called 'desc':

$conn->insert('my_table', [
    'desc' => 'Hello',
    ...
]);

The "desc" word throws a SyntaxException.

My solution was to put mysql quote as array parameter like this:

$conn->insert('my_table', [
    '`desc`' => 'Hello', // `desc`
    ...
]);

May it can be a parcial solution, can we discuss a fix??

Best.

[morozov]

@leoqbc we haven't given enough thought to this issue. Long story short, even if it's relatively easy to fix in Connection::insert() and ::update(), it's impossible to fix it in QueryBuilder w/o breaking its API.

Therefore, the only valid solution right now is to quote identifiers manually (e.g. by using Connection::quoteIdentifier()).

[leoqbc]

Make sense, as I imagined, not easy to patch...

Well Connection::quoteIdentifier() worked fine for me.

Maybe it can be refactored in future, since It breaks SOLID's SRP IMHO, but I dont know the complete API.

Thanks @morozov and good luck on this.

[morozov]

Closing as the issue is by design. The builder accepts SQL expressions as column names, so the consumer of the API is responsible for building proper SQL.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.