Cake php SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I got this error while adding users.

I have a view adduser.ctp

and in the User controller I have this function

 public function addUser() {
    $userGroups = $this->UserGroup->getGroups();
    unset($userGroups[1]);
    unset($userGroups[2]);
    $this->set('userGroups', $userGroups);
    if ($this->request->isPost()) {
        $this->User->set($this->data);
        if ($this->User->AddEditValidate()) {
            $this->request->data['User']['email_verified'] = 1;
            $this->request->data['User']['active'] = 2;
            $this->request->data['User']['parent_id'] = $this->Session->read('logged_client_id');
            $salt = $this->UserAuth->makeSalt();
            $this->request->data['User']['salt'] = $salt;
            $this->request->data['User']['temp_password'] = $this->request->data['User']['password'];
            $this->request->data['User']['password'] = $this->UserAuth->makePassword($this->request->data['User']['password'], $salt);
            if ($this->User->save($this->request->data, false)) {
                $this->sendLoginEmail($this->request->data['User']);
                $this->Session->setFlash(__('The user is successfully added'), 'success_flash');
                $this->redirect('/clientUsers/' . $this->Session->read('logged_client_id'));
            } else {
                $this->Session->setFlash(__('Problem in saving user information'), 'error_flash');
            }
        }
    }
}

Now in User model I validate by the following function

function AddEditValidate() {
    $validate1 = array(
        "user_group_id" => array(
            'rule' => array('comparison', '!=', 0),
            'message' => 'Please select group'),
        'first_name' => array(
            'mustNotEmpty' => array(
                'rule' => 'notEmpty',
                'message' => 'Please enter first name'),
            'custom' => array(
                'rule' => 'alphaNumericSpace',
                'message' => 'First name should be alphnumeric')
        ),
        'last_name' => array(
            'mustNotEmpty' => array(
                'rule' => 'notEmpty',
                'on' => 'create',
                'message' => 'Please enter last name'),
            'custom' => array(
                'rule' => 'alphaNumericSpace',
                'message' => 'Last name should be alphnumeric')
        ),
        'email' => array(
            'mustNotEmpty' => array(
                'rule' => 'notEmpty',
                'message' => 'Please enter email',
                'last' => true),
            'mustBeEmail' => array(
                'rule' => array('email'),
                'message' => 'Please enter valid email',
                'last' => true),
            'mustUnique' => array(
                'rule' => 'isEmailUnique',
                'message' => 'This email is already registered',
            )
        ),
        'oldpassword' => array(
            'mustNotEmpty' => array(
                'rule' => 'notEmpty',
                'message' => 'Please enter old password',
                'last' => true),
            'mustMatch' => array(
                'rule' => array('verifyOldPass'),
                'message' => 'Please enter correct old password'),
        ),
        'password' => array(
            'mustNotEmpty' => array(
                'rule' => 'notEmpty',
                'message' => 'Please enter password',
                'on' => 'create',
                'last' => true),
            'complex' => array(
                'rule' => array('complexPassword'),
                'message' => 'Password should be six characters long, which should have minimum one alphabet, one number and one special character',
            ),
        ),
        'captcha' => array(
            'mustMatch' => array(
                'rule' => array('recaptchaValidate'),
                'message' => ''),
        )
    );
    $this->validate = $validate1;
    return $this->validates();
}

Its gives me 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 'AddEditValidate' at line 1

SQL Query: AddEditValidate

Please help me.

Thanks

I know this question asked many times but it will not resolved my problem. In user controller the function is if ($this->User->AddEditValidate()) {} even i try if ($this->User->AddEditValidatee()) {} but it gives me the same error. Here in the question i enter AddEditValidatee its is mistake. i know it should be AddEditValidate. — Kamal
use $this->request->data not $this->data , edit your question for better understanding so that others can help — Abhishek
@Abhishek $this->request->data does not solve my problem and i edit the question. — Kamal
set the function to public and make it lowercase: public function addEditValidate — Nunser

Leobardo Romero Leobardo Romero · Accepted Answer · 2018-02-05T04:19:14

Database colum names are not quoted in version 3.0. You are using SQL keywords in your field names. If you cannot rename your columns, you should enable identifier quoting.

From book.cakephp.org:

$conn->driver()->autoQuoting(true);

By default CakePHP does not quote identifiers in generated SQL queries. The reason for this is identifier quoting has a few drawbacks:

Performance overhead - Quoting identifiers is much slower and complex than not doing it.

Not necessary in most cases - In non-legacy databases that follow CakePHP’s conventions there is no reason to quote identifiers.

If you are using a legacy schema that requires identifier quoting you can enable it using the quoteIdentifiers setting in your Configuration. You can also enable this feature at runtime:

When enabled, identifier quoting will cause additional query traversal that converts all identifiers into IdentifierExpression objects.

Cake php SQLSTATE[42000]: Syntax error or access violation: 1064

1 Answers