Mysql does not accept \\xD1 even though I use utf8mb4-CodePudding

MySQL returns the following error when I want to execute a statement:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD1' for column 'last_update' at row 1

Here is the code to insert the value:

$connection = DataBase\MySQLConnect::getConnection(self::$dbName);
$query  = "INSERT "
        . " into `answer` "
        . " ( "
        . " `reference`, "
        . " `language`, "
        . " `date`, "
        . " `info`, "
        . " `body`, "
        . " `url_html`, "
        . " `url_word`, "
        . " `url_pdf`, "
        . " `last_update` "
        . " ) "
        . " VALUE "
        . " ("
        . " :reference, "
        . " :language, "
        . " :date, "
        . " :info, "
        . " :body, "
        . " :url_html, "
        . " :url_word, "
        . " :url_pdf, "
        . " :last_update "
        . " )";
$stmt = DataBase\MySQLConnect::prepare($query, $connection);
DataBase\MySQLConnect::bindParam($stmt, ':reference', $this->fields['reference'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':language', $this->fields['language'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':date', $this->fields['date'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':info', $this->fields['info'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':body', $this->fields['body'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_html', $this->fields['url_html'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_word', $this->fields['url_word'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':url_pdf', $this->fields['url_pdf'], 'str');
DataBase\MySQLConnect::bindParam($stmt, ':last_update', $this->fields['last_update'], 'str');

$boolContinue = DataBase\MySQLConnect::execute($stmt);

All the static functions use PDO. I just added log writing.

The connection is created using PHP PDO and I bind the value. I also use utf8mb4 charset both in the connection and table, and collation utf8mb4_0900_ai_ci in the table.

I tried with utf8mb4_unicode_ci but the error remains the same.

Could someone help me on this topic, please? I have read a lot of discussions about similar cases but I did not find any efficient solution. Thanks in advance!

CodePudding user response：

I finally found the solution and it was not a problem of character encoding.

In fact, to avoid Mysql errors when inserting a too long value, a function shortens them if necessary. And this was the case for the Bulgarian value because PHP htmlspecialchar made it much longer than the column size.

As a result, the encoding was inconsistent and could not be inserted.

Anyway, thank you very much for your reactivity and your help!

CodePudding user response：

Try executing following query before executing insert query. I remember having similar issue which was resolved by running the following query after connection initializing;

SET NAMES utf8mb4;