Error with AES_DECRYPT Mysql character and Codeigniter

Asked

Viewed 87 times

0

Good afternoon guys. Would anyone like to help me find this mistake? I created a login area on the site, but I’m having trouble when the user uses in his password the exclamation character "!". Example of password: 123456A! As soon as he tries to log in from an error in Mysql. Follow the code:

Login - Model

public function login($data)
    {
        $this->db->where('usuario', $data['usuario']);
        $this->db->where($this->db->escape($data['senha']), "CAST(AES_DECRYPT(senha,'".$this->config->item('encryption_key')."') AS CHAR(255))",FALSE);
        $this->db->where('status', '1');
        $query = $this->db->get('usuarios')->result();
        return $query;
    }

Error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CAST(AES_DECRYPT(senha,'0ThearAOdAf9amHJ8mSi4Pc7adDvwUFq') AS CHAR(255)) AND `st' at line 4

SELECT * FROM `teste_usuarios` WHERE `usuario` = 'usuario1' AND '123456789!@' CAST(AES_DECRYPT(senha,'iz2GrBvs34UArNWxxUoELxoqEOlTTsA8') AS CHAR(255)) AND `status` = '1'

Filename: C:/xampp/htdocs/novosite/system/database/DB_driver.php

Line Number: 691

If anyone can help me. I appreciate it. Hug to all.

php mysql codeigniter

  • The method $this->db->where() generally expects as first parameter the column name in the table. https://www.codeigniter.com/userguide3/database/query_builder.html#Looking-for-specific-data

  • Your query is not being mounted correctly. Note that the password column is missing: ... \user` = 'user 1' AND '123456789!@' ...`.

  • Good afternoon @Pauloimon thanks for the return. So I don’t know why you’re not getting it right. The funny thing is that it only goes wrong when I put the exclamation character "!" in the password.

  • So, I believe it’s the parameters you’re going through in the method where() of Query Builder. You could try it like this: $this->db->where('senha', $data['senha']); and in your Controller you decrypt the $data['senha'] before going to Model. For passwords the Codeigniter doc itself recommends using the extension Password Hashing PHP instead of encryption lib: Passwords must be hashed instead, and you should do that via PHP’s own Password Hashing extension. I hope I’ve helped.

  • 1

    Good afternoon @Pauloimon So I did what you and lcssanches recommended using password_hash and password_veriy and it worked. I took the verification of the password of the Model and played for the control. Only one difference there that you mentioned that I did not do was in the Model pass this here: $this->db->where('senha', $data['senha']); in the model I just passed the verification if the user exists and in the password decryption control. I believe that’s it. Thanks for the help.

  • Good evening @Saul. I get it. I’m glad it worked out!

Show 1 more comment

1 answer

2


Do not control passwords like this by encrypting and to check decrypting.

In 99% of cases you should use password_hash and password_verify.

It would work that way:

function do_login($user, $plain_password){
    $this->db->select('password');
    $this->db->where('user', $user);
    $user_obj = $this->db->get('users')->row;
    if(!$user_obj) return false;
    $hash_password = $user_obj->password;
    return password_verify($plain_password, $hash_password);
}

function create_login($user, $plain_password){
    $hash_password = password_hash($plain_password, PASSWORD_DEFAULT);
    $this->db->set('user', $user);
    $this->db->set('password', $hash_password);
    $this->db->insert('users');
    return true;
}

Browser other questions tagged php mysql codeigniter

You are not signed in. Login or sign up in order to post.