On Friday, 10 January, 2020 10:50, Dominique Devienne <ddevie...@gmail.com>:
>On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp <d...@sqlite.org> wrote: >> length() on a BLOB should show the number of bytes in the BLOB. >> length() on a string should show the number of *characters* (not >> bytes) in the string up through but not including the first >> zero-character. It is possible to have additional content after the >> first zero-character in a string, which length() will not tell you >> about. > There's no way at all, to know the length of a text column with embedded > NULLs? C-Strings cannot have embedded nulls. The very definition of a C-String is "a sequence of non-zero characters followed by a zero character". So while you can store and retrieve invalid C-Strings in the database (as in use BIND and COLUMN_TEXT), "things" (meaning software) which expects a C-String to be a C-String will be confused by such improper usage of a C String, and "things" which expect "text" fields to contain properly encoded C-Strings are likely to have brain-seizures. You can, of course, cast the column as a blob (which IS allowed to have embedded nulls and DOES NOT have to have valid text encoding), and get the length of that bag-o-bytes (in bytes): sqlite> select * from copied; 85960605|,|0DE19F8AA100D2962FF22C60B3FA8940 85960465|,|A44615408E8B3E48A684C60CA2967031 85960451|,|33C8804354984071A740788AD308B279 sqlite> select typeof(id_local),typeof(lutfullstring),typeof(luthash) from copied; integer|text|text integer|text|text integer|text|text sqlite> select length(cast(lutfullstring as blob)) from copied; 194238 183050 193908 The first select is indeed selecting the ENTIRE string that has been asked for. It is then using printf "%s" to print it because it is SUPPOSED TO BE a null terminated string. It is doing exactly what it has been told to do. Similarly, the function LENGTH() on what is supposed to be a NULL terminated string returns the number of characters up to but not including the NULL terminator. The real problem here is that a BLOB has been stored as if it were TEXT. This is a failure of the application to properly sanitize its input. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users