Using AES_ENCRYPT in Prepared Statement with Bind Variables

Question

Wanting to encrypt a particular data variable but keep getting "PHP Fatal error: Call to undefined function AES_ENCRYPT()..."

Research has lead me to a hint that it's using PHP instead of MySQL?

$key="xyz";

$stmt = mysqli_prepare($mysqli, "INSERT INTO details (FirstName, LastName, EncryptThis) VALUES (?,?,?)");

if ($stmt === false) {
        trigger_error('Statement failed! ' . htmlspecialchars(mysqli_error($mysqli)), E_USER_ERROR);
    }                      

$bind = mysqli_stmt_bind_param($stmt, "sss", $FirstName, $LastName,  AES_ENCRYPT('$EncryptThis','$key'));

        if ($bind === false) {
        trigger_error('Bind param failed!', E_USER_ERROR);
    }                  

$exec = mysqli_stmt_execute($stmt);

Am using varbinary in the DB...

Have tried various uses of

AES_ENCRYPT('$EncryptThis','$key')

EG

AES_ENCRYPT($EncryptThis,$key)

etc etc

Possible duplicate of [How to use AES\_ENCRYPT and AES\_DECRYPT in mysql](http://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql) — rjdown, Jul 10 '16 at 01:43

spencer7593 · Accepted Answer · 2016-07-10T01:53:28.630

3

MySQL is expecting values to be be passed as bind parameters. Not names of functions or other SQL expressions. Just values.

If you want to invoke the MySQL AES_ENCRYPT function, that needs to be appear as part of the SQL text (the string prepared as a SQL statement). The name of the function can't be passed as a part of a bind parameter.

Like this:

 "INSERT ... VALUES ( ? , ? , AES_ENCRYPT( ? , ? ) )" 

 mysqli_stmt_bind_param($stmt, "ssss", $FirstName, $LastName, $EncryptThis, $key);

edited Jul 10 '16 at 01:53

answered Jul 10 '16 at 01:50

spencer7593

106,611
15
112
140

Have updated - no PHP Fatal error, except now a blank value shown in DB column.. No errors in error_log. I did not add an extra column in the DB for $key? Should INSERT include the key? – Fraser4655 Jul 10 '16 at 02:06
No - all good. My poor use of quotes. Thank you @spencer7593 – Fraser4655 Jul 10 '16 at 10:16

Using AES_ENCRYPT in Prepared Statement with Bind Variables

1 Answers1