Potential Bug Fix: Sqlite CLI removes CR characters in end of line even if they are inside quoted string literals.

SQLITE_CLI_GARBLES_STRINGS.md

BUG

SQLite CLI removes '\r' even from inside quoted strings that appear in SQL input piped into sqlite (CLI). These are never replaced later. A program should not tamper literals in this undocumented way.

DEMONSTRATION

Files in this repository face automatic CRLF translation from browsers or perhaps github. So take care that you either download zip of this gist or first ensure that git itself does not do automatic CRLF translations (run git config --global core.autocrlf false), only then clone the gist.

To run demo, hop inside folder containing bug_demo.sh, and run bash -x bug_demo.sh.

View the relevant files (see DESCRIPTION OF VARIOUS FILES below) using any program which shows all bytes. I used hexdump (hd) in demo for this purpose.

POTENTIAL FIX

A diff against version b066637bb7 that removes the bug (on my pc) is in file potential_correction.diff.

NOTES

fossil blame src/shell.c shows:

...
b1b48be1cf 2009-12-17        shaneh:       if( n>0 && zLine[n-1]=='\r' ) n--;
...

fossil info b1b48be1cf shows:

uuid         b1b48be1cf55f3179eab8824cf5fee8100518845 2009-12-17 21:07:15 UTC
parent       333c3ffe6d4d2293d01958812b9c02b0bf9796a4 2009-12-17 21:05:43 UTC
child        29e3c8da1bd3971215036e5f5cfa5b25c6caa81f 2009-12-17 21:07:54 UTC
tags         trunk`
comment      In shell.c (CLI), modified local_getline() to remove '\r' as well
              as '\n' from end of lines. 
              This provides consistency between MSVC and CYGWIN builds.
              (user: shaneh)

Someone please weigh the problem and decide on appropriate fix.

DESCRIPTION OF VARIOUS FILES

README                      This file.
original_dump.sql           original database dump.
                            (has '\r' inside a string literal).
final_dump.sql              dump of the imported database
                            ('\r' missing)
diff                        diff between original and final dumps

bug_demo.sh                 script that demonstrates bug

demo_output.txt             stdout of the above script when I ran it

potential_correction.diff    A diff against b066637bb7 that
                            removes the bug (on my pc).

LIST OF PROGRAMS USED IN DEMO SCRIPT

hd          hexdump, to demonstrate CR ('\r', ASCII 0x0D) character
            in outputs which would otherwise be not visible on terminal
grep        to look for CR in hexdump output
diff
sqlite3
uname       to identify the platform

-- Prashant Karmakar webster15july@gmail.com Sat Oct 8 12:27:49 IST 2016

bug_demo.sh

hd original_dump.sql | grep " 0d " # look for CR character
sqlite3 imported.db < original_dump.sql 
sqlite3 imported.db .dump > final_dump.sql 
rm imported.db
hd final_dump.sql | grep " 0d " # look for CR character
diff original_dump.sql final_dump.sql > diff
hd diff
sqlite3 -version
uname -a

demo_output.txt

000000e0  66 73 6b 0d 0a 64 66 61  64 66 27 27 0a 27 29 3b  |fsk..dfadf''.');|
00000000  31 34 63 31 34 0a 3c 20  6d 6f 6a 6f 66 73 6b 0d  |14c14.< mojofsk.|
00000010  0a 2d 2d 2d 0a 3e 20 6d  6f 6a 6f 66 73 6b 0a     |.---.> mojofsk.|
0000001f
3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
Linux pk-pc 4.4.0-38-generic #57-Ubuntu SMP Tue Sep 6 15:42:33 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

diff

14c14
< mojofsk
---
> mojofsk

final_dump.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE po (a);
INSERT INTO "po" VALUES('pi');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
COMMIT;

original_dump.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE po (a);
INSERT INTO "po" VALUES('pi');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
INSERT INTO "po" VALUES('pi
mojofsk
dfadf''
');
COMMIT;

potential_correction.diff

Index: src/shell.c
==================================================================
--- src/shell.c
+++ src/shell.c
@@ -515,11 +515,10 @@
       break;
     }
     while( zLine[n] ) n++;
     if( n>0 && zLine[n-1]=='\n' ){
       n--;
-      if( n>0 && zLine[n-1]=='\r' ) n--;
       zLine[n] = 0;
       break;
     }
   }
 #if defined(_WIN32) || defined(WIN32)