The cli truncates values containing null chars

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

The cli truncates values containing null chars

Father Chrysostomos
While the SQLite database itself supports embedded nulls without any problems, the command-line utility truncates values at the first null character, which can be very confusing if a field begins with a null character.

(I spent an hour debugging my code, trying to figure out why it was not writing the value to the field, only to realize eventually that my code was fine.  It was the debugging tool [that’s what I use the sqlite3 cli for] that was buggy.)

This excerpt from a terminal sessions demonstrates the problem.  You need to have the perl DBI and DBD::SQLite modules installed for it to work:

$ perl -MDBI -e 'my $dbh = DBI->connect("dbi:SQLite:dbname=/tmp/foo.sqlite","",""); $dbh->do("create table foo ( bar BLOB )"); $dbh->prepare("insert into foo values (?)")->execute("foo\0bar");'
$ sqlite3 /tmp/foo.sqlite
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
sqlite> select * from foo;
foo
sqlite>

I don’t know how to get null characters into a database from the sqlite3 shell, if that is even possible.  If you do not have the perl modules installed, you can use:

$ perl -MCPAN -eshell

and then type ‘install DBI DBD::SQLite' at the cpan prompt.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The cli truncates values containing null chars

Hick Gunter
This is well documented. The result of expressions involving strings with embedded NUL characters is undefined. The conversion BLOB -> TEXT will check for and stop at the first embedded NUL; in none occurs, a terminating NUL is added. You may store any sequence of bytes in a BLOB, but please do not expect a data storage layer library to handle opaque objects. If you do need to see inside a blob, use the HEX() function to convert it to a readable form.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Father Chrysostomos
Gesendet: Dienstag, 09. Mai 2017 05:02
An: [hidden email]
Betreff: [sqlite] The cli truncates values containing null chars

While the SQLite database itself supports embedded nulls without any problems, the command-line utility truncates values at the first null character, which can be very confusing if a field begins with a null character.

(I spent an hour debugging my code, trying to figure out why it was not writing the value to the field, only to realize eventually that my code was fine.  It was the debugging tool [that’s what I use the sqlite3 cli for] that was buggy.)

This excerpt from a terminal sessions demonstrates the problem.  You need to have the perl DBI and DBD::SQLite modules installed for it to work:

$ perl -MDBI -e 'my $dbh = DBI->connect("dbi:SQLite:dbname=/tmp/foo.sqlite","",""); $dbh->do("create table foo ( bar BLOB )"); $dbh->prepare("insert into foo values (?)")->execute("foo\0bar");'
$ sqlite3 /tmp/foo.sqlite
SQLite version 3.18.0 2017-03-28 18:48:43 Enter ".help" for usage hints.
sqlite> select * from foo;
foo
sqlite>

I don’t know how to get null characters into a database from the sqlite3 shell, if that is even possible.  If you do not have the perl modules installed, you can use:

$ perl -MCPAN -eshell

and then type ‘install DBI DBD::SQLite' at the cpan prompt.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The cli truncates values containing null chars

Jens Alfke-2

> On May 9, 2017, at 4:21 AM, Hick Gunter <[hidden email]> wrote:
>
> You may store any sequence of bytes in a BLOB, but please do not expect a data storage layer library to handle opaque objects.

This sounds like a typo? I definitely _do_ expect a data storage library to handle opaque/binary objects.

> If you do need to see inside a blob, use the HEX() function to convert it to a readable form.

It has always annoyed me that the CLI just dumps blobs directly to stdout. At best it’s unreadable garbage, and at worst it outputs escape sequences that mess up the terminal window’s settings. My schema stores a lot of binary data, and if I’m in the CLI and forget a “hex(…)” wrapper in a SELECT statement, sometimes I have to close the window to recover from purple-on-blue text :-p

IMO, blobs in the output should be either partially or fully escaped. If there is somehow a reason for letting the CLI output raw binary, then at least add a setting for how to output blobs, defaulting to partially-escaped.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The cli truncates values containing null chars

petern
Some output modes of shell.c already automatically hex print BLOB's:

<code>
static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){
  int i;
  char *zBlob = (char *)pBlob;
  raw_printf(out,"X'");
  for(i=0; i<nBlob; i++){ raw_printf(out,"%02x",zBlob[i]&0xff); }
  raw_printf(out,"'");
}
</code>

That function is called within the output loop switch statement:

<code>
   case MODE_Quote:
    case MODE_Insert: {
/*...bunch of code omitted*/
          output_hex_blob(p->out, pBlob, nBlob);
</code>

I'm not a user of BLOB's.  Maybe somebody else knows the use case for the
raw bytes in the other modes?   If there is no use case whatsoever, maybe
this is a bug report.




On Tue, May 9, 2017 at 9:24 AM, Jens Alfke <[hidden email]> wrote:

>
> > On May 9, 2017, at 4:21 AM, Hick Gunter <[hidden email]> wrote:
> >
> > You may store any sequence of bytes in a BLOB, but please do not expect
> a data storage layer library to handle opaque objects.
>
> This sounds like a typo? I definitely _do_ expect a data storage library
> to handle opaque/binary objects.
>
> > If you do need to see inside a blob, use the HEX() function to convert
> it to a readable form.
>
> It has always annoyed me that the CLI just dumps blobs directly to stdout.
> At best it’s unreadable garbage, and at worst it outputs escape sequences
> that mess up the terminal window’s settings. My schema stores a lot of
> binary data, and if I’m in the CLI and forget a “hex(…)” wrapper in a
> SELECT statement, sometimes I have to close the window to recover from
> purple-on-blue text :-p
>
> IMO, blobs in the output should be either partially or fully escaped. If
> there is somehow a reason for letting the CLI output raw binary, then at
> least add a setting for how to output blobs, defaulting to
> partially-escaped.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The cli truncates values containing null chars

Richard Hipp-3
In reply to this post by Jens Alfke-2
On 5/9/17, Jens Alfke <[hidden email]> wrote:
>
> It has always annoyed me that the CLI just dumps blobs directly to stdout.

Have you tried setting ".mode quote"?  Perhaps add that to your
~/sqlite.rc file?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users