0

Cake3 ORM Question: Is there a trick to avoid MySQL keyword errors in SQL strings?

I get a "1064 You have an error in your SQL syntax" error when I try to update my users table.

UPDATE users SET group = 'admin', modified = '2016-06-27 15:45:02' WHERE id = 2

How can I add the table name users.group to the SQL string to avoid the keyword error?

Controller.action (efault)

public function edit($id = null)
{
    $user = $this->Users->get($id);
    if ($this->request->is(['patch', 'post', 'put'])) {
        $user = $this->Users->patchEntity($user, $this->request->data);
        if ($this->Users->save($user)) {
            $this->Flash->success(__('The Entry has been saved.'));
            return $this->redirect(['action' => 'index']);
        } else {
            $this->Flash->error(__('The Entry could not be saved. Please, try again.'));
        }
    }
    $this->set(compact('user', 'contacts', 'userLists'));
    $this->set('_serialize', ['user']);
}
hasentopf
  • 755
  • 1
  • 12
  • 27
  • You should show the code that you are using which actually produces this query, as possible solutions depend on that. – ndm Jun 27 '16 at 16:12
  • yes, I totally understand your comment. But my problem is not a code problem. It is the ORM what produces the update SQL without the table name. That causes the SQL syntax error, because GROUP is a MySQL keyword. – hasentopf Jun 27 '16 at 16:27
  • Sure, I got that, but there are multiple ways to make the ORM/query-builder to generate that query, and depending on the how, the problem can be solved in different ways. You could for example have used `Query::update()`. – ndm Jun 27 '16 at 16:59
  • The duplicate uses bake, but it's the same problem, and the same solution applies here. – ndm Jun 27 '16 at 17:01
  • Thanks. Last question to this issue. Where do I "enable this feature at runtime" ? – hasentopf Jun 28 '16 at 05:49
  • Via the DB drivers `autoQuoting()` method.Have you checked the linked docs? – ndm Jun 28 '16 at 12:19
  • In the moment I put the dynamic database config change in the edit/add action of the controller. This is a working solution. I was just wondering if there is a better place (model callback?!). – hasentopf Jun 28 '16 at 14:20
  • Oh ok, I somehow read this as "_how do I ..._". Well, depends on where/when you want the auto-quoting to be applied, ie whether it should apply to a specific model only, or only in a specific controller/action (to all models), or to all models at all time, etc... I could imagine applying it to a specific model only could be problematic. – ndm Jun 28 '16 at 15:02

0 Answers0