CakePHP 3 + ORM Query builder and column name escape

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

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!

Recommended topics

Hot tags