sqlite3.iterdump() incompatible with binary data

[dotysan]

Bug report

Checklist

• I am confident this is a bug in CPython, not a bug in a third-party project
• I have searched the CPython issue tracker,
  and am confident this bug has not been reported before

CPython versions tested on:

3.11

Operating systems tested on:

Linux

Output from running 'python -VV' on the command line:

Python 3.11.5 (main, Aug 25 2023, 13:19:53) [GCC 9.4.0]

A clear and concise description of the bug:

Apologies if I'm misunderstanding. Please advice if I should post elsewhere. But shouldn't iterdump() properly detect VARCHAR columns with binary data and output X'' strings instead of throwing an error? This is what sqlite3 .dump does.

import sqlite3
with sqlite3.connect(db_path) as conn:
    with open(dump_path, 'w') as dump:
        for line in conn.iterdump():
            pass

The above will throw an error:

  File "foo.py", line 79, in dump_sqlite_db
    for line in conn.iterdump():
  File "/usr/lib/python3.11/sqlite3/dump.py", line 63, in _iterdump
    for row in query_res:
sqlite3.OperationalError: Could not decode to UTF-8 column ''INSERT INTO "sync_entities_metadata" VALUES('||quote("storage_key")||','||quote("metadata")||')'' with text 'INSERT INTO "sync_entities_metadata" VALUES(1,'v10����

I tried enabling conn.text_factory = bytes as a workaround, but now get a different error.

  File "foo.py", line 79, in dump_sqlite_db
    for line in conn.iterdump():
  File "/usr/lib/python3.11/sqlite3/dump.py", line 43, in _iterdump
    elif table_name.startswith('sqlite_'):
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: startswith first arg must be bytes or a tuple of bytes, not str

Linked PRs

• gh-108590: Fix sqlite3.iterdump for invalid unicode in text columns. #108657
• [3.12] gh-108590: Fix sqlite3.iterdump for invalid Unicode in TEXT columns (GH-108657) #108673
• [3.11] gh-108590: Fix sqlite3.iterdump for invalid Unicode in TEXT columns (GH-108657) #108674
• gh-108590: Fix sqlite3 iterdump() for table columns containing invalid Unicode sequences #108683
• gh-108590: Revert gh-108657 (commit 400a1cebc) #108686
• [3.11] gh-108590: Revert gh-108657 (commit 400a1cebc) (#108686) #108694
• gh-108590: Fix sqlite3.iterdump for invalid unicode in text columns and reproducability. #108695
• gh-108590: Improve sqlite3 docs on encoding issues and how to handle those #108699
• [3.12] gh-108590: Improve sqlite3 docs on encoding issues and how to handle those (GH-108699) #111324
• [3.11] gh-108590: Improve sqlite3 docs on encoding issues and how to handle those (GH-108699) #111325

[CorvinM]

I am not able to reproduce this on 3.11.5, could you provide an example database or the script/sql to create one that demonstrates the issue?

[dotysan]

Hi @CorvinM, thanks for taking the time to attempt to reproduce.

My use case is fiddling around with existing settings in a Google Chrome profile. Your mileage may vary.

But here's a simplified/synthesized test that should be reproducible...

$ cat foo.sql
BEGIN TRANSACTION;
CREATE TABLE foo (id INTEGER, data VARCHAR);
INSERT INTO foo VALUES(42,'a�');
COMMIT;
$ hexdump -C foo.sql 
00000000  42 45 47 49 4e 20 54 52  41 4e 53 41 43 54 49 4f  |BEGIN TRANSACTIO|
00000010  4e 3b 0a 43 52 45 41 54  45 20 54 41 42 4c 45 20  |N;.CREATE TABLE |
00000020  66 6f 6f 20 28 69 64 20  49 4e 54 45 47 45 52 2c  |foo (id INTEGER,|
00000030  20 64 61 74 61 20 56 41  52 43 48 41 52 29 3b 0a  | data VARCHAR);.|
00000040  49 4e 53 45 52 54 20 49  4e 54 4f 20 66 6f 6f 20  |INSERT INTO foo |
00000050  56 41 4c 55 45 53 28 34  32 2c 27 61 9f 27 29 3b  |VALUES(42,'a.');|
00000060  0a 43 4f 4d 4d 49 54 3b  0a                       |.COMMIT;.|
00000069

Notice a single non-ASCII/UTF byte in the data VARCHAR column.

Then do this to build the database:
sqlite3 foo.db <foo.sql
Everything works fine! The sqlite3 database is apparently restored and consistent.

But then in Python...

#! /usr/bin/env python3.11
"""https://github.com/python/cpython/issues/108590"""

import sqlite3

conn = sqlite3.connect('foo.db')
curs = conn.cursor()
curs.execute('SELECT * FROM foo')
for line in conn.iterdump():
    pass

It fails thusly:

 ./cpython-108590.py 
Traceback (most recent call last):
  File "cpython-108590.py", line 9, in <module>
    for line in conn.iterdump():
  File "/usr/lib/python3.11/sqlite3/dump.py", line 63, in _iterdump
    for row in query_res:
sqlite3.OperationalError: Could not decode to UTF-8 column ''INSERT INTO "foo" VALUES('||quote("id")||','||quote("data")||')'' with text 'INSERT INTO "foo" VALUES(42,'a�')'

[erlend-aasland]

I'm unable to reproduce this using the following:

import sqlite3
with sqlite3.connect(":memory:") as cx:
    cx.execute("CREATE TABLE foo (data VARCHAR)")
    cx.execute("INSERT INTO foo VALUES(?)", ["a\x9f"])
for row in cx.iterdump():
    print(row)
cx.close()

[erlend-aasland]

Apologies if I'm misunderstanding. Please advice if I should post elsewhere. But shouldn't iterdump() properly detect VARCHAR columns with binary data and output X'' strings instead of throwing an error? This is what sqlite3 .dump does.

The SQLite shell does not special case VARCHAR (take a look at shell.c in the SQLite sources); it simply ignores the unprintable character. On my computer, that is also the behaviour of iterdump.

[erlend-aasland]

Unable to reproduce on Debian or Ubuntu as well.

[erlend-aasland]

BTW, @dotysan, I see you are using the connection context manager. Note that the connection context manager does not close the database, it only makes sure that the transaction in the with body is committed; __exit__ implicitly executes COMMIT or ROLLBACK, depending on if an exception happened. So, from your use case, it seems to me you would be better off by using itertools.closing.

[CorvinM]

I am able to reproduce using @dotysan's foo.sql (recreated from hex dump, my terminal/editor was giving problems trying to "fix" the invalid encoding). The sqlite3 CLI both accepts it and reproduces it in a dump so I do believe its a python sqlite bug (rather than a corrupt db). Usually there is not supposed to be invalid encoded characters in a VARCHAR/TEXT field but it seems sqlite takes the garbage in -> garbage out policy. Working on a patch for this.

Heres an alternative hexdump of foo.sql that can be imported a bit easier (with xxd -r):

00000000: 4245 4749 4e20 5452 414e 5341 4354 494f  BEGIN TRANSACTIO
00000010: 4e3b 0a43 5245 4154 4520 5441 424c 4520  N;.CREATE TABLE 
00000020: 666f 6f20 2869 6420 494e 5445 4745 522c  foo (id INTEGER,
00000030: 2064 6174 6120 5641 5243 4841 5229 3b0a   data VARCHAR);.
00000040: 494e 5345 5254 2049 4e54 4f20 666f 6f20  INSERT INTO foo 
00000050: 5641 4c55 4553 2834 322c 2761 9f27 293b  VALUES(42,'a.');
00000060: 0a43 4f4d 4d49 543b 0a                   .COMMIT;.

[erlend-aasland]

We can't use a hexdump for a regression test; please provide a Python only reproducer.

[erlend-aasland]

@CorvinM, something like this should suffice:

import sqlite3
SCRIPT = """
    CREATE TABLE foo (data VARCHAR);
    INSERT INTO foo VALUES('a\x9f');
"""
with sqlite3.connect(":memory:") as cx:
    cx.executescript(SCRIPT)
for row in cx.iterdump():
    print(row)
cx.close()

[CorvinM]

@CorvinM, something like this should suffice:

import sqlite3
SCRIPT = """
    CREATE TABLE foo (data VARCHAR);
    INSERT INTO foo VALUES('a\x9f');
"""
with sqlite3.connect(":memory:") as cx:
    cx.executescript(SCRIPT)
for row in cx.iterdump():
    print(row)
cx.close()

Unfortunately I don't think I can make it that pretty unless there is a API function that lets us send a query of bytes instead of str that I'm not aware of. The encode() down the chain is causing a problem as its turning the '\x9f' into a '\xc2\x9f' before being handed to sqlite.

To show the encode() problem:

import sqlite3
SCRIPT = """
    CREATE TABLE foo (data VARCHAR);
    INSERT INTO foo VALUES('a\x9f');
"""
with sqlite3.connect("out.db") as cx:
    cx.executescript(SCRIPT)

$ sqlite3 out.db 'SELECT data from foo;' | xxd
00000000: 61c2 9f0a                                a...

Regardless, this works to show the original issue (albeit nasty):

import sqlite3
import gzip

"""
# Created from the following shell commands:
# hex dump required because of the invalid unicode 9f at offset 3a
# cant survive most terminals/editors or python encode()
xxd -r << EOF | sqlite3 foo.db
00000000: 4352 4541 5445 2054 4142 4c45 2066 6f6f  CREATE TABLE foo
00000010: 2028 6461 7461 2056 4152 4348 4152 293b   (data VARCHAR);
00000020: 0a49 4e53 4552 5420 494e 544f 2066 6f6f  .INSERT INTO foo
00000030: 2056 414c 5545 5328 2761 9f27 293b        VALUES('a.');
EOF

python -c 'import sqlite3; import gzip; print(gzip.compress(sqlite3.connect("foo.db").serialize()))'
"""
dbfile_gz = b"\x1f\x8b\x08\x00'7\xeed\x02\xff\xed\xd71\n\xc2@\x14\x84\xe1\xb7K\xb0\x13\x95\x14\xb6[j#\x88\x17p\r\x01\xc14\xc6`\xbf\xd1\x04\x04eA\xf6>\x9e\xc8\x0bY\xb9\xa266\x16v\xf2\x7f\xcc\x14\x0f\xde\x05f\xb3.\x0e\xa11\xad?\x9f\\03\xe9\x8bR27FD\xf4\xabo*6\xf9\xb8\xbf\xd12\xd9I\xf7\xf1\xdc\xbbJ\x0c\x00\x00\x00\x00\x00\xf8\xd5Tu\x86i\xaaV\xc1\xd5\xc7\xa6\xf5>Fgen\xab\xdcTvQ\xe4q\xe6{3\xda\xbb\xe0\xcc\xd6\x96\xd9\xd2\x96\xe3\xe76\xbfI\x0c\x00\x00\x00\x00\x00\xf8;\x89\xd2\x03w\xb9\x03\xef\xd0\xa6\xa3\x00 \x00\x00"

with sqlite3.connect(":memory:") as cx:
    cx.deserialize(gzip.decompress(dbfile_gz))
    for line in cx.iterdump():
        print(line)
cx.close()

[erlend-aasland]

Hm, yes I noticed this, @CorvinM. I can reproduce using the hexdump.