Nul Characters in Strings in SQLite

NUL Characters In Strings

1. Introduction

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

The length() SQL function only counts characters up to and excluding the first NUL. The quote() SQL function only shows characters up to and excluding the first NUL. The .dump command in the CLI omits the first NUL character and all subsequent text in the SQL output that it generates. In fact, the 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( a INTEGER PRIMARY KEY, b TEXT ); 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

... continue reading