1. Введение

SQLite позволяет символы NUL (ASCII 0x00, Unicode \u0000) посреди значений последовательности, сохраненных в базе данных. Однако, использование NUL в последовательностях может привести к удивительным поведениям:

1. SQL-функция length() считает знаки только до NUL, исключая первый символ NUL.

2. SQL-функция quote() показывает знаки только до NUL, исключая первый символ NUL.

3. Команда .dump в CLI опускает первый символ NUL и весь последующий текст в выводе SQL, которую это производит. На самом деле CLI опускает все после первого символа NUL во всех контекстах.

Использование знаков NUL в последовательностях кода на SQL не рекомендуется.

2. Неожиданное поведение

Рассмотрите следующий SQL:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');
SELECT a, b, length(b) FROM t1;

Оператор SELECT выше выведет:

1,'abc',3

Здесь мы предполагаем, что CLI использует ".mode quote". Но если вы выполните:

SELECT * FROM t1 WHERE b='abc';

Тогда никакие строки не возвращены. SQLite знает, что колонка t1.b на самом деле содержит 7-символьную строку и 7-символьная строка 'abc'||char(0)||'xyz' не равна 3-символьной строке 'abc', и таким образом никакие строки не возвращены. Но пользователь мог бы быть легко смущен этим, потому что вывод CLI показывает, что у последовательности есть только 3 знака. Это походит на ошибку. Но это как SQLite работает.

3. Как сказать, есть ли у вас знаки NUL в последовательностях

Если вы CAST строку в BLOB, то всю длину последовательности показывают. Например:

SELECT a, CAST(b AS BLOB) FROM t1;

Дает этот результат:

1,X'6162630078797a'

В выводе BLOB можно ясно рассмотреть символ NUL как 4-й символ в 7-символьной строке.

Другой, более автоматизированный, способ сказать, содержит ли значение последовательности X включенные знаки NUL, должен использовать такое выражение, как это:

instr(X,char(0))

Если это выражение возвращает ненулевое значение N, то там существует вложенный NUL в позиции N. Таким образом, чтобы посчитать количество строк, которые содержат включенные знаки NUL:

SELECT count(*) FROM t1 WHERE instr(b,char(0))>0;

4. Удаление знаков NUL из текстового поля

Следующий пример показывает, как удалить NUL и весь текст, который следует за ним, из колонки таблицы. Таким образом, если у вас есть файл базы данных, который содержит включенный NUL, и требуется удалить его, управлять UPDATE, подобным следующему:

UPDATE t1 SET b=substr(b,1,instr(b,char(0))) WHERE instr(b,char(0));