# Background

The goal: coolqlite should feel like a "next-generation" {mod}`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
[{abbr}`OWASP (Open Worldwide Application Security Project)` Top 10][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:

:::{figure} https://imgs.xkcd.com/comics/exploits_of_a_mom_2x.png
---
alt: |
   A school calls a parent, saying their son has broken
   their computers, in a sense.
   They ask if their son is really named
   (sql injection string here, to be explained later).
   The parent says "Oh, yes. Little Bobby Tables, we call him."
   The school complains that they've lost this year's student
   records,
   and the parent blames the school for not sanitizing
   database inputs.
---

The famed [XKCD 327](https://xkcd.com/327/), "Little Bobby Tables".  
This comic even birthed a
[site dedicated to preventing sql injection](https://bobby-tables.com/).
:::

Let's demonstrate the issue using some python:

:::{danger}

```python
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:

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

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

That would result in:

```sql
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:

```python
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.
```

[owasp top 10]: https://owasp.org/www-project-top-ten/

### Template Strings

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

Python 3.14 added ["template strings"][template-strings-docs],
or `t-strings`.
They look very similar, but t-strings return a
{class}`string.templatelib.Template` which can be inspected:

```python
>>> 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.

[template-strings-docs]: https://docs.python.org/3/library/string.templatelib.html

You can now move on to [Usage](./usage.md)
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 {mod}`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][future-timezone-blog],
I also wanted to make sure my datetime logic was sound,
and correctness was enforced.

[future-timezone-blog]: https://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html

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. <!-- six sevennn haha -->
   - 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.