On 2011/7/2, at 下午10:19, Sylvain Munaut wrote:
> Hi
>
>> It will be really appreciated if anyone with similar experience can offer
>> insight to my issue here.
>
> No idea if that's the problem but one known issue with external
> interaction to hlr.sqlite3 is that the binary coding/decoding routinge
> (to store binary data inside sqlite) are not standard.
>
> The DBI interface we use has it's own routines and if you try a
> 'native' sqlite3 driver, you may not get the right data. When
> accessing binary data fields from a auto-provisionning app for 27c3
> from a python script, I actually had to write my own 'dbi-equivalent'
> binary coding routine ...
>
> See the dbi source code for more info about the algorithm itself.
Sylvian, thanks a lot for the great hint! I also ported the dbd_decode_binary
routine to perl an it correctly decoded the sms text from user_data.
The overall logic looks like:
my $dbh = DBI->connect("dbi:SQLite:dbname=hlr.sqlite3");
$dbh->{sqlite_unicode} = 0;
my $sth = $dbh->prepare("SELECT * FROM SMS;");
$sth->execute();
my $row = $sth->fetchrow_hashref();
say gsm_7bit_decode( dbd_decode_binary($row->{user_data}) );
The implementation of gsm_7bit_decode and dbd_decode_binary in perl is posted
there: https://gist.github.com/1061933
This really surprise me. I briefly looked the libdbi implementation and turns
out it can only perform SQL INSERT or UPDATE with only full SQL statements, and
the dbd_encode_binary basically turns any BLOB into TEXT (by escaping \0) so it
can be part of the SQL statement.
