cakephp - Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual

Question

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 '@g.com, 'User'.'phone' = 87665r5, 'User'.'address' = 23lsdhf, 'User'.'location' ' at line 1

SQL Query: UPDATE 'cake'.'users' AS 'User' SET 'User'.'username' = paul, 'User'.'password' = eben, 'User'.'email' = [email protected], 'User'.'phone' = 87665r5, 'User'.'address' = 23lsdhf, 'User'.'location' = lskjaflasi, 'User'.'pincode' = 867567 WHERE 'User'.'id' = 1

My code is

       if($this->request->data)
        {$User=$this->request->data[User];
    $this->User->updateAll($User,array("User.id" => $v));}

How can I update the whole form?

Answer 1

updateAll() does not automatically wrap string values in quotes unlike when using save(). You have to do this yourself. From the docs:-

Literal values should be quoted manually using DboSource::value().

You need to wrap each string value in $this->request->data with quotes using something like the datasource's value() method before calling updateAll():-

$db = $this->getDataSource();
$value = $db->value($value, 'string');

It is advisable to not just pass $this->request->data to updateAll() anyway as someone could inject data into your database. Instead build a new array of save data from your request data and wrap strings as appropriate. For example:-

$user=$this->request->data[User]
$data = array(
    'username' => $db->value($user['username'], 'string'),
    'password' => $db->value($user['password'], 'string'),
    'email' => $db->value($user['email'], 'string'),
    'phone' => $db->value($user['phone'], 'string'),
    'address' => $db->value($user['address'], 'string'),
    'location' => $db->value($user['location'], 'string'),
    'pincode' => $db->value($user['pincode'], 'integer')
);
$this->User->updateAll($data, array("User.id" => $v));

Update

As an alternative to using updateAll() you would be better to use save() for what you are doing here. As long as your save data contains the record's primary key (e.g. User.id) it will perform an UPDATE rather than an INSERT:-

$this->request->data['User']['id'] = $v;
$this->User->save($this->request->data);

save() will handle all the strings for you so there is no need for wrapping them in quotes yourself.