php - PDO、AES_ENCRYPT 和 MySQL

Question

这个问题已经有答案了:

When to use single quotes, double quotes, and backticks in MySQL (13 个回答)

已关闭 7 年前。

我正在使用准备好的语句通过使用 MySQL 函数 AES_ENCRYPT 的 PHP 脚本插入数据。问题是，它不会插入，我收到错误:

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

我尝试了很多方法都无济于事，我非常感谢任何指导或指导。

我的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);

}

再次非常感谢您的帮助。

编辑:固定代码

$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);

}

Answer 1

问题是 $key 没有被绑定(bind)，它是一个字符串，这会破坏查询。

选项是 a) 绑定(bind)它 b) 引用它(此选项使其他绑定(bind)毫无意义，因为值中的单引号仍然会破坏它)。

将查询更新为:

$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)");

以及绑定(bind)到:

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