1

I am wondering: assuming everything is set up properly, meaning a column with charset utf8mb4 and a client connecting to the database setting the connection encoding to utf8mb4, is there any way in which said client could SELECT data which contains invalid UTF-8 byte sequences? Is there any way to insert data into a utf8mb4 column which is not in fact valid UTF-8?

Put another way: is it reasonable to assume that SELECTing any non-binary column over a client connection set to utf8mb4 should always return valid UTF-8 byte sequences?

(Let's assume plain SELECT * statements without any CAST shenanigans or such.)

Improve this question

2 Answers 2

Reset to default
2
+50

I don't think so. But I have come close:

mysql> CREATE TABLE invalid (c VARBINARY(22));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO invalid (c) VALUES (UNHEX('65c3c3c366'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c) FROM invalid;
+------------+
| HEX(c)     |
+------------+
| 65C3C3C366 |
+------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE invalid MODIFY COLUMN c VARCHAR(22) CHARACTER SET utf8mb4;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3\xC3\xC3f' for column 'c' at row 1 |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

By starting with BINARY, then converting to CHAR, I can get invalid data into the column. However, the ALTER gives a warning when I try to convert it.

Note in the final SELECT, the data is truncated. The truncation happened in the ALTER, not the SELECT, where your Question wanted it to happen. This can be verified by converting back to BINARY:

mysql> ALTER TABLE invalid MODIFY COLUMN c VARBINARY(22);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

If you have seen the problem happen, please provide details; perhaps I can dissect the problem.

Improve this answer
2
  • Well, the question originated here: stackoverflow.com/q/48524695/476; I'm skeptical about the concrete circumstances the OP describes and suspect it's an XY problem, and it's been impossible to get details out of the OP.
    – deceze
    Feb 20, 2018 at 8:59
  • Essentially you're demonstrating that a binary column can of course contain any binary gibberish you want, but as soon as you try to put that into an actual utf8mb4 column the data will be normalised to the extent possible and be valid UTF-8 data henceforth. That's what I expected.
    – deceze
    Feb 20, 2018 at 9:43
0

Ok, you´re welcome to help me to understand the problem... In the reffered thread the data comes from a html form field und was inserted in a prepared field. There was no data conversion after the insert! First of all, why it happens: A Javascript tries to interpret the string U+1F601 as expression for the unicode character of the "beaming face with smiling eyes". I must left us without details but the bug was the appendix of an 'A' so U+1F601A was interpreted. After the part of the code that tests the char for a surrogate pair; "\udf98\udc1a" was left. Now see the code below, it creates a fresh empty table, inserts the value of the textarea.

<html>
<head>
    <meta charset="utf-8" />
</head>
<body>
<pre><?php
    ini_set('mbstring.internal_encoding','UTF-8');
    ini_set('date.timezone', 'Europe/Berlin');
    ini_set( 'default_charset', 'UTF-8' );
    $dbConnection = new mysqli('host', 'user', 'password', 'dbname');
    $dbConnection->query("SET NAMES utf8");
    if($_POST) {
        $sql = "CREATE TABLE IF NOT EXISTS `test` (
                    `id` INT(11) NOT NULL AUTO_INCREMENT,
                    `message` VARCHAR(1920) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
                    PRIMARY KEY(`id`)
                )
                COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;";
        $dbConnection->query($sql);
        $char = $dbConnection->real_escape_string($_POST['hallo']);
        $sql = "SELECT HEX('".$char."')";
        echo $sql."\n";
        $result = $dbConnection->query($sql);
        $row = $result->fetch_row();
        echo $row[0]."\n";

        $sql = "INSERT INTO test VALUES (0, '".$char."');";
        $dbConnection->query($sql);

        $sql = "SELECT message FROM test ORDER BY id DESC LIMIT 1";
        echo $sql."\n";
        $result = $dbConnection->query($sql);
        $row = $result->fetch_row();
        echo $row[0]."\n";

        if(!mb_check_encoding($row[0], "UTF-8")) {
            echo "Php says its not valid\n";
        }
    }
?></pre>
<form method="post" action="test.php" accept-charset="utf-8">
    <textarea id="test" name="hallo" id="test"></textarea>
    <input type="submit">
</form>
<script type="text/javascript">
    var character = "\udf98\udc1a";
    var expression = /\\u([A-Fa-f0-9]{4}?)/gi;
    character = character.replace(expression, function(match, grp) {
        console.log("grp is " + grp);
        return String.fromCharCode(parseInt(grp, 16));
    });

    console.log(unescape(character));
    document.getElementById('test').value = unescape(character);

    var first = "\\u" + String.fromCharCode(parseInt("df98", 16));
    var second = "\\u" + String.fromCharCode(parseInt("dc1a", 16));
    var character = first + second;
    var third = unescape(character);

    console.log(first);
    console.log(second);
    console.log(third);
</script>
</body>
</html>
Improve this answer
1
  • 2
    Seems like you need to merge your two accounts. I think you're trying to clarify here, in which case you can also just edit your original question. Answers are only for answers, not replying, here on SE.
    – LowlyDBA - John M
    Feb 27, 2018 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.