PHP mcrypt and SQL Where for Encrypted Info?

Question

I Have a function where text is encrypted and decrypted. On every refresh the encryption is always different and the decrypted is always the same as the original string. I update to a sql database the encryption. I Can't Use a simple "SELECT * FROM mytable WHERE MyField = 'Myencryption';" because the 'Myencryption' will be different each time. How can I search in SQL an Mycrypt Encryption? Any Suggestions?

My Code is Below: ( I have a PDO SQL Class )

        // Encrypt Function
    private function encrypt($encrypt, $key){
        $encrypt = serialize($encrypt);
        $iv = mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_CBC), MCRYPT_DEV_URANDOM);
        $mac = hash_hmac('sha256', $encrypt, substr(bin2hex($key), -32));
        $passcrypt = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $encrypt.$mac, MCRYPT_MODE_CBC, $iv);
        $encoded = base64_encode($passcrypt).'|'.base64_encode($iv);
        return $encoded;
    }

    // Decrypt Function
    private function decrypt($decrypt, $key){
        $decrypt = explode('|', $decrypt.'|');
        $decoded = base64_decode($decrypt[0]);
        $iv = base64_decode($decrypt[1]);
        if(strlen($iv)!==mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_CBC)){ return false; }
        $decrypted = trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, $decoded, MCRYPT_MODE_CBC, $iv));
        $mac = substr($decrypted, -64);
        $decrypted = substr($decrypted, 0, -64);
        $calcmac = hash_hmac('sha256', $decrypted, substr(bin2hex($key), -32));
        if($calcmac!==$mac){ return false; }
        $decrypted = unserialize($decrypted);
        return $decrypted;
    } // End Decrypt


$this->db->query("SELECT * FROM `$this->main_db`.`$this->apps_tbl` WHERE `2` = ':db_name'");
        $this->db->bind(':db_name', $app_id);
        $row = $this->db->single();

That's not possible the whole idea behind encryption is that you can't decrypt it. Encryption is one way. You should use Hashing which is two ways. — Daan
– Daan, Commented Jan 29, 2015 at 13:55
@Daan You have that backwards. Hashing is one-way. Encryption is two-way. — ceejayoz
– ceejayoz, Commented Jan 29, 2015 at 13:55
@Daan Wrong way round. Encryption is two way, Hashing is one way. — Styphon
– Styphon, Commented Jan 29, 2015 at 13:55
@TVarcor Why are you changing the encryption key each time? To do this you would need to know the key used to encrypt the data (which is why the key is normally constant for all content) and then you encrypt the data before running the mysql query. — Styphon
– Styphon, Commented Jan 29, 2015 at 13:57
@Styphon The $key var doesn't change, I have the same primary key. Even though the encryption changes it always decrypted correctly — T Varcor
– T Varcor, Commented Jan 29, 2015 at 14:08

Animir · Accepted Answer · 2015-01-29 14:12:53Z

2

SELECT * FROM mytable WHERE 'text' = AES_DECRYPT(MyField, 'Your 256 key');

But if you have many rows in table or weak server, this way may be quite slow.

answered Jan 29, 2015 at 14:12

Animir

1,20411 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP mcrypt and SQL Where for Encrypted Info?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related