Hi all,
I have been working on a project involved using osmo-nitb and trying to read / send SMS by doing SQL queries directly hlr.sqlite3 in a perl program, but I cannot figure out the encode/decode process on the "user_data" field.
From "sms_from_text" function in libmsc/gsm_04_11.c, it appears to me that the content of user_data is a gsm_7bit_encode-ed of text if the SMS is sent with VTY interface. I then tried to port gsm_7bit_decode to perl and the implementation successfully decoded two test strings in libosmocore/tests/sms/sms_test.c. However it does not successfully decode any SMS user_data record in hlr.sqlite3 to the original text. The result looks like just a piece of garbled text, not even close to any human-readable text at all.
I also tried briefly to include gsm0338 decoding process, or some other perl-based implementation from CPAN, no success at all. I am currently out of ideas to try.
It will be really appreciated if anyone with similar experience can offer insight to my issue here.
On 07/02/2011 03:35 PM, Kang-min Liu wrote:
Hi all,
Hi Kang-min,
Our gsm_7bit_encode has some issues (e.g. see the archive of this mailinglist), from a quick look at the sms_from_text routine you mention, and the store inside the db.c i would assume your perl code is doing something wrong.
Can you share your perl code? Alternatively the following debug hint might be useful: 1.) send a SMS through the telnet interface.. 2.) osmo_hexdump the sms->user_data in the sms_from_text 3.) also directly decode the string there... (in C) 4.) dump the BLOB for the database in db_sms_store
good luck holger
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.
Cheers,
Sylvain
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.
Hi,
On Sun, Jul 03, 2011 at 11:54:23AM +0800, Kang-min Liu wrote:
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.
yes, had I ever read the source code of (c language) libdbi and libdbd-sqlite3, I would have never used it for the OpenBSC project.
It's ridiculous how they deal with databases, giving away any performance that the underlying DB might have.
Once we move the SQL + HLR/VLR out of the main OpenBSC process, I guess we will have to use 'native' database APIs.
Regards, Harald
-
Harald Welte -
Holger Hans Peter Freyther -
Kang-min Liu -
Sylvain Munaut