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.