PDO cutting off strings at a UTF-8 character

Question

I am using PHP 5.5 and when I attempt to insert a UTF-8 character in the MySQL database PDO cuts it off at the first non-ASCII character. I have set my connection to be: ``` (DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8', DB_USER, DB_PASS, array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING)) ``` I have tried the SET NAMES that everyone posts, but that doesn’t work either because the problem is NOT on the MySQL side of things. When I do an insert through phpMyAdmin and directly from the MySQL console, it works! When I select the accented string with PDO, it works! The problem is only on `INSERT` and `UPDATE` using PDO specifically! Here is the SQL of the table. It is all in UTF-8 but maybe someone knows of a conflict between a setting and PDO ``` CREATE TABLE IF NOT EXISTS `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_lang` int(11) NOT NULL DEFAULT '2', `id_tgroup_cat` int(11) NOT NULL, `fieldfor` int(11) NOT NULL, `c…

Accepted Answer

This is the key clue to me:

Where $text = "référence" (only saves the letter R in the database but without accents it saves everything) and $colors is an empty string for test purposes and $id is 2.

Sounds like it is a UTF-8 encoding issue. While the database is UTF-8 the whole chain from the code to the database—including the connection—should be UTF-8 clean.

How exactly does `$this->db->prepare` relate to the PHP connection to MySQL? A bit unclear from the code you have shown. But based on what you are showing, perhaps adjusting your query like this would help:

```
$query = $this->db->prepare("SET collation_connection = utf8_bin;
                             SET NAMES utf8;
                             UPDATE mytable
                                SET text = ?,
                                    colors = ?
                                WHERE id = ?");
```

Or maybe this:

```
$this->db->exec("SET collation_connection = utf8_bin; SET NAMES utf8;");
$query = $this->db->prepare("UPDATE mytable
                                SET text = ?,
                                    colors = ?
                                WHERE id = ?");
```

Note my forced-in addition of `SET collation_connection = utf8_bin;` as well as `SET NAMES utf8;`

In general you need to make sure your entire chain from the connection, to the database, to the tables is all UTF8 clean. I have a detailed answer to a similar question here.

But in your case, check the actual MySQL server `my.cnf` file. The following would set the whole chain to UTF-8:

```
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
```

EDIT: And since the original poster indicates the data is coming from an HTML5 form, I also think checking the BOM (byte order mark) for the actual HTML5 file itself would help as well. It should be set to UTF8. More details on what a BOM is are over here. Specifically the accepted answer from Martin Code which explains:

The UTF-8 BOM is a sequence of bytes (EF BB BF) that allows the reader to identify the file as an UTF-8 file.

PDO cutting off strings at a UTF-8 character

Solution

Problem

Related problems