PDO, AES_ENCRYPT, and MySQL

Question

I am using a prepared statement to instert data through a PHP script that using the MySQL function AES_ENCRYPT. The problem is, it will not insert, I get an error:

Fatal error: Uncaught exception 'PDOException' with message '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 ''Kelly', '33-04-33', 'female', 'true', 'false', 'false', 'false', 'Rural Route O' at line 1' in G:\PleskVhosts\insurancemidam.com\httpdocs\test\includes.php:186 Stack trace: #0 G:\PleskVhosts\insurancemidam.com\httpdocs\test\includes.php(186): PDOStatement->execute() #1 G:\PleskVhosts\insurancemidam.com\httpdocs\test\confirmation.php(191): dataBaseAccess->insertChildren('11', 'ca25bff56b00791...', 'Kelly', '33-04-33', 'female', '44444444', 'false', 'false', 'false', 'true', 'Rural Route One...') #2 {main} thrown in G:\PleskVhosts\insurancemidam.com\httpdocs\test\includes.php on line 186

I have tried so many things to no avail, I would genuinely appreciate any guidance or direction.

My PHP

public function insertChildren($employeeID, $key, $childName, $childBirth, $childGender, $childSSN, $isStep, $isFoster, $isStudent, $isHandicap, $address)  {

$conn = $this->connect('insurance');

$insertChildren = $conn->prepare("INSERT INTO dependent_children (emp_id, ssn, name, dob, gender, handicap, student, foster, step, address) VALUES (:emp_id, AES_ENCRYPT(:ssn, $key), :name, :dob, :gender, :handicap, :student, :foster, :step, :address)");

echo "<h2>$childGender</h2>";

$insertChildren->bindParam(":emp_id", $employeeID, PDO::PARAM_INT);
$insertChildren->bindParam(":name", $childName, PDO::PARAM_STR);
$insertChildren->bindParam(':dob', $childBirth, PDO::PARAM_STR);
$insertChildren->bindParam(':gender', $childGender, PDO::PARAM_STR);
$insertChildren->bindParam(':ssn', $childSSN, PDO::PARAM_LOB);
$insertChildren->bindParam(':handicap', $isHandicap, PDO::PARAM_STR);
$insertChildren->bindParam(':student', $isStudent, PDO::PARAM_STR);
$insertChildren->bindParam(':foster', $isFoster, PDO::PARAM_STR);
$insertChildren->bindParam(':step', $isStep, PDO::PARAM_STR);
$insertChildren->bindParam(':address', $address, PDO::PARAM_STR);

$insertChildren->execute();
echo var_dump($insertChildren);

}

Again, thank you so much for any help.

EDIT: Fixed Code

$insertChildren = $conn->prepare('INSERT INTO dependent_children (emp_id, ssn, name, dob, gender, handicap, student, foster, step, address) VALUES (:emp_id, AES_ENCRYPT(:ssn, :key), :name, :dob, :gender, :handicap, :student, :foster, :step, :address)');

echo "<h2>$childGender</h2>";

$insertChildren->bindParam(":emp_id", $employeeID, PDO::PARAM_INT);
$insertChildren->bindParam(":name", $childName, PDO::PARAM_STR);
$insertChildren->bindParam(':dob', $childBirth, PDO::PARAM_STR);
$insertChildren->bindParam(':gender', $childGender, PDO::PARAM_STR);
$insertChildren->bindParam(':key', $key, PDO::PARAM_LOB);
$insertChildren->bindParam(':ssn', $childSSN, PDO::PARAM_LOB);
$insertChildren->bindParam(':handicap', $isHandicap, PDO::PARAM_STR);
$insertChildren->bindParam(':student', $isStudent, PDO::PARAM_STR);
$insertChildren->bindParam(':foster', $isFoster, PDO::PARAM_STR);
$insertChildren->bindParam(':step', $isStep, PDO::PARAM_STR);
$insertChildren->bindParam(':address', $address, PDO::PARAM_STR);

$insertChildren->execute();
echo var_dump($insertChildren);

}

as @chris85 asked, since you bind all params why isn't `$key` as well? Can you try to bind `$key` and respond? — , Jun 02 '16 at 15:35
Thanks to both of the comments, I was able to fix it. Thanks so so much! I will edit the post so perhaps others can learn from it. — KellyM, Jun 02 '16 at 15:39
I'll post that as an answer so the question doesn't go unanswered. — chris85, Jun 02 '16 at 15:50

score 2 · Accepted Answer · answered Jun 02 '16 at 15:57

The issue is that $key is not being bound and it is a string, that breaks the query.

Options are a) bind it b) quote it (this options makes the other binds pointless because a single quote in the value will still break this).

Update the query to:

$insertChildren = $conn->prepare("INSERT INTO dependent_children (emp_id, ssn, name, dob, gender, handicap, student, foster, step, address) VALUES (:emp_id, AES_ENCRYPT(:ssn, :key), :name, :dob, :gender, :handicap, :student, :foster, :step, :address)");

and the binding to:

$insertChildren->bindParam(':key', $key, PDO::PARAM_STR);

PDO, AES_ENCRYPT, and MySQL

1 Answers1