Row not saving with ATTACH from another DB in WAL mode

SQLCipher Issues
1

Hi, I noticed something interesting and want to understand if it’s expected.

There are two DBs plain.db and cipher.db (encrypted)

  1. Create a sqlite connection to plain.db and a sqlcipher connection to cipher.db
  2. Attach and detach the plain.db with cipher.db in the sqlicipher connection
  3. Insert a value in the plain.db via the plain sqlite connection created in step 1, and check if the value is there.
  4. Kill the process with the connection open.

Expected Behavior

When we reopen the plain.db, the value should still be present in it.

Actual Behavior

The value is not found anymore in plain.db, with sqlite version >= 3.44 and both DBs in WAL mode.

Steps to Reproduce

This issue can only be reproduced when:

  • sqlite version >= 3.44
  • DBs are in WAL mode
  • cipher.db is encrypted
  • ATTACH is done
  • Process is killed

Python scripts to reproduce the issue. Usage python test.py

test.py

import os
import sqlite3
import subprocess
from time import sleep

def main():
    if os.path.exists('plain.db'):
        os.remove('plain.db')
    if os.path.exists('cipher.db'):
        os.remove('cipher.db')

    process = subprocess.Popen(
        [
            'python',
            'insert_attach_kill.py',
        ],
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE,
        text=True,
    )
    sleep(1)
    process.kill()
    stdout, stderr = process.communicate()
    assert stdout == '', stdout
    assert stderr == '', stderr

    conn_plain = sqlite3.connect("plain.db")
    cursor_plain = conn_plain.cursor()
    
    db_value = cursor_plain.execute("SELECT * FROM A;").fetchone()
    assert db_value == (1,), f'{db_value} != (1,)'  # this fails

if __name__ == "__main__":
    main()

insert_attach_kill.py

import sqlite3
from time import sleep
from pysqlcipher3 import dbapi2 as sqlcipher


def main():
    conn_plain = sqlite3.connect("plain.db")
    cursor_plain = conn_plain.cursor()
    cursor_plain.execute("PRAGMA journal_mode=WAL;")
    cursor_plain.execute("CREATE TABLE IF NOT EXISTS A(a);")

    conn_cipher = sqlcipher.connect("cipher.db")
    cursor_cipher = conn_cipher.cursor()
    cursor_cipher.execute('PRAGMA key="password";')
    cursor_cipher.execute("PRAGMA journal_mode=WAL;")
    cursor_cipher.execute('ATTACH DATABASE "plain.db" AS plain KEY "";')
    cursor_cipher.execute('DETACH DATABASE plain;')
    
    cursor_plain.execute('INSERT INTO A VALUES(1);')
    conn_plain.commit()

    db_value = cursor_plain.execute("SELECT * FROM A;").fetchone()
    assert db_value == (1,), f'{db_value} != (1,)'

    sleep(10)

if __name__ == "__main__":
    main()```
2

@Ojuswi the problem in this case is that you are using two different SQLite libraries. This is not supported and know to cause locking and corruption issues. Remove all references to sqlite3, and only use sqlcipher. You can still create plaintext databases with sqlcipher, just don’t set a key. If you make that change everything will work as expected.

1 Like