5
  1. Can strings in SQLite 3 include NUL characters?
  2. If the answer to 1 is "yes", how can they be written in SQL queries? SQLite doesn't seem to have chr or char functions.
Alexey Romanov
  • 167,066
  • 35
  • 309
  • 487
  • It's risky to use strings containing NUL characters I don't know if they'll break in SQLite but they risk being cropped in other tools that process nul terminated strings. – Alex Jasmin Oct 29 '10 at 05:59
  • 1
    I wonder if using a blob miht be more appropriate. Problem is you can't just use a standard INSERT, you gotta bind that, I think. I know too little about that to go further, but I'm hopeful for an answer too. – MPelletier Oct 29 '10 at 13:47
  • I am using blobs at the moment, but it would be nice to know if strings can be used as well. – Alexey Romanov Oct 29 '10 at 13:51
  • Out of curiosity, why would you need NULs in a string? NULs end strings, using them inside strings is just asking for trouble, IMHO. – MPelletier Oct 29 '10 at 15:52

2 Answers2

3

In general, no - SQLite internally is not 8-bit clean, probably due to its Tcl heritage. While NULs do not cause corruption problems, SQLite typically stops processing strings at the first embedded NUL character.

This is true even for operators such as GLOB. For instance, you cannot match a BLOB column with GLOB when you have embedded NUL characters, e.g. this

select * from table where blobcol glob x'00022a';

will only match empty blob values: While you can use literal BLOB syntax (i.e. x'hexdigits') and use the resulting values where strings are used, SQLite typically only uses the part before the first NUL.

At least,. this is the state of affairs up to including SQLite 3.26.0.

Note that SQLite also has a BLOB type which can store embedded NULs without any issues, but there is very little functionality in SQLite to use them, and they are often harder to use from SQL interface libraries.

They also silently convert to strings in many contexts, at which point the embedded NULs start causing issues again.

Remember Monica
  • 3,897
  • 1
  • 24
  • 31
1

Not sure from which version onwards it is supported, but you can do it:

create table foo (bar data);
insert into foo(bar) values (x'001122334400ff');
select length(bar),hex(bar),bar from foo;
My Other Me
  • 5,007
  • 6
  • 41
  • 48
  • That's a blob (mentioned in the comments already), not a string. – Alexey Romanov Oct 29 '10 at 15:14
  • In sqlite any blob data will always end up being a blob, regardless of the column declaration (and implied affinity). See the example under "Column Affinity Behavior Example" at http://www.sqlite.org/datatype3.html that demonstrates this. – My Other Me Nov 01 '10 at 10:26