CakePHP 3 + ORM Query builder and column name escape

Question

A've already rewrite some application from CakePHP 2 to CakePHP 3. I have some structure in database with column: key (autogenerated key-string). In mysql key is a keyword, so when I write an SQL Query I have to escape it as

INSERT INTO table (`key`) VALUES (....)

Unfortunately when I try to save Entity I receive an error:

[42000][1064] 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 'key, 

That error is triggered in Cake\ORM\Table in _insert() method on that place:

$statement = $this->query()->insert(array_keys($data))
        ->values($data)
        ->execute();

Any suggestions how to avoid that situation? CakePHP version 3.5.11

Answer 1

Either rename the column to avoid that conflict, or enable CakePHPs automatic identifier quoting, either globally in the database connection configuration via the quoteIdentifiers option:

// in config/app.php

'Datasources' => [
    'default' => [
        // ...
        'quoteIdentifiers' => true,
    ],

    // ...
]

or only for that specific operation by either toggling the drivers auto quoting flag on the fly:

$driver = $this->getConnection()->getDriver();
$autoQuouting = $driver->isAutoQuotingEnabled();
$driver->enableAutoQuoting(true);

$this->query()/* ... */;

$driver->enableAutoQuoting($autoQuouting);

or by manually quoting the name and passing it in a quoted fashion:

$connection = $this->getConnection();
$quotedColumnName = $connection->quoteIdentifier($columnName);
// ...

this is currently compatible with auto quoting, ie the quoted name wouldn't be quoted twice if auto auoting would be enabled, but I'm not sure if this is actually a promised behavior!

See also

• Cookbook > Database Access & ORM > Database Basics > Identifier Quoting
• Cookbook > Database Access & ORM > Database Basics > Configuration
• API > \Cake\Database\Driver::enableAutoQuoting()
• API > \Cake\Database\Driver::isAutoQuotingEnabled()
• API > \Cake\Database\Connection::quoteIdentifier()