0

Using a prepared statement in PHP...

$sql = "insert into `customers` (`name`, `email`) values (?, AES_ENCRYPT(?,?))";
$mysqli = $oDB->connect(); // see below for $oDB class and connect function
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sss", $name, $email, $key);
$stmt->execute();

... I'm trying to encrypt the email so it's stored a little more securely (or at least so that dumping the contents of the customers table doesn't show the email in plain text)

($key is a randomly generated string of 32 characters, if that's relevant)

The database and tables use utf8mb4_unicode_520_ci coding. And I've tried setting the connection charset before executing the statement...

class oDB {
    public function connect() {
        $mysqli = new mysqli($this->db['host'], $this->db['user'], $this->db['pass'], $this->db['name']);
        $mysqli->set_charset("utf8mb4");
        return $mysqli;
};

Upon executing the sql the program returns the error Incorrect string value: '\x99\xC1\xE8\x89u\'

Looking up this code I get solutions about setting the charset on the mysqli connection before sending the sql. But somehow that doesn't work.

Any ideas on how to resolve the incorrect string value error?

Denniz
  • 175
  • 1
  • 15
  • 1
    Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) – Jens Sep 18 '18 at 06:58
  • 1
    @Jens That wouldn't cause this error. – Barmar Sep 18 '18 at 07:00
  • @Jens, corrected the query, I used single-quotes because markdown was messing up the query. It's correctly shown now. – Denniz Sep 18 '18 at 07:01
  • Well, that string doesn't appear to be valid UTF-8… – deceze Sep 18 '18 at 07:03
  • @deceze, care to elaborate on how I can make it valid UTF-8? – Denniz Sep 18 '18 at 07:50
  • 1
    Care to elaborate where that string comes from in the first place and why it's not UTF-8? – deceze Sep 18 '18 at 07:55
  • `$email` comes from a form and is sanitized as string. `filter_input(INPUT_POST, 'email', FILTER_SANITIZE_STRING);` Or are you referring to another string? – Denniz Sep 18 '18 at 08:14
  • After setting the collation for the field `email` to `latin1_general_ci` the encrypt works. – Denniz Sep 18 '18 at 08:18

0 Answers0