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.

[erlend-aasland]

It does not matter if the repro is ugly; we cannot rely on the SQLite shell in the CI. Having a zipped database and loading it with deserialize is a better option (however, that won't work in 3.11, since deserialize isn't implemented there).

[dotysan]

For context, I stumbled across this while attempting to dump my Google Chrome profile settings using Python. They are the source of the binary data read/written into VARCHAR columns. I.e. not written from Python.

In particular %LocalAppData%/Google/Chrome/User Data/[Profile Dir]/Login Data is the db and the offending column is metadata in table sync_entities_metadata.

The workaround in my case is to bypass the Python module with...

    with open(dump_path, 'w') as dump:
        subprocess.run(['sqlite3', db_path, '.dump'], stdout=dump)

[dotysan]

...So I assume the rational for storing binary data in a sqlite3 VARCHAR is somewhere in the Chromium source..?

[CorvinM]

Found a much better way to get around the encoding issue.

import sqlite3
import gzip

SCRIPT = """
    CREATE TABLE foo (data VARCHAR);
    INSERT INTO foo VALUES (CAST(X'619f' AS VARCHAR));
"""

with sqlite3.connect(":memory:") as cx:
    cx.executescript(SCRIPT)
    for line in cx.iterdump():
        print(line)
cx.close()

[erlend-aasland]

You can drop import gzip, now ;)

[erlend-aasland]

BTW: Note that SQLite has the concept of type affinity. VARCHAR has the TEXT affinity. You can get the same result casting to TEXT. Also, the data type is optional in the table creation.

[erlend-aasland]

...So I assume the rational for storing binary data in a sqlite3 VARCHAR is somewhere in the Chromium source..?

SQLite will let you put pretty much any data type into any column. See SQLite quirks: https://sqlite.org/quirks.html.

[erlend-aasland]

@CorvinM wrote:

Working on a patch for this.

Thanks; I'm curious how you intend to solve it :)

[CorvinM]

@CorvinM wrote:

Working on a patch for this.

Thanks; I'm curious how you intend to solve it :)

I opened a draft PR #108657 for now. Would love to hear your thoughts if you want to take a peek.

[CorvinM]

Is it a correct fix? When you execute commands from the result of iterdump(), will you get a DB with the same content? Note that Unicode string \x9f is equivalent to bytes sequence \xc2\x9f.

Did you try to use conn.text_factory = lambda x: str(x, errors='surrogateescape') or conn.text_factory = lambda x: str(x, 'latin1') as a workaround?

Surrogate-escape seems to be different, I cannot recreate the same DB dump from iterdump when using it (its turing the \x9f into \udc9f) but can with the original chr() version. Adding a test to #108695 for dump reproducibility to check this.

[erlend-aasland]

@serhiy-storchaka, @CorvinM: As an alternative, I created #108699 in order to try to solve this in documentation only.

[serhiy-storchaka]

Let see. It is a complex issue, like every encoding issue.

The SQLite database can contain non UTF-8 sequences of bytes as a text. To work it around, there is a special mechanism: you can set text_factory to bytes, bytearray or custom factory which calls str() with other encoding or errors handler. What do you do with the result -- is your problem. If you get bytes or bytearray, you are responsible of decoding it. If you have a non-standard decoded string, you should use corresponding encoding when write it to a file or handle special characters in some way.

iterdump() has its own issues. It is not compatible with bytes and bytearray. When you use non-standard string decoding method, you can write it to file (using the correct encoding method) and feed to sqlite3 command, but you cannot feed it to execute().

So what can we do?

1. First of all, document the limitation of iterdump(), the workaround (seems, it is not well known) and the limitation of the workaround.
2. We can make execute() accepting non-UTF-8-encodable SQL statement and values. It should not be enabled by default, because it is easy to produce by accident a DB which breaks other programs. We can even use the same option to use surrogateescapes in results, it is more efficient than text_factory = lambda x: str(x, errors='surrogateescape').
3. We can make iterdump() converting all surrogateescapes into CAST(X'...' AS TEXT). Additional checks and transformations have a cost, so perhaps it should not be enabled by default. We can make iterdump() compatible with text_factory = bytes, but it will convert all TEXTs into BLOBs.

(2) and (3) are complex tasks and may require separate discussions about details. For example, should text_factory and new options be Connection or Cursor attributes, or parameters of execute()?

[erlend-aasland]

Let see. It is a complex issue, like every encoding issue.

+1

So what can we do?

1. First of all, document the limitation of iterdump(), the workaround (seems, it is not well known) and the limitation of the workaround.

IMO, this is the preferred solution. See #108699 for a draft docs update.

2. We can make execute() accepting non-UTF-8-encodable SQL statement and values. It should not be enabled by default, because it is easy to produce by accident a DB which breaks other programs. We can even use the same option to use surrogateescapes in results, it is more efficient than text_factory = lambda x: str(x, errors='surrogateescape').

I'm not sure this is a good idea. The sqlite3 module is a DB API (PEP-249) implementation, and the DB API dictates the param spec of execute(). If we change that, we'd be drifting further away from the DB API, which IMO is not desirable.

3. We can make iterdump() converting all surrogateescapes into CAST(X'...' AS TEXT). Additional checks and transformations have a cost, so perhaps it should not be enabled by default. We can make iterdump() compatible with text_factory = bytes, but it will convert all TEXTs into BLOBs.

Using CAST(X'...' AS TEXT) in the dump is a possibility. I'm not sure it is worth the added complexity, though.

[erlend-aasland]

I suggest we focus on 1. for now, the docs update. Well, in my opinion, docs are as hard to get right as encoding issues, so feel free to chime in 😄

[serhiy-storchaka]

I suggest we focus on 1. for now, the docs update. Well, in my opinion, docs are as hard to get right as encoding issues, so feel free to chime in 😄

At least it works with older versions of Python.

[hugovk]

I see lots of merged PRs for this issue. Can it be closed or is there more to be done?

[erlend-aasland]

The sqlite3 docs now includes a section about encoding issues, so it should hopefully be easier to avoid issues like this. However, @serhiy-storchaka is not satisfied with how the docs turned out and has flagged that he wants to improve them further. Perhaps those improvements should be done in a separate issue, though.

I'm fine with closing this.