Step 93. SQL Basics 2 — JOIN and Python Integration

Level 1 — Programming and the Inside of a Computer | Difficulty ★★★☆☆ | Estimated time: 3 hours

Prerequisites: CRUD and SQLite operations from Step 92, and Python basics from Steps 41–46.

• What you need: yesterday’s test.db (remaking it is fine if you deleted it) and Python. This chapter’s measurements were performed with Python 3.12’s built-in sqlite3 module — no separate installation is SQLite’s virtue.
• Caution: in the second half there’s an experiment where you deliberately write "dangerous code." Proceed only in the practice DB inside your own computer. Today’s highlight — watching with your own eyes the moment an input like ' OR '1'='1 changes an SQL statement.

Real services don’t cram data into a single table. Members go in the members table, orders in the orders table — stored separately. That’s to avoid the disaster of fixing all hundred of someone’s orders every time that member changes their address. But once tables are split, a question arises — how do you get "the list of items alice ordered"? The answer is today’s first star, JOIN. The second star is parameter binding — the official way to safely insert input values when executing SQL from Python, and its very reason for existence is SQL Injection defense. Today you’ll personally write both code that the attack works on and code that blocks it.

1. Learning Objectives

By the end of this chapter, you will be able to:

• JOIN two tables with an ON condition and query them
• Build queries that summarize tables with GROUP BY / COUNT / ORDER BY
• Handle a DB from Python with the sqlite3 module in the connect → execute → fetch → close rhythm
• Demonstrate the danger of string-concatenated queries with the ' OR '1'='1 experiment
• State in one sentence why parameter binding (?) blocks the same input

2. Background Knowledge — Today’s Tools and Concepts

Today’s Tools at a Glance

2-1. Normalization and JOIN — The Art of Splitting and Linking

The design philosophy of storing data "split apart without duplication" is called normalization (just learn the name for now). Since data is stored split apart, you have to link it back when querying. That link is the foreign key — like the orders table’s user_id column pointing at the members table’s id.

users:  id=2, name='alice'
orders: user_id=2, item='security book'
        user_id=2, item='mechanical keyboard'

JOIN follows these arrows and pastes the two tables into one sheet of results.

2-2. Python’s sqlite3 Module — The Program Becomes the Warehouse Keeper

Python has an SQLite driver built in by default. The pattern is always the same.

import sqlite3
conn = sqlite3.connect("test.db")   # call the warehouse
cur = conn.execute("SQL statement")  # issue a command
for row in cur: ...                  # receive results one line at a time
conn.close()                         # hang up

The work you typed by hand at the prompt yesterday is now done by a program thousands of times per second. Exactly this code is what runs behind a web service’s sign-up and login.

2-3. String Concatenation vs Binding — Today’s Showdown

There are two ways to build SQL with user input.

# Method A: string concatenation — dangerous!
sql = "SELECT * FROM users WHERE name='" + name + "'"

# Method B: parameter binding — safe
conn.execute("SELECT * FROM users WHERE name=?", (name,))

Method A welds the input into the sentence as part of it. What if the input contains a quote? As you saw yesterday, the sentence breaks — or changes. Method B prepares a ? slot and passes the input as data only — even if the input holds a quote, it’s treated as "just characters." Today we confirm this difference not as an error but as the success and failure of an attack.

3. Follow Along

3-1. A Second Table and Its Link

Input (open yesterday’s test.db):

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  item TEXT
);
INSERT INTO orders (user_id, item) VALUES (2, 'security book');
INSERT INTO orders (user_id, item) VALUES (2, 'mechanical keyboard');
INSERT INTO orders (user_id, item) VALUES (1, 'coffee beans');
INSERT INTO orders (user_id, item) VALUES (3, 'monitor');

How to read it: user_id points to whose order it is. 2 is alice, 1 is admin, 3 is bob (yesterday’s id numbers). Only alice has two entries.

Why: this completes storing-split-apart. From now on, the question "who bought what" can only be answered by crossing two tables.

3-2. JOIN — Two Tables into One Sheet

Input

SELECT users.name, orders.item
FROM users
JOIN orders ON users.id = orders.user_id;

Output (measured 2026-09-09):

alice|security book
alice|mechanical keyboard
admin|coffee beans
bob|monitor

How to read it: "JOIN users and orders, with the link (ON) users.id = orders.user_id." The condition after ON is the needle threading the two tables. In the result, each order sits side by side with its owner’s name.

Why: most queries in real services are JOINs. "My order list," "this post’s comments with the authors’ nicknames" — all are applications of this grammar.

3-3. Aggregation and Sorting — The Power to Summarize

Input

SELECT users.name, COUNT(*)
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.name
ORDER BY COUNT(*) DESC;

Output (measured 2026-09-09):

alice|2
bob|1
admin|1

How to read it: GROUP BY means "bundle the same names together"; COUNT(*) counts within each bundle. Finally, we sorted by count, largest first (DESC). Note that the order of bob and admin, who tie, isn’t fixed — in the measurement bob came first, but the order of ties can vary by environment. To settle ties too, add a second criterion like ORDER BY COUNT(*) DESC, users.name.

Why: this is log analysis’s basic pattern — "connection counts by IP," "error counts by hour" all share this structure. It’s the grammar that takes you one step up from an eye that reads tables to an eye that summarizes tables.

3-4. Handling the DB from Python — The Safe Way

Input (db_app.py)

import sqlite3

def find_user(name):
    conn = sqlite3.connect("test.db")
    rows = conn.execute(
        "SELECT id, name, is_admin FROM users WHERE name=?", (name,)
    ).fetchall()
    conn.close()
    return rows

print(find_user("alice"))
print(find_user("nobody"))

Output (measured 2026-09-09, python db_app.py):

[(2, 'alice', 0)]
[]

How to read it: ? is a placeholder, and the tuple (name,) after it is the value going into that slot. fetchall() brings the whole result as a list. A name that doesn’t exist gives an empty list [].

Why: this function is the prototype of login, search, and profile lookup. And the fact that the ? style is the standard — we’ll now prove with a contrast experiment.

3-5. Danger Experiment — The End of String Concatenation

Input (db_bad.py — educational vulnerable code, never deploy it anywhere):

import sqlite3

def find_user_bad(name):
    conn = sqlite3.connect("test.db")
    sql = "SELECT id, name, pw FROM users WHERE name='" + name + "'"
    print("SQL executed:", sql)          # observe what sentence it became
    rows = conn.execute(sql).fetchall()
    conn.close()
    return rows

print(find_user_bad("alice"))

Output (measured 2026-09-09):

SQL executed: SELECT id, name, pw FROM users WHERE name='alice'
[(2, 'alice', 'wonderland')]

It works normally. Now let’s feed in the attacker’s input. Change the last line like this.

print(find_user_bad("' OR '1'='1"))

Output (measured 2026-09-09):

SQL executed: SELECT id, name, pw FROM users WHERE name='' OR '1'='1'
[(1, 'admin', 'secret123'), (2, 'alice', 'wonderland'), (3, 'bob', 'builder99'), (4, 'charlie', 'choco789')]

How to read it: inside the sentence, the input became not a "string" but grammar. As OR '1'='1 got attached, the condition changed to "name is an empty string, OR 1 equals 1 (always true)," and the result is the whole table leaked — including the password column.

Why: this is the birthplace of SQL Injection. Feed the same input into the binding version from 3-4 — find_user("' OR '1'='1") returns an empty list [] (measured 2026-09-09). Because there’s no member by that name. You’ve just confirmed with your own eyes the difference between code that gets hit and code that blocks.

⚠️ Security connection: this experiment was performed entirely in a practice DB on my own computer. All attack practice stays in your own lab and legal platforms. Unauthorized attacks on real services are a crime. The moment you put this input string into a real website’s input field, it’s no longer an experiment — it’s an attack.

3-6. Make a Prediction — What If It Were a Login Function?

Prediction: say login was implemented in the vulnerable way we just saw.

sql = "SELECT * FROM users WHERE name='" + name + "' AND pw='" + pw + "'"

The attacker doesn’t know the password. What input in the ID field would neutralize the password check?

• (a) admin — the correct ID
• (b) admin'-- — a quote and a comment marker
• (c) 12345 — a common password guess

Check for yourself — building a function of this structure and feeding in (b), the output (measured 2026-09-09):

SQL executed: SELECT * FROM users WHERE name='admin'--' AND pw='idontknowanything'
[(1, 'admin', 'secret123', 1)]

The answer is (b). -- is SQL’s comment marker, so everything after it (the password-check part) gets ignored. The admin row was returned whole — admin login succeeded without a password.

Why it matters: this input is a historic attack that actually brought down countless services. And you now know it’s not a "magic incantation" but the logical result of a grammar collision.

3-7. Writing from Python — INSERT with Binding Too

It’s not just reading. The rule is the same when a program writes to the DB.

Input (db_add.py)

import sqlite3

def add_user(name, pw):
    conn = sqlite3.connect("test.db")
    conn.execute(
        "INSERT INTO users (name, pw, is_admin) VALUES (?, ?, 0)",
        (name, pw)
    )
    conn.commit()
    conn.close()

add_user("mallory", "pass123")

Output (measured 2026-09-09, querying SELECT name FROM users after running): mallory has been added at the end of the list.

How to read it: two things are new. First, after a write (INSERT/UPDATE/DELETE), conn.commit() — the stamp meaning "finalize these changes." Drop it and the changes can be rolled back when the connection closes. Second, ? binding is used in INSERT exactly the same way.

Why: the back side of a sign-up form is this function. Even if a user puts a malicious input like x'); DROP TABLE users;-- into the sign-up field, binding stores it as just "a member with a strange name." We actually confirmed this (measured 2026-09-09):

[('admin',), ('alice',), ('bob',), ('charlie',), ("x'); DROP TABLE users;--",)]
users table survived: 5 members

The table is intact, and the name was added as-is — an experiment confirming binding’s defensive power on the write path too.

4. Missions & Exercises

Mission — A Safe Mini Member Program + an Attack Demonstration Record

1. Write a member program in Python: 3 features — sign-up (INSERT·binding), login (SELECT·binding), member list (SELECT).
2. Add a feature that shows "order count per member" with JOIN.
3. Make the vulnerable version (string concatenation) as a separate file, and capture screens of both attacks ' OR '1'='1 and admin'-- succeeding. Mark # educational vulnerable code — never deploy anywhere in a comment at the very top of the file.
4. Capture the same inputs being blocked with "empty results" in the safe version.
5. Write SQL-injection-preview.md in your wiki — the two captures + a 3-line summary of "why binding blocks it."

Exercises

Exercise 1. Explain the role of ON users.id = orders.user_id in a JOIN, and state what happens if you drop this condition.

Exercise 2. In (name,), what happens if you drop the trailing comma? Explain why the comma is needed.

Exercise 3. Explain the process by which the input ' OR '1'='1 becomes "always true" in a string-concatenated query, writing out the completed SQL statement.

Exercise 4. Why does the binding version return an empty list for the same attack input? Unpack the meaning of "the input is treated as data, not grammar."

import sqlite3

DB = "test.db"

def signup(name, pw):
    conn = sqlite3.connect(DB)
    conn.execute("INSERT INTO users (name, pw, is_admin) VALUES (?, ?, 0)",
                 (name, pw))
    conn.commit()
    conn.close()

def login(name, pw):
    conn = sqlite3.connect(DB)
    rows = conn.execute(
        "SELECT id, name FROM users WHERE name=? AND pw=?", (name, pw)
    ).fetchall()
    conn.close()
    return "login success" if rows else "login failed"

def order_counts():
    conn = sqlite3.connect(DB)
    rows = conn.execute(
        "SELECT users.name, COUNT(*) FROM users "
        "JOIN orders ON users.id = orders.user_id "
        "GROUP BY users.name ORDER BY COUNT(*) DESC"
    ).fetchall()
    conn.close()
    return rows

6. Common Pitfalls & Fixes

Wall 1. The JOIN result is absurdly large (ballooning like multiplication)

Symptom (measured 2026-09-09): there are only 4 orders, yet SELECT COUNT(*) FROM users JOIN orders returns 16.

Cause: you dropped or got wrong the ON condition. A JOIN without a condition creates "every combination" (Cartesian product) — 4 members × 4 orders = 16 rows.

Fix: check that ON users.id = orders.user_id is correct. The link is JOIN’s life.

Wall 2. "Incorrect number of bindings" error with binding

Symptom (measured 2026-09-09):

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

Cause: the ? count and the value count don’t match. Dropping the trailing comma in (name,) is also a common accident — without the comma it’s not a tuple, just parentheses.

Fix: count the ?s and match them to the tuple’s element count. Even with a single value, keep the (value,) form.

Wall 3. "database is locked" appears from Python

Symptom: the program can’t open the DB and says it’s locked.

Cause: the program attempted a write while you had test.db open in the sqlite3 interactive shell. SQLite doesn’t allow concurrent writes.

Fix: close the shell with .exit and run again. Also check the program side didn’t drop conn.close().

Wall 4. Feeding in the attack input just gives an error

Symptom: ' OR '1'='1 gives a syntax error.

Cause: depending on where it gets concatenated, the quotes may not pair up. You need to check whether the function structure exactly matches the chapter’s example.

Fix: the print("SQL executed:", sql) line must be there — seeing what sentence actually got built is the core of this experiment. Read the sentence and trace the quote pairs with your finger.

Wall 5. I did an INSERT but opening it later, nothing was saved

Symptom: the program finished without errors, but the row isn’t in the DB.

Cause: you dropped conn.commit(). A write is unfinalized until commit.

Fix: always conn.commit() after INSERT/UPDATE/DELETE. Memorize the rhythm "after a write, the stamp."

7. Summary

Today’s Concepts

Today’s Commands

An Instinct More Important Than Commands

A string-concatenated query turns input into grammar; parameter binding confines input to data — that difference is injection’s success or failure. ' OR '1'='1 and admin'-- aren’t incantations but the results of grammar collisions. Someone who knows the principle has nothing to fear.

And this structure isn’t unique to SQL. The root of "input becoming the grammar of a command" is common to the whole injection family — command injection, XSS, and more; mixing code and data in one bowl is the root. Today you wrote "vulnerable code" and "safe code" with the same hands. A vulnerability isn’t theory — it’s inside code I wrote myself. This contrast experience is the true starting point of web security.

Once every box is checked, Step 93 is complete.