CakePHP 3.0: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Question

I faced saving data to database using Form in CakePHP 3.0.

//add.ctp
<div>
    <?= $this->Form->create($deposit) ?>
    <fieldset>
        <legend><?= __('Add') ?></legend>
        <?php
            echo $this->Form->input('date');
            echo $this->Form->input('profile_id', ['options' => $profiles]);
            echo $this->Form->input('amnt');
            echo $this->Form->input('desc');
            echo $this->Form->input('user_id', ['options' => $users]);
        ?>
    </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>
</div>

Here is my add function

public function add()
{
    $deposit = $this->Deposits->newEntity();
    if ($this->request->is('post')) {
        $deposit = $this->Deposits->patchEntity($deposit, $this->request->data);
        if ($this->Deposits->save($deposit)) {
            $this->Flash->success(__('The member deposit has been saved.'));
            return $this->redirect(['action' => 'index']);
        } else {
            $this->Flash->error(__('The member deposit could not be saved. Please, try again.'));
        }
    }

    $profiles = $this->Deposits->Profiles->find('list', ['limit' => 200]);
    $users = $this->Deposits->Users->find('list', ['limit' => 200]);
    $this->set(compact('deposit', 'profiles', 'users'));
}

When i submitted the the Form i found below Database syntex Error

Error: 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 'desc, user_id, created, modified) VALUES ('2015-06-06', 7, '3211', 'some text', 1,' at line 1

And SQL Query was showing:

INSERT INTO member_deposits (date, profile_id, amnt, desc, user_id, created, modified) VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6)

I spent plenty of time resolving the issue by Googling and from Similar Post, no luck, but after spending a day i found to get resolve simply configuring quoteIdentifiers to true.

quoteIdentifiers is by default set to false at config/app.php under Datasources of your cake project.

I don't know cakePHP, but I suspect it is your unfortunate use of a reserved word for a column 'DESC' - https://dev.mysql.com/doc/refman/5.0/en/reserved-words.html. Either find a way to escape the field name (eg. `desc`) or change the column name. — crafter, Jun 06 '15 at 11:45
@crafter, it works if i change the column **desc** to anything. Thanks. — Bayezid Alam, Jun 06 '15 at 12:16
I see, that's laudable, however in that case it's a duplicate: **http://stackoverflow.com/questions/27854333/cakephp-3-using-sqlserver-as-datasource-server-bake-error-database-error** On a side note, answering your own questions, and thus sharing experience is very welcome, but it's advised that you post the solution as an actual answer, so that the question appears solved, see also **http://stackoverflow.com/help/self-answer** — ndm, Jun 06 '15 at 12:24

score 5 · Accepted Answer · answered Jun 06 '15 at 13:05

One of your columns is using a column name that is reserved by MySQL.

dev.mysql.com/doc/refman/5.0/en/reserved-words.html

As you can see, DESC is reserved.

If you can find a way to change the query to use escaped column name specifies, mysql will tolerate this. for example

INSERT INTO `member_deposits` (
    `date`, `profile_id`, `amnt`,
    `desc`, `user_id`, `created`, `modified`) 
VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6)

Alternatively, change the column name to something that does not violate the mysql reserved word rule.

CakePHP 3.0: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

1 Answers1