2

I created a SQLite3 database and protected it with a password ("test") thanks to the application DB browser for SQLite. In order to connect to my database via Python, I need to provide the password but I can't figure out how to do that. I tried the following code:

conn=sqlite3.connect("mydatabase.db", Password="test")
cur=conn.cursor()

EDIT:

My SQLite3 database has been encrypted with SQLCipher (see image). enter image description here If I run the following code:

conn=sqlite3.connect("mydatabase.db")
cur=conn.cursor()

I get this error:

sqlite3.DatabaseError: file is encrypted or is not a database

How can I pass the password in order to connect with my db via Python?

EDIT 2

Here a brief summary of what I try to achieve. I am developing an application with Python 3 requiring a pre-populated database but this database needs to be protected with a password. After extensive research, it seems complicated to connect an encrypted SQLite3 database via Python 3. A library calls pysqlcipher exists but only for Python 2.7. My next question will be maybe too broadly and I apology in advance. Is there another portable database that exists allowing me to protect it with a password and still get access to Python? Another idea that I have in mind in order to troubleshoot my problem is to use the zipfile library. This link mentions that the zipfile module does not support encryption but it’s not clear if encryption refers to the SQLite3 database or to the zip file. The idea would be to zip my unprotected DB into a protected zip file as it seems I can do that (link). The goal of this edit is to get new ideas on how to solve my problem. Thanks

H. Dave
  • 549
  • 3
  • 9
  • 26
  • Try Jonathan's answer here: https://stackoverflow.com/questions/58964763/using-sqlcipher-in-python-the-easy-way Worked for me – Naila Nov 17 '21 at 17:11

2 Answers2

5

If your database is encrypted with SqlCipher, you need to install sqlcipher in your SO Windows: Download

Linux: sudo pacman -S sqlcipher or 

sudo apt-get install sqlcipher

After you need the pysqlcipher3 lib: pip install pysqlcipher3

See: https://github.com/rigglemania/pysqlcipher3

my sample code is:

from pysqlcipher3 import dbapi2 as sqlite3


class Database(object):
    def __init__(self, dbname):
        self.dbname = dbname

    def connDB(self):
        self.conn = sqlite3.connect(self.dbname)
        self.cursor = self.conn.cursor()
        self.cursor.execute("PRAGMA key='mypassword'")

    def createDB(self):
        self.connDB()
        self.cursor.execute(
            '''
            CREATE TABLE IF NOT EXISTS users (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            login TEXT NOT NULL,
            passwd TEXT);
            '''
        )

        self.cursor.execute(
            '''
            INSERT INTO users (name, login, passwd)
            VALUES ("Admininstrator", "admin", "12345")
            '''
        )
        self.conn.commit()
        self.conn.close()

    def queryDB(self, sql):
        self.connDB()
        self.cursor.execute(sql)

        if sql[0:6].lower() == 'select':
            result = self.cursor.fetchall()
            self.conn.close()
            return result
        else:
            self.conn.commit()
            self.conn.close()
Henrique Miranda
  • 63
  • 1
  • 8
  • Hello freind. I have a problem, I tried to install in linux apt-get install sqlcipher worked ok, so when I try to install using pip install pysqlcipher3 I got error. In other hand, I tried downloading from your windows version link. But I'm not sure how to do that. Please could you explain to me how to install in Windows? – GSandro_Strongs Oct 15 '21 at 16:02
3

You need the SQLCipher module to read that database. The default SQLite3 module has no support for that. See https://github.com/sqlitebrowser/sqlitebrowser/wiki/Encrypted-Databases

Dan D.
  • 73,243
  • 15
  • 104
  • 123