Repository navigation
Identifiers with non-ASCII characters are rejected ("The SQLite driver only supports ASCII characters in identifiers") #559
Description
Description
MySQL allows non-ASCII characters in identifiers, but the driver rejects them:
CREATE TABLE `t_faroese` (`id` int NOT NULL, `tíð` time NOT NULL, PRIMARY KEY (`id`));
-- The SQLite driver only supports ASCII characters in identifiers.
SELECT 123 AS `ńôñ`;
-- The SQLite driver only supports ASCII characters in identifiers.We hit this importing Faroese WordPress sites. A radio schedule plugin has a table with the columns tíð_fra, framleiðari, and lýsing. Importers such as wp sqlite import stop at the first failing statement, so every table after it is lost.
Reproduced on 3.1.1 and trunk (b5dc0cc), PHP 8.4.27, SQLite 3.45.2.
Cause
The restriction is deliberate. Two checks enforce it:
WP_MySQL_On_SQLite::translate(),pureIdentifiercaseWP_SQLite_Information_Schema_Builder::get_value()
Both cite https://sqlite.org/faq.html#q18: SQLite only folds the case of ASCII letters, so it can't match tíð and TÍÐ case-insensitively, as MySQL does.
The lexer already accepts these characters, and the rest of the driver works with them once the checks are gone: CREATE TABLE, indexes, defaults, INSERT/UPDATE/SELECT, ALTER TABLE … CHANGE/ADD, SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX, and information_schema queries.
Suggested fix
Remove both checks, and accept that non-ASCII letters must be used in their declared case:
- ASCII letters stay case-insensitive, also in non-ASCII identifiers:
tíð_FRAmatchestíð_fra. - Non-ASCII letters must match the declared case:
TÍÐ_FRAdoesn't matchtíð_fra. This fails with a clear error (no such column: TÍÐ_FRA), never with wrong results.
Rejecting every non-ASCII identifier is a much bigger limitation than requiring the declared case: code rarely changes the case of a non-ASCII letter between declaring and using a name. Full emulation would need Unicode case folding wherever SQLite and the information schema compare identifiers.
The change (we've run it, patched into 3.1.0, on local copies of our client sites since October 18)
diff --git a/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php b/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
index 57d72f7..5b70cb9 100644
--- a/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
+++ b/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
@@ -4306,21 +4306,17 @@ class WP_MySQL_On_SQLite extends PDO {
case 'identifierKeyword':
return '`' . $this->translate( $node->get_first_child() ) . '`';
case 'pureIdentifier':
- $value = $this->translate_pure_identifier( $node );
-
/*
- * At the moment, we only support ASCII bytes in all identifiers.
- * This is because SQLite doesn't support case-insensitive Unicode
- * character matching: https://sqlite.org/faq.html#q18
+ * MySQL allows non-ASCII characters (U+0080-U+FFFF) in identifiers.
+ *
+ * Note that SQLite only folds the case of ASCII letters when it
+ * matches identifiers (https://sqlite.org/faq.html#q18). Therefore,
+ * identifiers with non-ASCII letters must be referenced using the
+ * same letter case as they were declared with (e.g., "tíð" works,
+ * while "TÍÐ" doesn't). In practice, they are nearly always used
+ * consistently, and rejecting them altogether is far more limiting.
*/
- for ( $i = 0; $i < strlen( $value ); $i++ ) {
- if ( ord( $value[ $i ] ) > 127 ) {
- throw $this->new_driver_exception(
- 'The SQLite driver only supports ASCII characters in identifiers.'
- );
- }
- }
- return $value;
+ return $this->translate_pure_identifier( $node );
case 'textStringLiteral':
return $this->translate_string_literal( $node );
case 'dataType':
diff --git a/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-information-schema-builder.php b/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-information-schema-builder.php
index 9cd5c3d..2bebcc6 100644
--- a/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-information-schema-builder.php
+++ b/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-information-schema-builder.php
@@ -3101,19 +3101,6 @@ class WP_SQLite_Information_Schema_Builder {
foreach ( $node->get_children() as $child ) {
if ( $child instanceof WP_Parser_Node ) {
$value = $this->get_value( $child );
-
- /*
- * At the moment, we only support ASCII bytes in all identifiers.
- * This is because SQLite doesn't support case-insensitive Unicode
- * character matching: https://sqlite.org/faq.html#q18
- */
- if ( 'pureIdentifier' === $child->rule_name ) {
- for ( $i = 0; $i < strlen( $value ); $i++ ) {
- if ( ord( $value[ $i ] ) > 127 ) {
- throw new Exception( 'The SQLite driver only supports ASCII characters in identifiers.' );
- }
- }
- }
} else {
$value = $child->get_value();
}Tested with the mysql-on-sqlite suite: the three test*MustBeAscii tests turned into tests that assert success, plus tests for a real-world table definition (non-ASCII table, column, and index names, a non-ASCII default, DML, SHOW CREATE TABLE, ALTER TABLE, SHOW COLUMNS), unquoted non-ASCII identifiers, and the case limitation. The full suite passes, and check-cs is clean. I'm happy to share the tests.
Activity