SQLite Forum

TEXT values can contain arbitrary binary data
Login
No, it is definitely not true of most databases.

E.g., in PostgreSQL, you'll receive this error:

```
postgres=# CREATE TABLE foo ( some_text text NOT NULL );
CREATE TABLE

postgres=# INSERT INTO foo VALUES (E'\xff' || 'test');
ERROR:  invalid byte sequence for encoding "UTF8": 0xff

postgres=# INSERT INTO foo VALUES (E'\x61' || 'test');
INSERT 0 1
postgres=# select * from foo;
 some_text 
-----------
 atest
(1 row)
```


MariaDB, which I'm using for MySQL here, but I believe MySQL proper behaves similarly,

```
MariaDB [(none)]> CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE mydatabase;
Database changed
MariaDB [mydatabase]> CREATE TABLE foo ( some_txt text NOT NULL );
Query OK, 0 rows affected (0.125 sec)

MariaDB [mydatabase]> INSERT INTO foo VALUES (X'ff');
ERROR 1366 (22007): Incorrect string value: '\xFF' for column `mydatabase`.`foo`.`some_txt` at row 1
MariaDB [mydatabase]> INSERT INTO foo VALUES (X'31');
Query OK, 1 row affected (0.098 sec)

MariaDB [mydatabase]> select * from foo;
+----------+
| some_txt |
+----------+
| 1        |
+----------+
1 row in set (0.000 sec)
```

Now, MySQL has some legacy baggage; last I checked, the default encoding is still `latin1`, and in that encoding, any byte sequence is a valid string. (There are no malformed sequences of bytes, which isn't true in UTF-8.)

Your exact example, in MariaDB:

```
MariaDB [mydatabase]> CREATE TABLE IF NOT EXISTS test_table (
    ->   some_text text NOT NULL
    -> );
Query OK, 0 rows affected (0.032 sec)

MariaDB [mydatabase]> INSERT INTO test_table VALUES (CONCAT(0xff, 'bork'));
ERROR 1366 (22007): Incorrect string value: '\xFFbork' for column `mydatabase`.`test_table`.`some_text` at row 1
```

But again, the database here is configured to be UTF-8. Being configurable to a different encoding is different from the observed SQLite behavior, too: MySQL/MariaDB will encoding/decode appropriately, but it's still something it is very much aware of, in order to do so. (It's important for certain operations, like string comparison.)