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.)