Inserting into mysql database AES_ENCRYPT using PHP PDO

Question

I am trying to encrypt the data values when inserting into MYSQL using AES_ENCRYPT and CBC encryption mode:

SET @@session.block_encryption_mode = 'aes-256-cbc';

If I insert the data directly in SQL, it successfully inserts without any problem.

However, if I insert through PHP using prepared statements (PDO), the data doesn't insert into the database and I receive no error and the lastInsertID returned is 0.

If I remove the AES_ENCRYPT part, it inserts the data successfully.

Complete code:

$sql .= "SET @IV = RANDOM_BYTES(16);";
        $sql .= "INSERT INTO ". TABLE_NAME. " (record_created, name, dob, someinfo, iv) 
            VALUES (
                NOW(), 
                :name,
                AES_ENCRYPT(:dob, :key, @IV), 
                AES_ENCRYPT(:someinfo, :key, @IV),  
                @IV); ";



        try {
            $db = Employee::getConnection();
            $stmt = $db->prepare($sql);  

            $stmt->bindParam(':key', $key);
            $stmt->bindParam(':name', $employee->name);
            $stmt->bindParam(':dob', $employee->dob);
            $stmt->bindParam(':someinfo', $employee->someinfo);

            $stmt->execute();
            $employee->id = $db->lastInsertId();
            $db = null;
            echo json_encode($employee);

@u_mulder, my bad. I don't know how I missed it. I removed it, yet same problem. lastInsertId() is returning 0 and data is not being inserted. — input
– input, Commented Aug 2, 2015 at 17:39

Henry · Accepted Answer · 2015-08-02 17:45:01Z

3

you can't fire multiple Querys seperateted with ";" like in PhpMyAdmin. This is a way, you may prefer:

<?php
$aeskey = '4ldetn43t4aed0ho10smhd1l';

$sql = "INSERT INTO ". TABLE_NAME. " (record_created, name, dob, someinfo) 
            VALUES (
                NOW(), 
                :name',
                AES_ENCRYPT(:dob, '".$aeskey."'), 
                AES_ENCRYPT(:someinfo, '".$aeskey."'));";

$db = Employee::getConnection();
$stmt = $db->prepare($sql);  
$stmt->bindParam(':name', $employee->name);
$stmt->bindParam(':dob', $employee->dob);
$stmt->bindParam(':someinfo', $employee->someinfo);
$stmt->execute();
$employee->id = $db->lastInsertId();
$db = null;
echo json_encode($employee);

answered Aug 2, 2015 at 17:45

Henry

5974 silver badges12 bronze badges

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

2 Comments

input Over a year ago

thanks for your reply. I've tried inserting this way and it works but I want to use CBC encryption mode which requires IV as a parameter. How would I enter that?

I'm Root James Over a year ago

I'm doing the same thing, and it's possible that you must create another column in the table for the IV, and then generate a random IV for each item, and store it with the value. I assume this because I have not been able to use @iv with the PHP PDO yet.

Collectives™ on Stack Overflow

Inserting into mysql database AES_ENCRYPT using PHP PDO

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related