Created
October 15, 2019 10:01
-
-
Save dshcherb/eb5f4edd94dce845047091f16a67f240 to your computer and use it in GitHub Desktop.
Writing null/zero (\x00, ^@) bytes to an sqlite3 DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import sqlite3 | |
def init_db(): | |
TEST_DB_FILE = 'testdb.sqlite' | |
db = sqlite3.connect(TEST_DB_FILE, isolation_level="EXCLUSIVE") | |
c = db.execute("BEGIN") | |
c.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name='snapshot'") | |
if c.fetchone()[0] == 0: | |
# Using TEXT colums is not a good idea because SQL expressions will have undefined result per sqlite3 documentation: | |
# https://www.sqlite.org/c3ref/bind_blob.html | |
# "If a non-negative fourth parameter is provided to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter must be the byte offset where the NUL terminator would occur assuming the string were NUL terminated. If any NUL characters occur at byte offsets less than the value of the fourth parameter then the resulting string value will contain embedded NULs. The result of expressions involving strings with embedded NULs is undefined." | |
db.execute("CREATE TABLE snapshot (handle TEXT PRIMARY KEY, data BLOB)") | |
db.commit() | |
return db | |
def write_data(db, key, data): | |
db.execute("REPLACE INTO snapshot VALUES (?, ?)", (key, sqlite3.Binary(data))) | |
def main(): | |
db = init_db() | |
write_data(db, 'somekey', b'about-to-truncate\x00this-will-be-trucated') | |
db.commit() | |
db.close() | |
if __name__ == '__main__': | |
main() | |
""" | |
After running this there will be correct data seen in the resulting file: | |
$ strings testdb.sqlite | |
SQLite format 3 | |
tablesnapshotsnapshot | |
CREATE TABLE snapshot (handle TEXT PRIMARY KEY, data BLOB)/ | |
indexsqlite_autoindex_snapshot_1snapshot | |
Zsomekeyabout-to-truncate | |
this-will-be-trucated | |
somekey | |
# less output will show this ("^@" corresponds to the null byte, see https://en.wikipedia.org/wiki/C0_and_C1_control_codes) | |
^Csomekeyabout-to-truncate^@this-will-be-trucated | |
However, sqlite3 client will truncate the data: | |
$ sqlite3 testdb.sqlite 'select * from snapshot;' | |
somekey|about-to-truncate | |
""" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment