ports/162650: sqlite3 command shell incorrectly handles some blobs

[Valentin Davydov]

>Number:         162650
>Category:       ports
>Synopsis:       sqlite3 command shell incorrectly handles some blobs
>Confidential:   no
>Severity:       serious
>Priority:       low
>Responsible:    freebsd-ports-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          sw-bug
>Submitter-Id:   current-users
>Arrival-Date:   Fri Nov 18 09:30:10 UTC 2011
>Closed-Date:
>Last-Modified:
>Originator:     Valentin Davydov
>Release:        RELENG_8_2
>Organization:
State Optical Institute
>Environment:
FreeBSD hostname.domain 8.2-STABLE FreeBSD 8.2-STABLE #13: Fri Apr 29 12:54:43 MSD 2011     user at hostname.domain:/usr/obj/usr/src/sys/KOR  i386

>Description:
When the commadline shell supplied with databases/sqlite3 (any version of year 2010 and later) is asked to output database content in the form of SQL statement (by issuing .mode insert), and actual data contains blobs, some of them gets corrupted in the output. This is due to the datatype/format mismatch in one of the internal functions of the shell called output_hex_blob().
>How-To-Repeat:
Take FreeBSD 8.x (bug was tested on several various versions, both i386 and amd64). Install port databases/sqlite3, either compiling from ports or through binary package. Then launch sqlite3 shell and give it commands as shown below (shell responses are indented for clarity):

$ sqlite3 db.tmp
   SQLite version 3.7.9 2011-11-01 00:52:41
   Enter ".help" for instructions
   Enter SQL statements terminated with a ";"
sqlite> create table t(v blob);
sqlite> insert into t values(X'0123456789');
sqlite> .mode insert
sqlite> select * from t;
   INSERT INTO table VALUES(X'01234567ffffff89');

>Fix:
Following patch helps (save it as databases/sqlite3/files/patch-src-shell.c)

--- src/shell.c.orig    2011-11-01 16:31:18.000000000 +0400
+++ src/shell.c 2011-11-10 22:45:11.000000000 +0400
@@ -490,7 +490,7 @@
 */
 static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){
   int i;
-  char *zBlob = (char *)pBlob;
+  unsigned char *zBlob = (unsigned char *)pBlob;
   fprintf(out,"X'");
   for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]); }
   fprintf(out,"'");

Perhaps one should take another strategy (for example, replace "%02x" format
with "%02hhx" of even reimplement necessary fprinf() functionality) depending on the policy (it seems that using signed datatypes for semanticaly non-negative data is the policy of the sqlite developers team).

>Release-Note:
>Audit-Trail:
>Unformatted: