Background

The goal: coolqlite should feel like a “next-generation” sqlite3 module.

What You Should Understand About SQL & Python First

SQL Injection

You may skip this section if you’re already familiar with the concept.

The OWASP Top 10 “represents a broad consensus about the most critical security risks to web applications”.

“Injection”, including SQL injection, has been on it since it began in 2003, never falling below 6th place.

The short version: SQL queries are strings. Sometimes those queries need to change based on user input, like taking the name of a student in a form and inserting it into a Students table.

The naïve approach is to just stick the data in the query with quotes around the string. But that’s not good enough:

The famed XKCD 327, “Little Bobby Tables”.
This comic even birthed a site dedicated to preventing sql injection.

Let’s demonstrate the issue using some python:

Danger

def create_add_student_query(student: str) -> str:
    return f"insert into Students(name) values ('{student}');"

create_add_student_query("Normal Name")

This would give us:

insert into Students(name) values ('Normal Name');

But suppose someone gave the name "Robert'); DROP TABLE Students; --"?

That would result in:

insert into Students(name) values ('Robert'); DROP TABLE Students; --');

That’s two SQL statements: an insert and a drop-table, followed by a comment.

There are two ways to deal with this:

1. Escaping, where you use a provided library function to properly quote the input.
   If you need to do this manually, there’s a risk of forgetting.

2. Parameter binding, where you define spots to substitute your inputs.
   This seems to be preferred nowadays.

One annoying thing about parameter binding is that you either do positional parameter binding and risk mixing up the positions, or use named binding and have to repeat yourself.

The stdlib sqlite library uses parameter binding, and it looks like this:

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("""create table Debts
    ( borrower TEXT NOT NULL
    , lender TEXT NOT NULL
    , amount INTEGER NOT NULL DEFAULT 0
    ) strict""").close()

# paul owes peter money!
peter = "peter"
paul = "paul"

# positional
db.execute(
    "insert into Debts(borrower, lender) values (?, ?)",
    (peter, paul)).close()
# wait, did we mix up the order there?

# let's do named binding to avoid this!
borrower = paul
lender = peter

db.execute("""insert into
    Debts(borrower, lender)
    values (:borrower, :lender)""",
    dict(borrower=borrower, lender=lender)
    ).close()
# We had to write the words "borrower" and "lender"
# four times each. Cumbersome.

Template Strings

Before, we used python’s f-strings to interpolate text into a database query.

Python 3.14 added “template strings”, or t-strings. They look very similar, but t-strings return a string.templatelib.Template which can be inspected:

>>> string = "stringy"
>>> f"f-{string}"
'f-stringy'
>>> t"t-{string}"
Template(strings=('t-', ''), interpolations=(Interpolation('stringy', 'string', None, ''),))

Now we can combine the best parts of quoting/escaping (it’s obvious where the values come from, not repeating yourself), with the best parts of parameter binding (enforcing non-injection).

This is the approach coolqlite takes.

You can now move on to Usage unless you want to know about the history and philosophy of coolqlite.

The Pre-Recipe Life Story

Origin

For a small, simple project I was working on, I wanted to just use sqlite and not think about all the various setup steps necessary for other libraries.

Luckily, python’s sqlite3 module was right there! Except… there were parts of those libaries I missed:

• easily binding parameters, correctly

• easily converting between python and sqlite datatypes

• convenience methods for validating the number of rows you got

And because it had some important datetime logic, including the pernicious case of future events, I also wanted to make sure my datetime logic was sound, and correctness was enforced.

So I made a little helper module that snowballed into this.

Philosophy

0. Programming should be fun, but remember it’s a tool.

1. Using SQL shouldn’t be hard.

   • SQLite solves a lot of problems other DBs make you think about.

2. Good tools help you “fall into the pit of success”.

3. Good tools make complex things manageable, but don’t hide essential complexity.

   • This might mean embracing “explicit is better than implicit”.

4. We should make the line between the database and the programming language as seamless as possible.

5. Question what’s inherent complexity versus an implementation detail.

6. Correctness matters. “Worse is better” is worse if correctness is sacrificed.

7. Convention over configuration.

   • This means defaults that lead to a good first-time UX.

   • …but allow changing things that shouldn’t be set in stone.

   • Exposing switches is one thing, but replacing parts is more flexible.