def setUp(self):
try:
os.remove(get_db_path())
except OSError:
pass
self.con1 = sqlite.connect(get_db_path(), timeout=0.1)
self.cur1 = self.con1.cursor()
self.con2 = sqlite.connect(get_db_path(), timeout=0.1)
self.cur2 = self.con2.cursor()
def CheckConvertTimestampMicrosecondPadding(self):
"""
http://bugs.python.org/issue14720
The microsecond parsing of convert_timestamp() should pad with zeros,
since the microsecond string "456" actually represents "456000".
"""
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("CREATE TABLE t (x TIMESTAMP)")
# Microseconds should be 456000
cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")
# Microseconds should be truncated to 123456
cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")
cur.execute("SELECT * FROM t")
values = [x[0] for x in cur.fetchall()]
self.assertEqual(values, [
datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
])
def CheckSetIsolationLevel(self):
"""
See issue 3312.
"""
con = sqlite.connect(":memory:")
self.assertRaises(UnicodeEncodeError, setattr, con,
"isolation_level", u"\xe9")
def CheckCollationIsUsed(self):
if sqlite.version_info < (3, 2, 1): # old SQLite versions crash on this test
return
def mycoll(x, y):
# reverse order
return -cmp(x, y)
con = sqlite.connect(":memory:")
con.create_collation("mycoll", mycoll)
sql = """
select x from (
select 'a' as x
union
select 'b' as x
union
select 'c' as x
) order by x collate mycoll
"""
result = con.execute(sql).fetchall()
if result[0][0] != "c" or result[1][0] != "b" or result[2][0] != "a":
self.fail("the expected order was not returned")
con.create_collation("mycoll", None)
try:
result = con.execute(sql).fetchall()
self.fail("should have raised an OperationalError")
except sqlite.OperationalError, e:
self.assertEqual(e.args[0].lower(), "no such collation sequence: mycoll")
def CheckConnectionExecutemany(self):
con = sqlite.connect(":memory:")
con.execute("create table test(foo)")
con.executemany("insert into test(foo) values (?)", [(3,), (4,)])
result = con.execute("select foo from test order by foo").fetchall()
self.assertEqual(result[0][0], 3, "Basic test of Connection.executemany")
self.assertEqual(result[1][0], 4, "Basic test of Connection.executemany")
def CheckFailedOpen(self):
YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
try:
con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
except sqlite.OperationalError:
return
self.fail("should have raised an OperationalError")
def CheckCreateCollationNotCallable(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("X", 42)
self.fail("should have raised a TypeError")
except TypeError, e:
self.assertEqual(e.args[0], "parameter must be callable")
def CheckCreateCollationNotAscii(self):
con = sqlite.connect(":memory:")
try:
con.create_collation("collä", cmp)
self.fail("should have raised a ProgrammingError")
except sqlite.ProgrammingError, e:
pass
def CheckNullCharacter(self):
# Issue #21147
con = sqlite.connect(":memory:")
self.assertRaises(ValueError, con, "\0select 1")
self.assertRaises(ValueError, con, "select 1\0")
cur = con.cursor()
self.assertRaises(ValueError, cur.execute, " \0select 2")
self.assertRaises(ValueError, cur.execute, "select 2\0")
def setUp(self):
self.con = sqlite.connect(":memory:")
try:
del sqlite.adapters[int]
except:
pass
sqlite.register_adapter(int, ObjectAdaptationTests.cast)
self.cur = self.con.cursor()
def createDatabase(self, encrypt=True):
conn = sqlite.connect(self.db)
if encrypt:
self.setPassword(conn, self.password)
conn.execute('create table tbl(col text)')
conn.execute("insert into tbl values('data')")
conn.commit()
conn.close()
def setUp(self):
self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
self.cur = self.con.cursor()
self.cur.execute("create table test(x foo)")
sqlite.converters["FOO"] = lambda x: "[%s]" % x
sqlite.converters["BAR"] = lambda x: "<%s>" % x
sqlite.converters["EXC"] = lambda x: 5 // 0
sqlite.converters["B1B1"] = lambda x: "MARKER"
def CheckScriptSyntaxError(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript("create table test(x); asdf; create table test2(x)")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def CheckScriptErrorNormal(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
raised = False
try:
cur.executescript("create table test(sadfsadfdsa); select foo from hurz;")
except sqlite.OperationalError:
raised = True
self.assertEqual(raised, True, "should have raised an exception")
def CheckPragmaSchemaVersion(self):
# This still crashed pysqlite <= 2.2.1
con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
try:
cur = self.con.cursor()
cur.execute("pragma schema_version")
finally:
cur.close()
con.close()
def assertDatabaseError(self, password):
conn = sqlite.connect(self.db)
self.setPassword(conn, password)
try:
col_value = self.queryData(conn)
self.assertIsNone(col_value)
except sqlite.DatabaseError as ex:
self.assertEqual('file is encrypted or is not a database', str(ex))
finally:
conn.close()
def CheckClosedCall(self):
con = sqlite.connect(":memory:")
con.close()
try:
con()
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckClosedCurExecute(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
con.close()
try:
cur.execute("select 4")
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckUnicodeConnect(self):
"""
With pysqlite 2.4.0 you needed to use a string or a APSW connection
object for opening database connections.
Formerly, both bytestrings and unicode strings used to work.
Let's make sure unicode strings work in the future.
"""
con = sqlite.connect(u":memory:")
con.close()
def CheckClosedSetProgressCallback(self):
con = sqlite.connect(":memory:")
con.close()
def progress(): pass
try:
con.set_progress_handler(progress, 100)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckClosedCreateFunction(self):
con = sqlite.connect(":memory:")
con.close()
def f(x): return 17
try:
con.create_function("foo", 1, f)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckPragmaAutocommit(self):
"""
Verifies that running a PRAGMA statement that does an autocommit does
work. This did not work in 2.5.3/2.5.4.
"""
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table foo(bar)")
cur.execute("insert into foo(bar) values (5)")
cur.execute("pragma page_size")
row = cur.fetchone()
def CheckClearTraceCallback(self):
"""
Test that setting the trace callback to None clears the previously set callback.
"""
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.set_trace_callback(None)
con.execute("create table foo(a, b)")
self.assertFalse(traced_statements, "trace callback was not cleared")
def CheckTraceCallbackUsed(self):
"""
Test that the trace callback is invoked once it is set.
"""
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.execute("create table foo(a, b)")
self.assertTrue(traced_statements)
self.assertTrue(any("create table foo" in stmt for stmt in traced_statements))
def CheckClosedSetAuthorizer(self):
con = sqlite.connect(":memory:")
con.close()
def authorizer(*args):
return sqlite.DENY
try:
con.set_authorizer(authorizer)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def CheckScriptStringSql(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript("""
-- bla bla
/* a stupid comment */
create table a(i);
insert into a(i) values (5);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 5)
def CheckCollationRegisterTwice(self):
"""
Register two different collation functions under the same name.
Verify that the last one is actually used.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", cmp)
con.create_collation("mycoll", lambda x, y: -cmp(x, y))
result = con.execute("""
select x from (select 'a' as x union select 'b' as x) order by x collate mycoll
""").fetchall()
if result[0][0] != 'b' or result[1][0] != 'a':
self.fail("wrong collation function is used")
def businessdict():
con = sqlite.connect('db/db1.db')
con.row_factory = sqlite.Row
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute('select * from users where business = ?;',
(session['business'], ))
result = cur.fetchall()
try:
con.close()
except:
pass
return result
def lookupmailserver():
business = str(session['business'])
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
serverlist = []
for row in cur.execute(
'select name from mailconfig where business = (?) OR share = 1;',
(business, )):
serverlist.append(row[:][0])
con.close()
return serverlist
def CheckScriptStringUnicode(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.executescript(u"""
create table a(i);
insert into a(i) values (5);
select i from a;
delete from a;
insert into a(i) values (6);
""")
cur.execute("select i from a")
res = cur.fetchone()[0]
self.assertEqual(res, 6)
def lookuptemplates():
business = str(session['business'])
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
templatelist = []
for row in cur.execute(
'select name from templates where business = (?) or shared = 1;',
(business, )):
templatelist.append(row[:][0])
con.close
return templatelist
def lookupadmin():
business = str(session['business'])
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'select username from users where notify = 1 and business = (?);',
(business, ))
admins = cur.fetchone()
admins = admins[0]
con.close
return admins
def lookupfirstname(username):
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute('select firstname from users where username = (?);',
username)
try:
validusername = cur.fetchone()
return validusername
except:
validusername = False
return validusername
def logpwreset(username):
timestamp = (datetime.now())
timestamp = timestamp.strftime("%m/%d/%Y %I:%M:%S %p")
timestamp = timestamp.replace(' ', '-')
print(timestamp)
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'insert into passreset (username, date) values ((?), (?));',
(username, timestamp))
con.close()
def detect_sqlcipher_version() -> int:
"""Returns the major part of the version of the system's sqlcipher package"""
conn = sqlcipher.connect(':memory:') # pylint: disable=no-member
query = conn.execute('PRAGMA cipher_version;')
version = query.fetchall()[0][0]
match = re.search(r'(\d+).(\d+).(\d+)', version)
if not match:
raise ValueError(f'Could not process the version returned by sqlcipher: {version}')
sqlcipher_version = int(match.group(1))
conn.close()
return sqlcipher_version
def setUp(self):
self.con = sqlite.connect(":memory:")
self.con.executescript("""
create table t1 (c1, c2);
create table t2 (c1, c2);
insert into t1 (c1, c2) values (1, 2);
insert into t2 (c1, c2) values (4, 5);
""")
# For our security test:
self.con.execute("select c2 from t2")
self.con.set_authorizer(self.authorizer_cb)
def CheckStatementFinalizationOnCloseDb(self):
# pysqlite versions <= 2.3.3 only finalized statements in the statement
# cache when closing the database. statements that were still
# referenced in cursors weren't closed an could provoke "
# "OperationalError: Unable to close due to unfinalised statements".
con = sqlite.connect(":memory:")
cursors = []
# default statement cache size is 100
for i in range(105):
cur = con.cursor()
cursors.append(cur)
cur.execute("select 1 x union select " + str(i))
con.close()
def reguserlookup():
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
con.row_factory = sqlite.Row
userquery = []
for row in cur.execute(
'select username from users where business = (?) and role = "user";',
(session['business'], )):
userquery.append(row[:][0])
con.close()
return userquery
def CheckStatementFinalizationOnCloseDb(self):
# pysqlite versions <= 2.3.3 only finalized statements in the statement
# cache when closing the database. statements that were still
# referenced in cursors weren't closed an could provoke "
# "OperationalError: Unable to close due to unfinalised statements".
con = sqlite.connect(":memory:")
cursors = []
# default statement cache size is 100
for i in range(105):
cur = con.cursor()
cursors.append(cur)
cur.execute("select 1 x union select " + str(i))
con.close()
def CheckCollationRegisterTwice(self):
"""
Register two different collation functions under the same name.
Verify that the last one is actually used.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", cmp)
con.create_collation("mycoll", lambda x, y: -cmp(x, y))
result = con.execute("""
select x from (select 'a' as x union select 'b' as x) order by x collate mycoll
""").fetchall()
if result[0][0] != 'b' or result[1][0] != 'a':
self.fail("wrong collation function is used")
def deleteDBrow(rowid, business, email, date):
con = sqlite.connect('db/db1.db')
cur = con.cursor()
with con:
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'delete from phishsched where id = (?) and business = (?);', (
rowid,
session['business'],
))
con.close()
dellist.append('Scheduled send for: ' + email + ' at' + date +
' - deleted!')
def getlastsent():
con = sqlite.connect('db/db1.db')
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'select * from (select id, department, username, template, mailname, bitly, admin, sentdate, scheduler, type from phishsched where business = (?) and sentdate != "none" and activetime = "none" ORDER BY ID DESC LIMIT 10) order by id asc;',
(session['business'], ))
lastsent = cur.fetchall()
try:
con.close()
except:
pass
return lastsent
def checkpassword():
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
try:
cur.execute('select password from users where username = (?);',
(session['username'], ))
passwordstatus = cur.fetchone()
passwordstatus = 1
return passwordstatus
except:
passwordstatus = None
return passwordstatus
def templatestats(template):
business = str(session['business'])
business = business.replace('[', '')
business = business.replace(']', '')
con = sqlite.connect('db/db1.db')
con.row_factory = dict_factory
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'select * from phishsched where business = (?) and template = (?);',
(business, template))
tempstats = cur.fetchall()
con.close()
return tempstats
def CheckProgressHandlerUsed(self):
"""
Test that the progress handler is invoked once it is set.
"""
con = sqlite.connect(":memory:")
progress_calls = []
def progress():
progress_calls.append(None)
return 0
con.set_progress_handler(progress, 1)
con.execute("""
create table foo(a, b)
""")
self.assertTrue(progress_calls)
def CheckClearHandler(self):
"""
Test that setting the progress handler to None clears the previously set handler.
"""
con = sqlite.connect(":memory:")
action = 0
def progress():
nonlocal action
action = 1
return 0
con.set_progress_handler(progress, 1)
con.set_progress_handler(None, 1)
con.execute("select 1 union select 2 union select 3").fetchall()
self.assertEqual(action, 0, "progress handler was not cleared")
def checkifexist(username):
con = sqlite.connect('db/db1.db')
with con:
username = ''.join(username)
cur = con.cursor()
cur.execute('PRAGMA key = '+dbkey+';')
con.row_factory = sqlite.Row
cur.execute('select EXISTS ( select placeholder from users where username = (?));', (username,))
if cur.fetchone()[0] == 1:
doesitexist = 1
else:
doesitexist = 0
con.close()
return doesitexist
def setdept(username):
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute("SELECT department FROM users where username = (?);",
(username, ))
con.close()
try:
loadept = cur.fetchone()
loadept = str(loadbusiness[0])
except:
loadept = 'None'
return loadept
def testClosedCreateFunction(self):
con = sqlite.connect(":memory:")
con.close()
def f(x):
return 17
try:
con.create_function("foo", 1, f)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def testClosedSetAuthorizer(self):
con = sqlite.connect(":memory:")
con.close()
def authorizer(*args):
return sqlite.DENY
try:
con.set_authorizer(authorizer)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def testClosedSetProgressCallback(self):
con = sqlite.connect(":memory:")
con.close()
def progress():
pass
try:
con.set_progress_handler(progress, 100)
self.fail("Should have raised a ProgrammingError")
except sqlite.ProgrammingError:
pass
except:
self.fail("Should have raised a ProgrammingError")
def loadmessages():
con = sqlite.connect('db/db1.db')
with con:
con.row_factory = sqlite.Row
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
"select id, sender, message, datesent from messages where username = (?) and business = (?);",
(
session['username'],
session['business'],
))
currentmessages = cur.fetchall()
return currentmessages
def CheckDeregisterCollation(self):
"""
Register a collation, then deregister it. Make sure an error is raised if we try
to use it.
"""
con = sqlite.connect(":memory:")
con.create_collation("mycoll", lambda x, y: (x > y) - (x < y))
con.create_collation("mycoll", None)
try:
con.execute("select 'a' as x union select 'b' as x order by x collate mycoll")
self.fail("should have raised an OperationalError")
except sqlite.OperationalError as e:
if not e.args[0].startswith("no such collation sequence"):
self.fail("wrong OperationalError raised")
def testClearTraceCallback(self):
"""
Test that setting the trace callback to None clears the previously set callback.
"""
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.set_trace_callback(None)
con.execute("create table foo(a, b)")
self.assertFalse(traced_statements, "trace callback was not cleared")
def scheduledb(username, phonedid, messagecontent, department, token,
confirmation):
print('sched')
admins = lookupadmin()
business = session['business']
con = sqlite.connect('db/db1.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA key = ' + dbkey + ';')
cur.execute(
'insert into phishsched ( type, bitly, sentdate, scheduler, phonedid, username, business, message, admin, department, token, smsconfirmation) values ( "sms", 1, datetime("now", "localtime"), ?, ?, ?, ?, ?, ?, ?, ?, ? );',
(session['username'], phonedid, username, business,
messagecontent, admins, department, token, confirmation))
con.close()
def testTypeMapUsage(self):
"""
pysqlite until 2.4.1 did not rebuild the row_cast_map when recompiling
a statement. This test exhibits the problem.
"""
SELECT = "select * from foo"
con = sqlite.connect(":memory:",detect_types=sqlite.PARSE_DECLTYPES)
con.execute("create table foo(bar timestamp)")
con.execute("insert into foo(bar) values (?)", (datetime.datetime.now(),))
con.execute(SELECT)
con.execute("drop table foo")
con.execute("create table foo(bar integer)")
con.execute("insert into foo(bar) values (5)")
con.execute(SELECT)
def testStatementReset(self):
# pysqlite 2.1.0 to 2.2.0 have the problem that not all statements are
# reset before a rollback, but only those that are still in the
# statement cache. The others are not accessible from the connection object.
con = sqlite.connect(":memory:", cached_statements=5)
cursors = [con.cursor() for x in range(5)]
cursors[0].execute("create table test(x)")
for i in range(10):
cursors[0].executemany("insert into test(x) values (?)", [(x,) for x in range(10)])
for i in range(5):
cursors[i].execute(" " * i + "select x from test")
con.rollback()
def _authenticate(self):
try:
connection = sqlite.connect(self._database_path)
cursor = connection.cursor()
cursor.row_factory = sqlite.Row
cursor.execute(f"PRAGMA key=\"x'{self.cipher_key}'\";")
cursor.execute('PRAGMA cipher_compatibility = 3;')
cursor.execute('SELECT * FROM Identity;').fetchone()
except sqlite.DatabaseError:
raise EnpassDatabaseError(
'Either the master password or the key file provided cannot decrypt '
'the database, or it is not a valid enpass 6 encrypted database.'
) from None
return connection, cursor
def CheckOpenUri(self):
if sqlite.sqlite_version_info < (3, 7, 7):
with self.assertRaises(sqlite.NotSupportedError):
sqlite.connect(':memory:', uri=True)
return
self.addCleanup(unlink, TESTFN)
with sqlite.connect(TESTFN) as cx:
cx.execute('create table test(id integer)')
with sqlite.connect('file:' + TESTFN, uri=True) as cx:
cx.execute('insert into test(id) values(0)')
with sqlite.connect('file:' + TESTFN + '?mode=ro', uri=True) as cx:
with self.assertRaises(sqlite.OperationalError):
cx.execute('insert into test(id) values(1)')
def CheckAutoCommit(self):
"""
Verifies that creating a connection in autocommit mode works.
2.5.3 introduced a regression so that these could no longer
be created.
"""
con = sqlite.connect(":memory:", isolation_level=None)
