SQLite Forum

INSERT SELECT form can leak (tested on 3.28.0)
Login

INSERT SELECT form can leak (tested on 3.28.0)

(1.1) By Rico Mariani (rmariani) on 2021-03-16 20:40:27 edited from 1.0 [source]

Minimum repro

-- must be auto-increment
create table x (
  pk integer primary key autoincrement
);

-- any join will do (even a bogus one like this)
insert into x
  select NULL pk from
  (select 1) t1 inner join (select 1) t2;

There are various workarounds, as long the the join isn't at the top level it all works fine. e.g. the following works without leaking:

insert into x
  select * from (
  select NULL pk from
  (select 1) t1 inner join (select 1) t2);

LeakSanitizer output follows:

=================================================================
==2879225==ERROR: LeakSanitizer: detected memory leaks

Indirect leak of 4368 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f5027343c95 in pcache1Alloc sqlite3-2.c:16684
    #5 0x7f50276012e1 in pcache1AllocPage sqlite3-2.c:16780
    #6 0x7f50276009ca in pcache1FetchStage2 sqlite3-2.c:17247
    #7 0x7f50276000f7 in pcache1FetchNoMutex sqlite3-2.c:17351
    #8 0x7f50275fdd00 in pcache1Fetch sqlite3-2.c:17393
    #9 0x7f502732dc5a in sqlite3PcacheFetch sqlite3-2.c:15833
    #10 0x7f502732c740 in getPageNormal sqlite3-2.c:23748
    #11 0x7f50272b7a84 in sqlite3PagerGet sqlite3-2.c:23927
    #12 0x7f502734f47b in btreeGetPage sqlite3-3.c:2106
    #13 0x7f502734b19e in lockBtree sqlite3-3.c:3064
    #14 0x7f50272b697e in sqlite3BtreeBeginTrans sqlite3-3.c:3437
    #15 0x7f502739846e in sqlite3VdbeExec sqlite3-3.c:24084
    #16 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #17 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 4112 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f5027343c95 in pcache1Alloc sqlite3-2.c:16684
    #5 0x7f5027343b72 in sqlite3PageMalloc sqlite3-2.c:16824
    #6 0x7f502730fbf2 in sqlite3PagerSetPagesize sqlite3-2.c:22061
    #7 0x7f502730e40d in sqlite3PagerOpen sqlite3-2.c:23219
    #8 0x7f502730a23d in sqlite3BtreeOpen sqlite3-3.c:2467
    #9 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #10 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #11 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 4104 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f5027343c95 in pcache1Alloc sqlite3-2.c:16684
    #5 0x7f5027343b72 in sqlite3PageMalloc sqlite3-2.c:16824
    #6 0x7f50273e2f7a in allocateTempSpace sqlite3-3.c:2659
    #7 0x7f50273e28c5 in btreeCursor sqlite3-3.c:4370
    #8 0x7f50273bdc55 in sqlite3BtreeCursor sqlite3-3.c:4412
    #9 0x7f50273988a4 in sqlite3VdbeExec sqlite3-3.c:24106
    #10 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #11 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 2056 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f50272b3644 in sqlite3MallocZero sqlite3-1.c:26937
    #5 0x7f50275fee18 in pcache1ResizeHash sqlite3-2.c:16882
    #6 0x7f50275fd457 in pcache1Create sqlite3-2.c:17122
    #7 0x7f5027311916 in sqlite3PcacheSetPageSize sqlite3-2.c:15769
    #8 0x7f5027310f5e in sqlite3PcacheOpen sqlite3-2.c:15758
    #9 0x7f502730e594 in sqlite3PagerOpen sqlite3-2.c:23227
    #10 0x7f502730a23d in sqlite3BtreeOpen sqlite3-3.c:2467
    #11 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #12 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #13 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 768 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f50272b3644 in sqlite3MallocZero sqlite3-1.c:26937
    #5 0x7f502730cd34 in sqlite3PagerOpen sqlite3-2.c:23099
    #6 0x7f502730a23d in sqlite3BtreeOpen sqlite3-3.c:2467
    #7 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #8 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #9 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 176 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f50272b3644 in sqlite3MallocZero sqlite3-1.c:26937
    #5 0x7f50275fcfa3 in pcache1Create sqlite3-2.c:17104
    #6 0x7f5027311916 in sqlite3PcacheSetPageSize sqlite3-2.c:15769
    #7 0x7f5027310f5e in sqlite3PcacheOpen sqlite3-2.c:15758
    #8 0x7f502730e594 in sqlite3PagerOpen sqlite3-2.c:23227
    #9 0x7f502730a23d in sqlite3BtreeOpen sqlite3-3.c:2467
    #10 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #11 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #12 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 144 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f50272b3644 in sqlite3MallocZero sqlite3-1.c:26937
    #5 0x7f502730a1dd in sqlite3BtreeOpen sqlite3-3.c:2462
    #6 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #7 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #8 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

Indirect leak of 80 byte(s) in 1 object(s) allocated from:
    #0 0x12eeb9f in malloc 
    #1 0x7f50275fca4d in sqlite3MemMalloc sqlite3-1.c:22870
    #2 0x7f50272f395f in mallocWithAlarm sqlite3-1.c:26702
    #3 0x7f502729d2fe in sqlite3Malloc sqlite3-1.c:26732
    #4 0x7f50272b3644 in sqlite3MallocZero sqlite3-1.c:26937
    #5 0x7f50273096c8 in sqlite3BtreeOpen sqlite3-3.c:2370
    #6 0x7f502739840f in sqlite3VdbeExec sqlite3-3.c:24080
    #7 0x7f50272c3df6 in sqlite3Step sqlite3-3.c:18743
    #8 0x7f50272c2c96 in sqlite3_step sqlite3-3.c:18808

(2) By Richard Hipp (drh) on 2021-03-16 22:25:29 in reply to 1.1 [link] [source]

Appears to have been resolved by check-in a9b90aa12eecdd9f on 2019-05-03 which first appeared in version 3.29.0.

(3) By Rico Mariani (rmariani) on 2021-03-17 03:36:30 in reply to 2 [link] [source]

Excellent thank you!

(4.1) Originally by Rico Mariani (rmariani) with edits by Richard Hipp (drh) on 2021-03-17 12:17:53 from 4.0 in reply to 2 [link] [source]

FWIW, a huge swath of the Messenger test suites use SQLite and they run with ASAN in CI so it's a nice "real world" test that's running all the time. The only unfortunate thing is that it tends to run on older SQLite versions because that's what is on the iOS devices we target.

I added code to our compiler to detect the bad pattern so we can avoid it.

https://github.com/facebookincubator/CG-SQL/commit/9db63e43b7fb2561384b47cd3f9cc1d685b233d8