SQLite insert Null character

Question

So I'm writing a script in Python(2.7) that converts files and then stores the results in a SQlite DB.

For the Primary key I'm using the last 29 bytes of the file(as it contains a unique identifier). The problem is these unique identifier's contain null btyes(x00) so when I try to insert "40 00 00 00 00 00 00 00 00 3F F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 05" it just ends up as "40"(@).

How can I get SQlite to stop truncating this? I've tried to use the TEXT, BLOB, NULL, NONE, and sqlite3.binary(when inserting) but all return just "@".

Below is the statement I use for inserting and the test table I'm working with.

CREATE TABLE files (id blob PRIMARY KEY);
cur.execute("INSERT INTO files (id) VALUES (?)", (sqlite3.binary(tid), ))

EDIT: Okay so I'm just an idiot... it's all there I just never thought to SELECT statement it because in sqlite3 CLI it just looked like a "@"...(temp.txt contains just the last 29 bits of a file)

Python 2.7.13 (v2.7.13:a06454b1afa1, Dec 17 2016, 20:42:59) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect('kp_trans.db')
>>> cur = conn.cursor()
>>> with open("temp.txt", 'rb') as f:
...      cur.execute("SELECT * FROM files WHERE id = ?", (sqlite3.Binary(f.read()), ))
...      for row in cur:
...            print row[0]
...
<sqlite3.Cursor object at 0x02346920>
@          ?≡                    ☺ ♣
>>>

`INSERT INTO files (id) values("40 00 00 00 00 00 00 00 00 3F F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 05");` works for me. — Michael O., May 09 '17 at 16:06
But is it stored as blob or a string? I think if you want a blob it should be INSERT INTO files (id) values('X400000...') — varro, May 09 '17 at 16:11
Check what the database actually has by issuing: SELECT id, typeof(id), hex(id) from files; using the SQLite3 command line tool. — varro, May 09 '17 at 16:19
That was just an example in hex try b'\x40\x00\3f'(as it's read from a file in binary mode) it'll only insert the '\x40'. - http://i.imgur.com/pxN2bH2.png — waterflame, May 09 '17 at 16:26
Well, that's certainly not just 0x40, but it seems to be only 4 bytes rather than 29. — varro, May 09 '17 at 16:37

score 1 · Answer 1 · answered May 09 '17 at 16:21

If you contrive to read the contents of those files you speak of as bytes (rather than characters, using 'rb' in the open statement) and then to capture the last 29 bytes as a bytes, as in the following code, then I think sqlite may not mangle them.

>>> id = b'40\00\00\00\00\00\00\00\00\3F\F0\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00\01\00\05'
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('CREATE TABLE files (id blob PRIMARY KEY)')
<sqlite3.Cursor object at 0x0000000005600CE0>
>>> c.execute("INSERT INTO files (id) VALUES (?)", (id, ))
<sqlite3.Cursor object at 0x0000000005600CE0>
>>> for rec in c.execute('SELECT * FROM files'):
...     rec
...     
(b'40\x00\x00\x00\x00\x00\x00\x00\x00\x03F\\F0\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x05',)

Edit: I should also mention that sqlite3 has converted id to bytearray. You will need to take this into account.

>>> bytearray(id)
bytearray(b'40\x00\x00\x00\x00\x00\x00\x00\x00\x03F\\F0\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x05')

SQLite insert Null character

1 Answers1