Converting to PDO with AES_ENCRYPT insert fails

Converting mysqli procedural to PDO style and everything works except one INSERT INTO statement, and I can’t see why it fails.

Below is the bit of the script where it fails (I have changed it to use javascript alerts when tracking it down.)

$sql = "INSERT INTO coachdetails (firstname, lastname, StreetAddress, postcode, gender, dob, Village, Town, nat_memb_no) VALUES((AES_ENCRYPT( ?, ?), AES_ENCRYPT(?, ?), AES_ENCRYPT(?, ?), AES_ENCRYPT(?,?),  ?, ?, ?, ?, ? )";
$stmt = $conn->prepare($sql);
if ($stmt) {
    if ($stmt->bind_param('ssssssssssssi', $firstname, $k1, $lastname, $k1, $address_1, $k2, $postcode, $k2, $gender, $dob, $town_1, $town_2, $nat_memb_no)) {
        if ($stmt->execute()) {
            $ok = true;
            mysqli_stmt_close($stmt);
            echo '<script type="text/javascript">alert("Record added"); </script>';
        } else {
            echo '<script type="text/javascript">alert("Record NOT added EXECUTE"); </script>';
            die(mysqli_error($conn));
        }
    } else {
        echo '<script type="text/javascript">alert("Record NOT added BIND "); </script>';
        die(mysqli_error($conn));
    }
} else {
    echo '<script type="text/javascript">alert("Rcord NOT added STMT" ); </script>';
    die(mysqli_error($conn));
}

It always fails at the prepare statement line

There are SELECT FROM , DELETE and UPDATE statements using AES_ENCRYPT or AES_DECRYPT within the same module that have been converted to PDO and work correctly.

Many thanks

What error do you get?

The error being displayed on screen (after the Rcord NOT added STMT alert is:
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 ‘’ at line 1

I’m using WAMP with MySQL 5.7.31 and PHP 7.3.21

VALUES((AES_ENCRYPT(

You have unbalanced braces here; remove the first brace and give it another go.

An easy way to spot things like this is to split your queries over several lines and add tabs when opening braces, the same way you do with other code.

1 Like

Thanks - it is amazing how long I have been staring at that statement and not seen it!

Sponsor our Newsletter | Privacy Policy | Terms of Service