NUL Characters In Strings

Small. Fast. Reliable.<br>Choose any three.

Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase

Search

About<br>Documentation<br>Download<br>Support<br>Purchase

Search Documentation<br>Search Changelog

NUL Characters In Strings

1. Introduction

SQLite allows NUL characters (ASCII 0x00, Unicode \u0000) in the middle<br>of string values stored in the database. However, the use of NUL within<br>strings can lead to surprising behaviors:

The length() SQL function only counts characters up to and excluding<br>the first NUL.

The quote() SQL function only shows characters up to and excluding<br>the first NUL.

The .dump command in the CLI omits the first NUL character and all<br>subsequent text in the SQL output that it generates. In fact, the<br>CLI omits everything past the first NUL character in all contexts.

The use of NUL characters in SQL text strings is not recommended.

2. Unexpected Behavior

Consider the following SQL:

CREATE TABLE t1(<br>a INTEGER PRIMARY KEY,<br>b TEXT<br>);<br>INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');

SELECT a, b, length(b) FROM t1;

The SELECT statement above shows output of:

1,'abc',3

(Through this document, we assume that the CLI has ".mode quote" set.)<br>But if you run:

SELECT * FROM t1 WHERE b='abc';

Then no rows are returned. SQLite knows that the t1.b column actually<br>holds a 7-character string, and the 7-character string 'abc'||char(0)||'xyz'<br>is not equal to the 3-character string 'abc', and so no rows are returned.<br>But a user might be easily confused by this because the CLI output<br>seems to show that the string has only 3 characters. This seems like<br>a bug. But it is how SQLite works.

3. How To Tell If You Have NUL Characters In Your Strings

If you CAST a string into a BLOB, then the entire length of the<br>string is shown. For example:

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

Gives this result:

1,X'6162630078797a'

In the BLOB output, you can clearly see the NUL character as the 4th<br>character in the 7-character string.

Another, more automated, way<br>to tell if a string value X contains embedded NUL characters is to<br>use an expression like this:

instr(X,char(0))

If this expression returns a non-zero value N, then there exists an<br>embedded NUL at the N-th character position. Thus to count the number<br>of rows that contain embedded NUL characters:

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

4. Removing NUL Characters From A Text Field

The following example shows how to remove NUL character, and all text<br>that follows, from a column of a table. So if you have a database file<br>that contains embedded NULs and you would like to remove them, running<br>UPDATE statements similar to the following might help:

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

This page was last updated on 2022-05-23 22:21:54Z