Dec-04-2023, 08:02 PM
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
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