SQLite User Forum

UTF-16 + blob + .mode quote → nonsense
Login

UTF-16 + blob + .mode quote → nonsense

(1) By anonymous on 2022-01-02 17:59:48 [source]

Running this through the shell (built from check-in da0af4dd9b) with an in-memory database:

    pragma encoding='UTF-16le';
    create table foo(fooval blob not null);
    insert into foo values(x'0123456789ABCDEF');
    .headers off
    .mode tabs
    select quote(fooval) from foo;
    .mode insert foo
    select fooval from foo;
    .mode quote
    select fooval from foo;

Getting this output:

    X'0123456789ABCDEF'
    INSERT INTO foo VALUES(X'0123456789abcdef');
    X'e28c81e69d85eaae89eebf8d'

That third line looks like the result of decoding the blob as UTF-16, encoding the resulting text as UTF-8, and then hexdumping the result of that.

The blob handling in shell_callback is essentially the same for insert mode and quote mode, so I blame exec_prepared_stmt instead:

    Index: src/shell.c.in
    ==================================================================
    --- src/shell.c.in
    +++ src/shell.c.in
    @@ -3364,11 +3364,12 @@
           do{
             nRow++;
             /* extract the data and data types */
             for(i=0; i<nCol; i++){
               aiTypes[i] = x = sqlite3_column_type(pStmt, i);
    -          if( x==SQLITE_BLOB && pArg && pArg->cMode==MODE_Insert ){
    +          if( x==SQLITE_BLOB && pArg &&
    +              (pArg->cMode==MODE_Insert || pArg->cMode==MODE_Quote) ){
                 azVals[i] = "";
               }else{
                 azVals[i] = (char*)sqlite3_column_text(pStmt, i);
               }
               if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){

Suggestion: rewriting the whole mess to use sqlite3_value pointers rather than C strings would mean fewer special cases like the one above.

(2) By Larry Brasfield (larrybr) on 2022-01-03 19:15:12 in reply to 1 [link] [source]

Thanks for the report. Fixed yesterday.