Auto quote reserved words with Doctrine 2

Question

Is there a way to auto quote reserved words with Doctrine 2 when using $entityManager->find('entity', id) ?

When using the query builder this can be done but there should be a global configuration setting that does this? I don't want to have to specify it in the annotations for the reserved words.

Answer 1

This was an issue I raised a while back with the Doctrine team.

https://github.com/doctrine/doctrine2/issues/2409

The ticket was closed with the comment:

You have to manually escape characters with @Column(name="`integer`")

So I guess you'd need to deal with any reserved keywords in your annotations

Answer 2

4.6. Quoting Reserved Words

Sometimes it is necessary to quote a column or table name because of reserved word conflicts. Doctrine does not quote identifiers automatically, because it leads to more problems than it would solve. Quoting tables and column names needs to be done explicitly using ticks in the definition.

<?php
/** @Column(name="`number`", type="integer") */
private $number;

Doctrine will then quote this column name in all SQL statements according to the used database platform.

Identifier Quoting does not work for join column names or discriminator column names unless you are using a custom QuoteStrategy.

For more control over column quoting the Doctrine\ORM\Mapping\QuoteStrategy interface was introduced in 2.3. It is invoked for every column, table, alias and other SQL names. You can implement the QuoteStrategy and set it by calling Doctrine\ORM\Configuration#setQuoteStrategy().

The ANSI Quote Strategy was added, which assumes quoting is not necessary for any SQL name. You can use it with the following code:

<?php
use Doctrine\ORM\Mapping\AnsiQuoteStrategy;

$configuration->setQuoteStrategy(new AnsiQuoteStrategy());

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#quoting-reserved-words

Answer 3

It's not implemented by Doctrine just because it's too platform-depending.

All you need, is implement own QuoteStrategy.

For example, for symfony project:

---

Copy-paste vendor AnsiQuoteStrategy class, rename it and make some quoting:

AppBundle/ORM/QuoteStrategy.php

namespace AppBundle\ORM;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\ORM\Mapping as M;

class QuoteStrategy implements M\QuoteStrategy
{
  private function quote($token, AbstractPlatform $platform)
  {
    // implement your quote strategy
    switch ($platform->getName()) {
      case 'mysql':
      default:
        return '`' . $token . '`';
    }
  }

  // add quoting to appropriate methods
  public function getColumnName($fieldName, M\ClassMetadata $class, AbstractPlatform $platform)
  {
    return $this->quote($class->fieldMappings[$fieldName]['columnName'], $platform);
  }
  // ... Rest methods
}  

Then, register your quote strategy as a service:

src/AppBundle/Resources/config/services.yml

  app.orm.quote_strategy:
    class: AppBundle\ORM\QuoteStrategy
    public: false

Then, use it for your entitymanager configuration:
app/config/config.yml

orm:
  entity_managers:
    default:
      quote_strategy: app.orm.quote_strategy

That is all :)

Answer 4

Per the statement made by @tim-lytle, I have re-raised the issue. This really should be included with Doctrine ORM's scope of safety.

https://github.com/doctrine/doctrine2/issues/5874