Step 92. SQL Basics 1 — Talking to a Database (CRUD)

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

Prerequisites: basic terminal operations (Steps 18–20).

What you need: sqlite3. On Linux, sudo apt install sqlite3 -y; on macOS, brew install sqlite3; on Windows, use WSL or download the prebuilt tools from sqlite.org. This chapter’s measurements were performed on sqlite3 3.45.1 in WSL (Ubuntu 24.04).
Caution: today’s exercises are 100% safe. Everything happens in practice files inside your own computer. SQL is a command language shaped like English sentences — "can you read it" comes before memorization.

A web service’s outer appearance is HTML and CSS, but its innards — the member list, posts, comments, and passwords — almost all live in a warehouse called a database (DB). And the language for talking to that warehouse keeper is SQL. For you studying security, the importance of SQL converges to one thing: SQL Injection — an attack where user input squeezes into an SQL statement and changes the command — is the flagship of web hacking, and without knowing SQL, this attack can’t even begin. Whether defending or attacking, only someone who knows "what normal SQL looks like" can recognize "abnormal." Today, we get that normal into our hands.

1. Learning Objectives

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

Enter sqlite3, create a table, and exit
Type the four CRUD statements (CREATE/INSERT·SELECT·UPDATE·DELETE) without the book
Pick out only the rows you want with WHERE, LIKE, ORDER BY, and LIMIT
Size up an unfamiliar DB file within 30 seconds using dot commands (.tables, .schema, .exit)
Explain why "UPDATE/DELETE without WHERE" and "quote collisions" are dangerous

2. Background Knowledge — Today’s Tools and Concepts

Today’s Tools at a Glance

Category	Details
Language/environment	The sqlite3 command-line tool (common across WSL/Linux/macOS/Windows)
Today’s commands	`CREATE TABLE`, `INSERT INTO`, `SELECT`, `UPDATE`, `DELETE`, `WHERE`, `LIKE`, `ORDER BY`, `LIMIT`, dot commands (`.tables`·`.schema`·`.exit`)
Concepts needed	Table, row, column, PRIMARY KEY, single quotes for strings, semicolons
Today’s artifacts	A mini board warehouse `board.db` and a wiki summary document

2-1. Relational Databases — A World of Tables

The kind we handle today is the relational database, which stores data in spreadsheet-like tables. Three terms are enough.

Table — one sheet. E.g., users (the member table)
Row — one horizontal line of the table, one record. E.g., one member
Column — the kind of a vertical cell, an attribute. E.g., name, password

"Add a row to the users table" is the same sentence as "register one member." That’s exactly what happens behind a real service’s sign-up button.

2-2. SQLite — A DB Without Even a Server

The database world has giant players (MySQL, PostgreSQL), but today we use SQLite. There’s no server installation or configuration, and the whole DB is a single file (test.db). It’s built into Python by default, so the next chapter’s Python integration continues right on, and its syntax is standard SQL, so almost everything you learn here works as-is on other DBs. Countless apps inside your phone actually store data with SQLite — it’s a player you’d be wrong to underestimate for being light.

2-3. How to Read SQL — The Verb Comes First

An SQL statement is mostly an English sentence of "verb + target + condition."

SELECT name FROM users WHERE is_admin = 1;

Read it aloud — "SELECT name, FROM users, WHERE is_admin is 1." If the sentence reads aloud, you’ve already understood half. The semicolon (;) at the end is the period marking "end of sentence" — in sqlite3, if you drop it, nothing executes and it keeps waiting for more input (you’ll actually see this in 3-6).

3. Follow Along

3-1. Installation and Entry

Input

sudo apt install sqlite3 -y     # Ubuntu/Debian family
sqlite3 test.db

Output (measured 2026-09-09, sqlite3 3.45.1):

SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite>

How to read it: the prompt changed to sqlite>. From now on, everything you type is SQL or an SQLite-specific command. test.db is a declaration of "I’ll use this file as the warehouse," and the file gets created at the first save.

Why: this prompt right now is a direct phone line to the DB. It’s your first experience of grabbing a service’s back door yourself.

3-2. Creating a Table — CREATE

Input

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  pw TEXT,
  is_admin INTEGER
);

Output (measured 2026-09-09): no output means success. Type .tables and you’ll see users.

How to read it: "create a table called users. Four cells — id (integer, primary key), name (text), pw (text), is_admin (integer)." The PRIMARY KEY is each row’s social security number — numbers get assigned automatically. .tables is an SQLite-specific command (starting with a dot) that shows the list of tables in the warehouse.

Why: deciding the table’s blueprint first is the rule of relational DBs. Ours is four cells for now, but a real service’s member table has the same structure.

3-3. Inserting Data — INSERT

Input

INSERT INTO users (name, pw, is_admin) VALUES ('admin', 'secret123', 1);
INSERT INTO users (name, pw, is_admin) VALUES ('alice', 'wonderland', 0);
INSERT INTO users (name, pw, is_admin) VALUES ('bob', 'builder99', 0);
INSERT INTO users (name, pw, is_admin) VALUES ('charlie', 'choco789', 0);
INSERT INTO users (name, pw, is_admin) VALUES ('dave', 'davepass', 0);

Output (measured 2026-09-09): it succeeds quietly with no output.

How to read it: "INSERT INTO users, into these columns, these values." Wrapping text (strings) in single quotes is SQL’s iron rule. Drop the quotes and you get an error or a different meaning.

Why: this is the substance of sign-up. The sentence you just typed by hand is what a program types for you in a web service.

3-4. Querying — SELECT and WHERE

Input

SELECT * FROM users;

Output (measured 2026-09-09):

1|admin|secret123|1
2|alice|wonderland|0
3|bob|builder99|0
4|charlie|choco789|0
5|dave|davepass|0

How to read it: * means "all columns." The vertical bars (|) in the output separate the cells. All five members show.

Let’s attach a condition.

SELECT name FROM users WHERE is_admin = 1;

Output (measured 2026-09-09): admin

How to read it: "only name, from users, only the admins." WHERE is the filter. This combination — which columns, from which table, with which condition — is all of SELECT.

Why: this statement is the very substance of login. Asking with SELECT "is there a row with this ID and password?" The moment input squeezes into that sentence is exactly SQL Injection — so engrave the appearance of "normal" in your eyes now.

3-5. Modifying and Deleting — UPDATE, DELETE

Input

UPDATE users SET pw='newpass456' WHERE name='admin';
SELECT name, pw FROM users WHERE name='admin';

Output (measured 2026-09-09): admin|newpass456 — the password changed.

Input

DELETE FROM users WHERE id=5;
SELECT * FROM users;

Output (measured 2026-09-09): dave is gone and four members remain.

1|admin|newpass456|1
2|alice|wonderland|0
3|bob|builder99|0
4|charlie|choco789|0

How to read it: "UPDATE users, SET like this, WHERE this condition" / "DELETE FROM users, the ones matching the condition."

⚠️ Fatal caution: if you drop WHERE, it applies to everyone. We actually confirmed this on a copy of the practice file (measured 2026-09-09): running UPDATE users SET pw='x'; changed every member’s pw to x. DELETE FROM users; empties the table completely. A large share of real-world major accidents is "forgot the WHERE" — check the condition twice before executing.

Why: these are the remaining two of CRUD. If you’ve gotten all four into your hands, you’re already "a person who can talk to a DB."

3-6. Make a Prediction — What If a Quote Breaks?

Prediction: what happens if you register someone whose name contains a single quote?

INSERT INTO users (name, pw, is_admin) VALUES ('o'neil', 'pass', 0);

(a) It registers normally
(b) An error occurs
(c) The name gets truncated to o and registers

Check for yourself — output (measured 2026-09-09):

Error: in prepare, near "neil": syntax error
  INSERT INTO users (name, pw, is_admin) VALUES ('o'neil', 'pass', 0);
                                      error here ---^

The answer is (b). The string was interpreted as ending at the first quote, and an incomprehensible remainder neil was left after it. sqlite3 even draws an arrow (---^) at the error position.

Why it matters: what you just saw is the phenomenon that "user input can break the structure of an SQL statement." You’re lucky when it errors — what if this quote gets used not to break the sentence but to change it? That’s the seed of SQL Injection. Today we only see "it breaks"; "it changes" you’ll confirm with your own eyes in Step 93.

3-7. Dot Commands — SQLite’s Operating Manual

If SQL is "commands given to the warehouse keeper," commands starting with a dot are "the SQLite program’s own control buttons."

Input

.schema users
.headers on
.mode column
SELECT * FROM users;
.exit

Output (measured 2026-09-09): .schema users shows the table’s blueprint.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, pw TEXT, is_admin INTEGER);

SELECT after .headers on and .mode column prints as a tidy table with column names.

id  name     pw          is_admin
--  -------  ----------  --------
1   admin    newpass456  1
2   alice    wonderland  0
3   bob      builder99   0
4   charlie  choco789    0

How to read it: the four you’ll use often are .tables (table list), .schema tablename (structure), .help (full list), and .exit (quit).

Why: when you receive an unfamiliar DB file — say, a server’s DB seized in breach analysis — the very first things you do are .tables and .schema. It’s the skill of grasping "what’s inside" within 30 seconds.

3-8. Query Applications — LIKE, ORDER BY, LIMIT

SELECT answers more questions than you’d think. Let’s learn three frequently used ones.

Input

SELECT * FROM users WHERE name LIKE 'a%';
SELECT name FROM users ORDER BY name;
SELECT name FROM users ORDER BY id DESC LIMIT 2;

Output (measured 2026-09-09):

1|admin|newpass456|1        ← LIKE 'a%' : members starting with a
2|alice|wonderland|0

admin                        ← ORDER BY name : by name
alice
bob
charlie

charlie                      ← ORDER BY id DESC LIMIT 2 : the 2 most recent
bob

How to read it: in LIKE 'a%', the % is a wildcard meaning "whatever comes after doesn’t matter." ORDER BY column sorts by that column; attach DESC for largest-first (descending); LIMIT N takes only the first N.

Why: "only admins," "only the 10 newest posts," "only ones with this letter in the name" — behind a service’s query screens sits almost entirely this grammar. Once you know a search box’s substance is LIKE, the way you see the web changes.

4. Missions & Exercises

Mission — Designing and Operating a Mini Board Warehouse

Create board.db and design a posts table (at least 4 of: id, author, title, body, created date).
INSERT 5 posts.
Run the 3 kinds of SELECT: ① query everything ② query only a specific author ③ query only titles.
Modify one post with UPDATE, delete one with DELETE, then confirm the results.
Search with LIKE for only posts whose title contains ‘notice’, and pull just the 3 newest with ORDER BY + LIMIT.
Attach the .schema posts output to your wiki’s SQL-basics.md, and write in two lines "why an UPDATE without WHERE is dangerous."

Exercises

Exercise 1. Explain the relationship among table, row, and column using the "member table" as an example.

Exercise 2. If you drop the semicolon at the end of an SQL statement, what happens in sqlite3?

Exercise 3. Explain the difference between SELECT * FROM users WHERE name LIKE 'a%'; and WHERE name LIKE '%a%'.

Exercise 4. You ran UPDATE to change one person but everyone changed. What’s the cause, and what habit before executing can prevent it?

5. Model Answers & Completion Criteria

Mission Model Answer

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author TEXT,
  title TEXT,
  body TEXT,
  created TEXT
);
INSERT INTO posts (author, title, body, created) VALUES
  ('admin', 'Notice: board rules', 'Let us follow the rules', '2026-09-09');
INSERT INTO posts (author, title, body, created) VALUES
  ('alice', 'First post', 'Hello everyone', '2026-09-09');
-- (fill in 5 total)

SELECT * FROM posts;                          -- ① everything
SELECT * FROM posts WHERE author='alice';     -- ② specific author
SELECT title FROM posts;                      -- ③ titles only

UPDATE posts SET body='edited content' WHERE id=2;
DELETE FROM posts WHERE id=5;

SELECT title FROM posts WHERE title LIKE '%notice%';
SELECT title FROM posts ORDER BY id DESC LIMIT 3;

How to verify: ① is posts in .tables, and ② after UPDATE·DELETE, is the SELECT * result as intended? ③ did the LIKE search and the newest-3 extraction actually run? ④ are the .schema posts output and the two-line danger explanation in the wiki? All "yes" means complete.

Exercise Answers

Answer 1. A table is one sheet (users = the member table), a row is one horizontal line, one record (one member), and a column is the kind of a vertical cell (attributes like name, pw). "Add a row to the users table" = "register one member."

Answer 2. It judges the sentence hasn’t ended and shows an indented continuation prompt ( ...>), waiting for more input (measured 2026-09-09: after sqlite> SELECT * FROM users, a ...> appears on the next line). Only when you type ; and press Enter does it finally execute.

Answer 3. 'a%' finds only names that start with a (in the measurement, admin and alice came out), while '%a%' finds all names where a appears anywhere. That’s because the leading % means "whatever comes before doesn’t matter" — dave and charlie also contain a, so they’re caught by the second search.

Answer 4. The cause is a missing WHERE condition (or one too broad) — an UPDATE without WHERE applies to everyone in the table (measured 2026-09-09 on a copy: all four members’ pw became ‘x’). Prevention habit: before UPDATE/DELETE, first run a SELECT with the same WHERE to visually confirm "what will change," then just swap the verb and execute.

Completion Criteria Checklist

[ ] I can enter sqlite3, create a table, and exit
[ ] I can type the four CRUD statements without the book
[ ] I can pick out only the rows I want with WHERE, LIKE, ORDER BY, and LIMIT
[ ] I can use the 4 dot commands (.tables, .schema, .help, .exit)
[ ] I can explain the danger of "UPDATE/DELETE without WHERE"
[ ] I can reproduce and explain the phenomenon of quotes colliding with SQL syntax
[ ] Mission: I completed the board.db board warehouse and summarized it in the wiki

6. Common Pitfalls & Fixes

Wall 1. I typed a statement and a strange prompt (...>) appeared

Symptom (measured 2026-09-09):

sqlite> SELECT * FROM users
   ...>

Cause: you dropped the semicolon (;). SQLite considers the sentence unfinished until it meets ;.

Fix: type ; and Enter. Until it becomes a habit, recite aloud "a period at the end of a sentence."

Wall 2. "no such table: users" error appears

Symptom (measured 2026-09-09):

Error: in prepare, no such table: members

Cause: mostly one of two — you opened a different file (sqlite3 test2.db), or CREATE TABLE errored midway and never actually got created.

Fix: check the current file’s table list with .tables. If the list is empty, just CREATE again. Checking which file you opened comes first.

Wall 3. Quote errors ("syntax error") keep appearing

Symptom (measured 2026-09-09):

Error: in prepare, near "neil": syntax error

Cause: you didn’t wrap a string in quotes, mixed double and single quotes, or there’s a quote inside the string (the situation in 3-6).

Fix: in SQL, strings use single quotes as the rule. Scan the input value letter by letter with your eyes, and read the position the error message’s arrow (---^) points to.

Wall 4. I ran UPDATE/DELETE and more changed than expected

Symptom (measured 2026-09-09): running UPDATE users SET pw='x'; without WHERE changed all four members’ passwords to ‘x’.

Cause: the WHERE condition was broad or missing entirely.

Fix: solve it with a habit — before UPDATE/DELETE, first run a SELECT with the same WHERE to confirm "what will change" in advance. Since this is a practice file, you can just remake it now, but this habit prevents real-world disasters.

Wall 5. Results come out cramped and hard to read

Symptom: vertical-bar output like 1|admin|newpass456|1 is hard to read.

Cause: sqlite3’s default output mode is list. It’s not a malfunction.

Fix: type .headers on and .mode column and it changes to a tidy table with column names (see the measured output in section 3-7). Exiting with .exit resets it, so type them again each time you start.

7. Summary

Today’s Concepts

Concept	One-line explanation
Relational database	A warehouse that stores data in tables
Table / row / column	One sheet / one horizontal line (one record) / the kind of a vertical cell
PRIMARY KEY	Each row’s social security number — auto-numbered, no duplicates
CRUD	The four actions: Create, Read, Update, Delete
SQLite	A DB that runs as a single file with no server — syntax is standard SQL
The seed of SQL Injection	The phenomenon of an input’s quote breaking an SQL statement’s structure

Today’s Commands

Command	What it does
`sqlite3 file.db`	Open a DB file and enter the interactive prompt
`CREATE TABLE name (...)`	Create a table
`INSERT INTO table (columns) VALUES (values)`	Add a row (strings in single quotes)
`SELECT columns FROM table WHERE condition`	Query — `*` means all columns
`UPDATE table SET column=value WHERE condition`	Modify (WHERE check required)
`DELETE FROM table WHERE condition`	Delete (WHERE check required)
`LIKE 'a%'` / `ORDER BY column DESC` / `LIMIT N`	Partial search / sort / count limit
`.tables` / `.schema table` / `.exit`	Table list / blueprint / quit

An Instinct More Important Than Commands

SQL is a readable sentence. When you meet a statement you don’t know, find the verb first and read it aloud — "select, from where, on what condition." And engrave two iron rules in your body. First, strings in single quotes. Second, for UPDATE/DELETE, checking WHERE comes first. These two prevent half of real-world accidents.

Remember the quote accident you saw in 3-6 today. That unease — that a single input can break a sentence — is the starting point of understanding SQL Injection. Today we saw up to "it breaks." What happens when this input gets used to change the sentence — the day to confirm that with your own eyes, together with Python integration, comes soon.

Once every box is checked, Step 92 is complete.