Using a column name called "before" with PDO

Question

I am working with Cakephp 3 and I would like to have a column called "before" in my Model.

Unfortunately, when I try to create and save an Entity, I got this error:

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before, after, created_at) VALUES (1047, 'blabla', 'toto', ' at line 1

What I did to create this entity is as usual:

$this->save(
    $this->newEntity([
        'tracker_id' => $trackerEntity->get('id'),
        'before' => 'blabla',
        'after' => $after,
        'created_at' => new \DateTime(),
    ])
);

If I rename my column "before" to another name like "toto", it's working. A MYSQL function "before" exists, so I guess that's why PDO doesn't like it...

Has anyone a trick to still use a column with this name? Like a way to escape the function?

Thanks

**http://stackoverflow.com/questions/27854333/how-to-safely-use-reserved-sql-names** — ndm, May 05 '17 at 20:35
Just add these thingies around \`before\` when you setup your query in the save method... — Ulrik McArdle, May 05 '17 at 20:38

score 2 · Accepted Answer · answered May 05 '17 at 20:47

2

Add 'quoteIdentifiers' => true in your datasource configuration in config/app.php to enable identifier quoting and you will be able to use reserved words.

answered May 05 '17 at 20:47

Rayann Nayran

1,135
7
15

1

Note that this will have an impact on performance, as the ORM needs to do extra work. It's a useful option if you *must* have a column named before, but if the design is entirely in your control, it might well be worth trying to think of an alternative that's not a reserved word. – Greg Schmidt May 07 '17 at 15:17

Using a column name called "before" with PDO

1 Answers1