How does sqlite3 module determine value type

[mirlos]

Hi, I have a preexisting sqlite3 db with a teble of users with a varchar column for storing a password hash.

If the db is accessed through python only, I can insery bytes values into the column, and queries retireve it as bytes back.

However, if I insert new rows though the sqlite3 commands (to import users from elsewhere), the hashes come back as str values for those rows.

This can be reproduced by creating a db - value inserted as bytes object will be returned as bytes, value inserted through sqlite3 cli is a str, even in the same select statement. Making the query through sqlite3 cli does not create a visible difference between the two.

My goal is to see if one can supply the values in the DB using native sqlite3 cli in a way that the python module recognizes them as bytes.

$ python3
Python 3.8.10 (default, May 26 2023, 14:05:08)
[GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> db = sqlite3.connect('bytes.db')
>>> cur = db.cursor()
>>> cur.execute('create table user (username varchar, password_hash varchar)')
<sqlite3.Cursor object at 0x7f733ba219d0>
>>> cur.execute('insert into user values(?, ?)', ('one', b'$2b$12$BUjknFoiNwxATM6NPiYS6u6F/eXY7gGDUP2WFMGT3ePT3y6RvANJ.'))
<sqlite3.Cursor object at 0x7f733ba219d0>
>>> cur.execute('commit')
<sqlite3.Cursor object at 0x7f733ba219d0>
>>>
$ sqlite3 bytes.db 'insert into user values("two", "$2b$12$uRxu9lCXmotZUM3DcgoI1eS9ZStv7AR.cPoYL3YqCwrLYb1IgQ2w2")'
$ python3
Python 3.8.10 (default, May 26 2023, 14:05:08)
[GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> db = sqlite3.connect('bytes.db')
>>> cur = db.cursor()
>>> list(cur.execute('select * from user'))
[('one', b'$2b$12$BUjknFoiNwxATM6NPiYS6u6F/eXY7gGDUP2WFMGT3ePT3y6RvANJ.'),
('two', '$2b$12$uRxu9lCXmotZUM3DcgoI1eS9ZStv7AR.cPoYL3YqCwrLYb1IgQ2w2')]
>>>
$ sqlite3 -csv -header bytes.db 'select * from user'
username,password_hash
one,$2b$12$BUjknFoiNwxATM6NPiYS6u6F/eXY7gGDUP2WFMGT3ePT3y6RvANJ.
two,$2b$12$uRxu9lCXmotZUM3DcgoI1eS9ZStv7AR.cPoYL3YqCwrLYb1IgQ2w2

[Larz60+]

I'm having a hard time understanding your problem, but can at least understand it has something to do with data types.

You can use 'sqlite3' from command line to examine the schema of any (or all) tables and find out their data types.
see here for sqlite3 commands.

As far as access is concerned, it's kind of a personal thing as to which method you use.
I like SQLalchemy, and if you are interested, I have a simple tutorial (on this forum) for SQLalchemy here.

[mirlos]

Thanks for looking into this.

The problem is that apparently, the python sqlite module will store a bytes object in the DB as a blob, not text. even for columns declared as varchar in the schema. Sqlite is very loose about the stored value types and allows for different types to be stored in different rows of the same column. This means the module is able to convert the value back into a bytes object when running a select. This is also the same when using sqlalchemy on top.

But other tools, such as the sqlite3 command line tool, will always use text for these values, even when retrieving them - in shell world, there are no blobs. You can see the difference in the output of the example at the end, the python output has the hash for user 'one' be a bytes literal, whereas 'two' has it as a str object. In the final output in CLI, there is no difference, as the blob object is also a proper utf-8-encoded string sequence.

Thus what I needed to do was to use python for all handling of my database, and converting to bytes when I wanted the hashes inserted as blobs.