How to store NUL characters into SQLite BLOB ?

dont_you has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to store binary data that contains NUL ("\x00") characters into a BLOB column of a SQLite 3 database. SQLite documentation says that BLOB's can contain NUL characters, but I get the data truncated at first ocurrence of NUL. I'm missing something? Should I always encode the data before the insert? Or this is a bug of DBD::SQLite?
Thanks in advance,
José

The following code outputs just "ABC" (using DBD::SQLite 3.08):

use DBI;

my $dbh = DBI->connect('DBI:SQLite:dbname=test_blob.db', '', '');

$dbh->do('CREATE TABLE test_blob( Bindata BLOB )');

my $bindata = "ABC" . "\x00" . "DEF";

my $sth1 = $dbh->prepare('INSERT INTO test_blob VALUES(?)' );
$sth1->execute($bindata);

my $sth2 = $dbh->prepare('SELECT Bindata FROM test_blob');
$sth2->execute();

my $row = $sth2->fetch();
my $fetched_data = $row->[0];

$sth2->finish();
$dbh->disconnect;

print $fetched_data;
[download]

Update: A known workaround is to use bind_param() to force data type as SQL_BLOB. For example:

use DBI qw(SQL_BLOB);

# ...

$sth = $dbh->prepare('INSERT INTO test_blob VALUES(?)' );
$sth->bind_param(1, $bindata, SQL_BLOB );
$sth->execute();
[download]

Comment on How to store NUL characters into SQLite BLOB ? Select or Download Code

Replies are listed 'Best First'.
Re: How to store NUL characters into SQLite BLOB ? by rnahi (Curate) on Sep 13, 2005 at 18:29 UTC
It looks like a bug. Try the follwing code with DBD::SQLite2 (previous version), and it will show the correct result. use strict; use warnings; use Data::Dumper; use DBI; my $dbh = DBI->connect('DBI:SQLite2:dbname=test_blob.db', '', ''); $dbh->{sqlite_handle_binary_nulls}=1; $dbh->do('CREATE TABLE test_blob( Bindata BLOB )'); my $bindata = "ABC" . "\x00" . "DEFINITIVELY"; print Data::Dumper->Dump([$bindata],['original']); my $sth1 = $dbh->prepare('INSERT INTO test_blob VALUES(?)' ); $sth1->execute($bindata); my $sth2 = $dbh->prepare('SELECT Bindata FROM test_blob'); $sth2->execute(); my $row = $sth2->fetch(); my $fetched_data = $row->[0]; print Data::Dumper->Dump([$fetched_data],['fetched']); [download] Results: $original = 'ABCDEFINITIVELY'; $fetched = 'ABCDEFINITIVELY'; Moreover, the data was inserted even using SQLite 3. If you want to be sure, just check if the file contains the string you have inserted after char 0x0. `$ grep DEFINITIVELY test_blob.db Binary file test_blob.db matches` [download] Therefore, the bug is in the retrieval function. (Notice that the standalone sqlite3 binary program gets the same truncated result.)	[reply] [d/l] [select]
Re: How to store NUL characters into SQLite BLOB ? by pg (Canon) on Sep 14, 2005 at 00:35 UTC
DBD::SQLite usually does not come with the latest version of SQLite library. DBD::SQLite has a private variable sqlite_version that tells you the version of SQLite library it uses. Because of this, what you read from the documentation of the latest SQLite library, when you try it with DBD::SQLite, it does not neccessary to work. We cannot determine whether this is a bug of DBD::SQLite, only base on your code. Several possibilities: The version of SQLite came with DBD::SQLite does not support BLOB with \x00. In this case, it is not a bug at all; The version of SQLite came with DBD::SQLite claims to support BLOB with \x00, but there was a bug. In this case, the bug is with SQLite, not DBD::SQLite; There is really a bug in DBD::SQLite. (The version of SQLite library it used supports \x00 with BLOB, but somehow DBD::SQLite messed that up)	[reply]
Re^2: How to store NUL characters into SQLite BLOB ? by Anonymous Monk on Sep 22, 2005 at 17:58 UTC
Well, I see the bug with SQLite 3.2.5 and DBD::SQLite 1.09. SQLite doesn't mention any bug fixes related to this (there is only one more recent version, 3.2.6), and as far as I canl tell, 1.09 is the latest SQLite version.	[reply]
Re^2: How to store NUL characters into SQLite BLOB ? by Anonymous Monk on Nov 11, 2005 at 02:55 UTC
I've tracked down the problem a bit deeper, and determined that it indeed is a problem with DBD::SQLite. Contrary to the other comments on this page, it is not a problem with retrieval but with storage. Blobs set by outside means are retrieved correctly. Currently, the full NUL containing BLOB is being stored, but since it be being bound with 'sqlite3_bind_text()' the SQLite internal type is being set to text, and then the subsequent retrieve fails. The solution is use sqlite3_bind_blob() to bind the variable. There is is currently code in dbdimp.c that appears to do this, but it never is called. I'm not sure if this is due to a bug or something not implemented at a higher level. I've come up with a a workaround patch, but I don't understand the internals well enough to know whether it is a good idea. --- dbdimp.c.orig 2005-11-10 19:27:55.000000000 -0700 +++ dbdimp.c 2005-11-10 19:27:32.000000000 -0700 @@ -356,11 +356,17 @@ STRLEN len; char * data = SvPV(value, len); retval = sqlite3_bind_blob(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); - } + } else { STRLEN len; char * data = SvPV(value, len); - retval = sqlite3_bind_text(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + if (memchr(data, 0, len)) { + sqlite_trace(4, "binding NUL containing data as a blob"); + retval = sqlite3_bind_blob(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + } + else { + retval = sqlite3_bind_text(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + } }	[reply]