SQLite User Forum

Three testcases causing different Assertion Failed
Login
Hierarchical

Three testcases causing different Assertion Failed

(1) By Jingzhou Fu (fuboat) on 2022-01-01 15:08:44 [source]

Hi Richard. I find some testcases causing Assertion Failed today, here are the PoCs, and I will show their backtrace in the replies of this thread.

  • command: sqlite3 < crash.sql
  • version: version: 3.37.1
  • compile params: Clang-12 with debug enabled

PoC No.1 (crash.sql):

PRAGMA writable_schema = 1;
CREATE TABLE c1(x);
CREATE TABLE sqlite_sequence (name PRIMARY KEY) WITHOUT ROWID;
ALTER TABLE c1 RENAME TO a;

PoC No.2 (crash.sql)

ATTACH ':memory:' AS aux;
CREATE TABLE aux.t20_2(y);
CREATE TEMP TRIGGER q AFTER INSERT ON t20_2 BEGIN UPDATE t20_3 SET z=z+1; END;
CREATE TEMP TABLE IF NOT EXISTS f2(f3);
DETACH aux;
SAVEPOINT two;
PRAGMA schema_version = 10;
CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';
CREATE TEMP TABLE IF NOT EXISTS f2(f3);
CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';

PoC No.3 (crash.sql)

SAVEPOINT abc;
SAVEPOINT abc;
PRAGMA secure_delete=true;
ATTACH ':memory:' as aux;
PRAGMA page_size = 1024;
PRAGMA writable_schema=ON;
PRAGMA auto_vacuum = incremental;
CREATE TABLE sqlite_stat1(tbl, idx);
INSERT INTO sqlite_stat1 VALUES(2, zeroblob(248*1020 + 100));
SAVEPOINT abc;
PRAGMA auto_vacuum = incremental;
CREATE TABLE stat(sqlsim4, sqlsim5);
SAVEPOINT abc;
INSERT INTO sqlite_stat1 VALUES(2, zeroblob(248*1020 + 100));
ANALYZE;
SAVEPOINT abc;
PRAGMA auto_vacuum = incremental;
CREATE VIRTUAL TABLE v2 USING echo ;
INSERT INTO stat VALUES(2, zeroblob(248*1020 + 100));;ANALYZE;ROLLBACK TO abc;;ANALYZE;;ATTACH '' AS vacuum_db;
ANALYZE;
ANALYZE;

And, emm, I want to say more here. Sqlite3 is really a safety database engine, I think. The same as you have mentioned in DOCUMENT cves.html, these three testcases also seems to be harmless.

I believe that you have noticed I am doing some research on DBMS fuzzing. Among DBMS software I am testing including MySQL, PostgreSQL, MariaDB and SQLite, the SQLite engine does best in defending against arcane SQL statements.

You know, other DBMS software has much stricter limitations on SQL syntax and semantics, such as column types checking. However, there are still MUCH MORE bugs I found in them recent days than in SQLite. I think it is amazing that we can use SQLite with much less limitations and much more safety at the same time.

Thank you very much, Richard. SQLite is really great.

(2) By Jingzhou Fu (fuboat) on 2022-01-01 15:12:44 in reply to 1 [link] [source]

bt of Poc No.1

sqlite3: sqlite3.c:126779: void sqlite3CompleteInsertion(Parse *, Table *, int, int, int, int *, int, int, int): Assertion `pParse->nested==0' failed.

Program received signal SIGABRT, Aborted.
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50      ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fe574ab3859 in __GI_abort () at abort.c:79
#2  0x00007fe574ab3729 in __assert_fail_base (fmt=0x7fe574c49588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x2e1c40 <str> "pParse->nested==0", 
    file=0x263b40 <str.1> "sqlite3.c", line=126779, function=<optimized out>) at assert.c:92
#3  0x00007fe574ac4f36 in __GI___assert_fail (assertion=0x2e1c40 <str> "pParse->nested==0", file=0x263b40 <str.1> "sqlite3.c", line=126779, 
    function=0x2e1b40 <__PRETTY_FUNCTION__.sqlite3CompleteInsertion> "void sqlite3CompleteInsertion(Parse *, Table *, int, int, int, int *, int, int, int)") at assert.c:101
#4  0x000000000092278b in sqlite3CompleteInsertion (pParse=0x7ffd14ba3100, pTab=0x60b000000670, iDataCur=5, iIdxCur=5, regNewData=43, aRegIdx=0x62e00000a5a4, update_flags=4, appendBias=0, 
    useSeekResult=0) at sqlite3.c:126779
#5  0x000000000068383a in sqlite3Update (pParse=0x7ffd14ba3100, pTabList=0x62e0000096a0, pChanges=0x62e000009220, pWhere=0x62e00000a1a0, onError=11, pOrderBy=0x0, pLimit=0x0, pUpsert=0x0)
    at sqlite3.c:143715
#6  0x000000000061f3ac in yy_reduce (yypParser=0x7ffd14ba1330, yyruleno=157, yyLookahead=1, yyLookaheadToken=..., pParse=0x7ffd14ba3100) at sqlite3.c:164372
#7  0x000000000060dc64 in sqlite3Parser (yyp=0x7ffd14ba1330, yymajor=1, yyminor=...) at sqlite3.c:165425
#8  0x0000000000548600 in sqlite3RunParser (pParse=0x7ffd14ba3100, zSql=0x62e0000090de "", pzErrMsg=0x7ffd14ba1e80) at sqlite3.c:166721
#9  0x000000000089e852 in sqlite3NestedParse (pParse=0x7ffd14ba3100, zFormat=0x2e8080 <str> "UPDATE \"%w\".sqlite_sequence set name = %Q WHERE name = %Q") at sqlite3.c:113575
#10 0x00000000006b2d6f in sqlite3AlterRenameTable (pParse=0x7ffd14ba3100, pSrc=0x62e00000ae20, pName=0x7ffd14ba2678) at sqlite3.c:108450
#11 0x000000000062aa5d in yy_reduce (yypParser=0x7ffd14ba25d0, yyruleno=288, yyLookahead=1, yyLookaheadToken=..., pParse=0x7ffd14ba3100) at sqlite3.c:164945
#12 0x000000000060dc64 in sqlite3Parser (yyp=0x7ffd14ba25d0, yymajor=1, yyminor=...) at sqlite3.c:165425
#13 0x0000000000548600 in sqlite3RunParser (pParse=0x7ffd14ba3100, zSql=0x60c00000011a ";", pzErrMsg=0x7ffd14ba30e0) at sqlite3.c:166721
#14 0x00000000005354c0 in sqlite3Prepare (db=0x617000000080, zSql=0x60c000000100 "ALTER TABLE c1 RENAME TO a;", nBytes=-1, prepFlags=128, pReprepare=0x0, ppStmt=0x7ffd14ba34a0, 
    pzTail=0x7ffd14ba34c0) at sqlite3.c:133175
#15 0x0000000000533b0a in sqlite3LockAndPrepare (db=0x617000000080, zSql=0x60c000000100 "ALTER TABLE c1 RENAME TO a;", nBytes=-1, prepFlags=128, pOld=0x0, ppStmt=0x7ffd14ba34a0, 
    pzTail=0x7ffd14ba34c0) at sqlite3.c:133250
#16 0x000000000052b98f in sqlite3_prepare_v2 (db=0x617000000080, zSql=0x60c000000100 "ALTER TABLE c1 RENAME TO a;", nBytes=-1, ppStmt=0x7ffd14ba34a0, pzTail=0x7ffd14ba34c0)
    at sqlite3.c:133336
#17 0x00000000004a9d3f in shell_exec (pArg=0x7ffd14ba4080, zSql=0x60c000000100 "ALTER TABLE c1 RENAME TO a;", pzErrMsg=0x7ffd14ba3620) at shell.c:14642
#18 0x00000000004b10ee in runOneSqlLine (p=0x7ffd14ba4080, zSql=0x60c000000100 "ALTER TABLE c1 RENAME TO a;", in=0x0, startline=4) at shell.c:21957
#19 0x00000000004ad765 in process_input (p=0x7ffd14ba4080) at shell.c:22067
#20 0x0000000000479bd8 in main (argc=1, argv=0x7ffd14ba55f8) at shell.c:22892

(3) By Jingzhou Fu (fuboat) on 2022-01-01 15:13:23 in reply to 1 [link] [source]

bt of PoC No.2

sqlite3: sqlite3.c:132653: int sqlite3InitOne(sqlite3 *, int, char **, u32): Assertion `(db->mDbFlags & DBFLAG_SchemaKnownOk)==0' failed.

Program received signal SIGABRT, Aborted.
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50      ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fe4e4077859 in __GI_abort () at abort.c:79
#2  0x00007fe4e4077729 in __assert_fail_base (fmt=0x7fe4e420d588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x29b100 <str> "(db->mDbFlags & DBFLAG_SchemaKnownOk)==0", 
    file=0x263b40 <str.1> "sqlite3.c", line=132653, function=<optimized out>) at assert.c:92
#3  0x00007fe4e4088f36 in __GI___assert_fail (assertion=0x29b100 <str> "(db->mDbFlags & DBFLAG_SchemaKnownOk)==0", file=0x263b40 <str.1> "sqlite3.c", line=132653, 
    function=0x29b160 <__PRETTY_FUNCTION__.sqlite3InitOne> "int sqlite3InitOne(sqlite3 *, int, char **, u32)") at assert.c:101
#4  0x000000000072d0ef in sqlite3InitOne (db=0x617000000080, iDb=0, pzErrMsg=0x7fff1508dc28, mFlags=0) at sqlite3.c:132653
#5  0x000000000072c8cb in sqlite3Init (db=0x617000000080, pzErrMsg=0x7fff1508dc28) at sqlite3.c:132889
#6  0x0000000000728df7 in sqlite3ReadSchema (pParse=0x7fff1508dc20) at sqlite3.c:132915
#7  0x0000000000635b3a in sqlite3StartTable (pParse=0x7fff1508dc20, pName1=0x7fff1508d180, pName2=0x7fff1508d198, isTemp=0, isView=0, isVirtual=1, noErr=0) at sqlite3.c:114531
#8  0x00000000006b9ab1 in sqlite3VtabBeginParse (pParse=0x7fff1508dc20, pName1=0x7fff1508d180, pName2=0x7fff1508d198, pModuleName=0x7fff1508d1c8, ifNotExists=0) at sqlite3.c:145099
#9  0x000000000062af94 in yy_reduce (yypParser=0x7fff1508d0f0, yyruleno=295, yyLookahead=1, yyLookaheadToken=..., pParse=0x7fff1508dc20) at sqlite3.c:164978
#10 0x000000000060dc64 in sqlite3Parser (yyp=0x7fff1508d0f0, yymajor=1, yyminor=...) at sqlite3.c:165425
#11 0x0000000000548600 in sqlite3RunParser (pParse=0x7fff1508dc20, zSql=0x60c00000012a ";", pzErrMsg=0x7fff1508dc00) at sqlite3.c:166721
#12 0x00000000005354c0 in sqlite3Prepare (db=0x617000000080, zSql=0x60c000000100 "CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';", nBytes=-1, prepFlags=128, pReprepare=0x0, 
    ppStmt=0x7fff1508dfc0, pzTail=0x7fff1508dfe0) at sqlite3.c:133175
#13 0x0000000000533b0a in sqlite3LockAndPrepare (db=0x617000000080, zSql=0x60c000000100 "CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';", nBytes=-1, prepFlags=128, pOld=0x0, 
    ppStmt=0x7fff1508dfc0, pzTail=0x7fff1508dfe0) at sqlite3.c:133250
#14 0x000000000052b98f in sqlite3_prepare_v2 (db=0x617000000080, zSql=0x60c000000100 "CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';", nBytes=-1, ppStmt=0x7fff1508dfc0, pzTail=0x7fff1508dfe0)
    at sqlite3.c:133336
#15 0x00000000004a9d3f in shell_exec (pArg=0x7fff1508eba0, zSql=0x60c000000100 "CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';", pzErrMsg=0x7fff1508e140) at shell.c:14642
#16 0x00000000004b10ee in runOneSqlLine (p=0x7fff1508eba0, zSql=0x60c000000100 "CREATE VIRTUAL TABLE temp.'ia1' USING 'ia';", in=0x0, startline=10) at shell.c:21957
#17 0x00000000004ad765 in process_input (p=0x7fff1508eba0) at shell.c:22067
#18 0x0000000000479bd8 in main (argc=1, argv=0x7fff15090108) at shell.c:22892

(4) By Jingzhou Fu (fuboat) on 2022-01-01 15:14:10 in reply to 1 [link] [source]

bt of PoC No.3

sqlite3: sqlite3.c:55055: int pager_playback_one_page(Pager *, i64 *, Bitvec *, int, int): Assertion `!isSavepnt' failed.

Program received signal SIGABRT, Aborted.
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50      ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007f7188983859 in __GI_abort () at abort.c:79
#2  0x00007f7188983729 in __assert_fail_base (fmt=0x7f7188b19588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x276a40 <str.603> "!isSavepnt", file=0x263b40 <str.1> "sqlite3.c", 
    line=55055, function=<optimized out>) at assert.c:92
#3  0x00007f7188994f36 in __GI___assert_fail (assertion=0x276a40 <str.603> "!isSavepnt", file=0x263b40 <str.1> "sqlite3.c", line=55055, 
    function=0x276780 <__PRETTY_FUNCTION__.pager_playback_one_page> "int pager_playback_one_page(Pager *, i64 *, Bitvec *, int, int)") at assert.c:101
#4  0x000000000057f05f in pager_playback_one_page (pPager=0x618000000080, pOffset=0x7ffe5a4ad300, pDone=0x615000005300, isMainJrnl=0, isSavepnt=1) at sqlite3.c:55055
#5  0x000000000057dc48 in pagerPlaybackSavepoint (pPager=0x618000000080, pSavepoint=0x612000000ba0) at sqlite3.c:56209
#6  0x000000000055c861 in sqlite3PagerSavepoint (pPager=0x618000000080, op=2, iSavepoint=4) at sqlite3.c:59725
#7  0x00000000005596bf in sqlite3BtreeSavepoint (p=0x607000000100, op=2, iSavepoint=4) at sqlite3.c:70059
#8  0x0000000000558e35 in vdbeCloseStatement (p=0x62e000008740, eOp=2) at sqlite3.c:82227
#9  0x00000000005585e1 in sqlite3VdbeCloseStatement (p=0x62e000008740, eOp=2) at sqlite3.c:82260
#10 0x0000000000551efc in sqlite3VdbeHalt (p=0x62e000008740) at sqlite3.c:82452
#11 0x00000000007c4a3f in sqlite3VdbeExec (p=0x62e000008740) at sqlite3.c:95326
#12 0x000000000073eae9 in sqlite3Step (p=0x62e000008740) at sqlite3.c:85265
#13 0x000000000073b5f2 in sqlite3_step (pStmt=0x62e000008740) at sqlite3.c:85322
#14 0x00000000004b68b1 in exec_prepared_stmt (pArg=0x7ffe5a4af320, pStmt=0x62e000008740) at shell.c:14423
#15 0x00000000004ab942 in shell_exec (pArg=0x7ffe5a4af320, zSql=0x60c000000100 "ANALYZE;", pzErrMsg=0x7ffe5a4ae8c0) at shell.c:14738
#16 0x00000000004b10ee in runOneSqlLine (p=0x7ffe5a4af320, zSql=0x60c000000100 "ANALYZE;", in=0x0, startline=21) at shell.c:21957
#17 0x00000000004ad765 in process_input (p=0x7ffe5a4af320) at shell.c:22067
#18 0x0000000000479bd8 in main (argc=1, argv=0x7ffe5a4b0898) at shell.c:22892

(5) By RandomCoder on 2022-01-01 19:55:03 in reply to 1 [link] [source]

And as always, because I was curious when these appeared:

POC #1

3.27.2 2019-02-25 16:06:06 bd49a8271d650fa89e446b42e513b595a717b9212c91dd384aab871fc1d0f6d7
Error: near line 3: table sqlite_sequence may not be indexed

-- vs --

3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
Assertion failed: pParse->nested==0, file sqlite3.c, line 118372

POC #3:

3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ebd1f
Error: near line 15: SQL logic error
Error: near line 18: no such module: echo
Error: near line 19: SQL logic error
Error: near line 20: SQL logic error
Error: near line 21: SQL logic error

-- vs --

3.35.0 2021-03-12 15:10:09 acd63062eb06748bfe9e4886639e4f2b54ea6a496a83f10716abbaba4115500b
Error: near line 15: SQL logic error
Error: near line 18: no such module: echo
Error: near line 19: SQL logic error
Error: near line 20: SQL logic error
Assertion failed: !isSavepnt, file sqlite3.c, line 54428

Interestingly, I can't repo the assert on the second POC. Not quite sure why yet.

(6.1) By Richard Hipp (drh) on 2022-01-06 12:34:41 edited from 6.0 in reply to 1 [link] [source]

All three problems are fixed on trunk, by these check-ins:

PoC #1 is a faulty assert() statement and is harmless to production builds. PoC #2 is a real bug but it is minor and should not cause problems in a production build.

PoC #3, on the other hand, could lead to database corruption if a SAVEPOINT is rolled back and the outer transaction goes on to commit. PoC #3 was introduced by check-in 23ca23894af352ea about 10 months ago.