SQL in Your Application

In the SQL for Beginners series you have typed every statement by hand into a prompt and watched it run. Chapter 23 of this 24-chapter course moves the same SQL into a program: how to pass values safely with placeholders, what SQL injection looks like when you get it wrong, when to let an ORM write the query for you, and how migrations keep a schema in step with your code.

Placeholders instead of strings

Every database driver does the same two-part dance: you send the SQL text with holes in it, and you send the values to fill. The engine parses the SQL once, then binds the values without ever treating them as code. The holes are written ? — one per value, in order:

SELECT first_name, last_name
FROM customers
WHERE country = ?
ORDER BY id;

The sqlite3 command line can drive this too, with the .parameter dot-command standing in for your application’s driver:

sqlite3 shop.db ".parameter init" ".parameter set :country India" \
  "SELECT first_name, last_name FROM customers WHERE country = :country ORDER BY id;"

Expected output:

Aarav|Sharma
Priya|Nair

In PHP the same statement is prepared once and executed with an array of values; PDO puts the two parts in separate method calls on purpose:

$stmt = $pdo->prepare(
    'SELECT first_name, last_name FROM customers WHERE country = ? ORDER BY id'
);
$stmt->execute(['India']);

foreach ($stmt as $row) {
    echo $row['first_name'], ' ', $row['last_name'], PHP_EOL;
}

Expected output:

Aarav Sharma
Priya Nair

Because the SQL text never changes between calls, the engine caches the parsed statement and every later execution only replaces the bound values — safety and speed arrive together.

Three placeholder styles

The idea is standard; the spelling is not. Knowing all three saves you from reading three sets of documentation:

• ? positional — SQLite, MySQL and PDO. Values are matched by position, so adding a column to the statement means counting carefully.
• :name named — PDO, SQLite and Oracle. WHERE country = :country reads like prose and lets you pass the same value twice without repeating it in the list.
• $1, $2… — PostgreSQL’s libpq style: numbered, position-checked by the server, and the reason you will see WHERE id = $1 in every Postgres tutorial.

One hard limit applies everywhere: a placeholder stands for a value, never for a table or column name. SELECT * FROM ? is not valid, so anything the user picks from a menu of identifiers — an ORDER BY column, a sort direction — must be checked against a whitelist in your code before it goes anywhere near the SQL.

SQL injection, in one line

The moment you build the SQL by pasting a value into it, the value becomes part of the program. Suppose the application lets visitors filter customers by city and does it the obvious way:

$city = $_GET['city'];   // an attacker sends:  ' OR '1'='1
$sql = "SELECT * FROM customers WHERE city = '" . $city . "'";
$rows = $pdo->query($sql)->fetchAll();   // all six customers

The engine cannot tell where your data ends and your query began. Written out, the statement the attacker receives is perfectly legal SQL — and it has no filter left in it:

sqlite3 shop.db "SELECT COUNT(*) FROM customers WHERE city = '' OR '1'='1';"

Expected output: 6 — every row in the table. Change the payload to '; DROP TABLE customers; -- and you can see how the same trick becomes destructive.

Hand the same input to a placeholder and it stops being SQL:

$stmt = $pdo->prepare('SELECT COUNT(*) FROM customers WHERE city = ?');
$stmt->execute(["' OR '1'='1"]);
echo $stmt->fetchColumn();

Expected output: 0 — no city matches that string, because the string is all it is.

Escaping is the older, weaker answer: double the single quotes and the value cannot break out. It is worth recognising because you will meet it in legacy code, and worth seeing fail first. A customer whose name is typed with a plain quote destroys the statement:

sqlite3 shop.db "SELECT COUNT(*) FROM customers WHERE last_name = 'O'Brien';"

Expected output:

Error: in prepare, near "Brien": syntax error
  LECT COUNT(*) FROM customers WHERE last_name = 'O'Brien';
                                      error here ---^

Escaped properly the apostrophe is written twice and the query runs (it finds nobody, which is the honest answer for a name our sample data does not contain):

sqlite3 shop.db "SELECT COUNT(*) FROM customers WHERE last_name = 'O''Brien';"

Expected output: 0

Escaping still leaves you responsible for every other edge case — backslashes under MySQL, the % and _ wildcards hiding inside a LIKE, encodings where a byte split in half changes meaning. Placeholders make all of it the driver’s problem, which is where it belongs.

ORMs and hand-written SQL

An ORM (object-relational mapper) gives each table a class and each row an object, so the SQL disappears behind method calls. In Laravel’s query builder the customer lookup becomes a chain — and it still compiles to the placeholder form you just learned. On a SQLite connection it comes out as:

Customer::where('country', 'India')->orderBy('id')->get();
// select * from "customers" where "country" = ? order by "id" asc

For CRUD work this is a good trade: mapping, safety and migrations come free, and the code reads like the domain. Two costs appear as your code grows. The first is the N+1 problem: a loop that runs one query per row — six customers means one query for the list and six more for their orders, seven round trips where one join would do. Eager loading (Customer::with('orders')->get()) or a single aggregate collapses them back into one statement. The second cost is shape: reports with window functions, CTEs or five joins fight the abstraction, and the generated SQL is harder to read than the query you would have written. The working rule is simple — let the ORM handle entity CRUD, and write the reporting queries yourself.

Transactions belong in the code too

The atomic pairs from chapter 21 do not disappear when SQL moves into a program — they become a block of code, and every driver spells the same three verbs. PDO exposes them directly, and the rule is that the catch never lets a half-written order survive:

$pdo->beginTransaction();
try {
    $order = $pdo->prepare(
        'INSERT INTO orders (customer_id, order_date, status) VALUES (?, ?, ?)'
    );
    $order->execute([1, '2026-01-12', 'paid']);

    $item = $pdo->prepare(
        'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)'
    );
    $item->execute([$pdo->lastInsertId(), 1, 1, 499]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Two inserts, one outcome — exactly what you proved by hand with BEGIN and ROLLBACK, now attached to a failure nobody planned for. Frameworks wrap the ceremony so it is written once instead of in every repository: Laravel’s DB::transaction(function () { /* ... */ }); commits when the closure returns and rolls back when it throws.

Migrations: schema as versioned code

Hand-editing a production table is how environments drift apart. A migration is a numbered file that changes the schema one step, applied in order and recorded, so every copy of the database — your laptop, your colleague’s, the server — reaches the same shape by running the same list:

migrations/
  0003_add_customers_city_index.up.sql
  0003_add_customers_city_index.down.sql

The pair is deliberately boring — the up file makes the change, the down file reverses it:

-- 0003_add_customers_city_index.up.sql
CREATE INDEX idx_customers_city ON customers(city);

-- 0003_add_customers_city_index.down.sql
DROP INDEX idx_customers_city;

Something has to remember which files have run, and that something is a tiny table with one row per version:

CREATE TABLE IF NOT EXISTS schema_migrations (
    version    TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL
);

Laravel packages exactly this idea behind one command: php artisan migrate runs every unapplied file in database/migrations/, records it, and stops at the first failure. Two habits go with it. Never edit a migration that has already shipped — add a new one, because other people’s databases already recorded the old number. And keep a way back: the down file (or php artisan migrate:rollback) is your undo, while a backup is your insurance policy — one command per engine, and for SQLite the whole database is a single file you can copy while it works:

mysqldump shop > backup.sql              # MySQL
pg_dump shop > backup.sql                # PostgreSQL
sqlite3 shop.db ".backup shop-backup.db" # SQLite

Expected output: none — but backup.sql (or shop-backup.db) now holds a consistent copy you can restore, diff or practise on while the original keeps serving traffic.

Common mistakes

• Building SQL by concatenation — one user-supplied string inside the statement and the filter becomes optional. Placeholders for every value, every time, including your own code.
• Escaping instead of binding — doubling quotes passes today’s test and fails on the next encoding, wildcard or database engine. It is a patch, not a defence.
• Binding identifiers — a placeholder cannot stand for a table, column or sort direction. Whitelist those choices in code before the SQL is assembled.
• Trusting the ORM to fix a slow loop — one query per row is still one query per row behind a fluent API. Count round trips, then eager-load or join.
• Editing schema by hand in production — the change works and then nobody can reproduce it. Every structural change becomes a numbered migration with a matching down file.
• Leaving multi-statement writes in autocommit — an order can be inserted and its items lost the moment the second statement fails. Wrap dependent writes in the driver’s transaction API.

Practice checklist

1. Run the .parameter example and get Aarav|Sharma and Priya|Nair back.
2. Reproduce the ' OR '1'='1 payload against a concatenated query (6 rows), then through a placeholder (0 rows).
3. Write a function in your own stack that looks up a customer by id using a placeholder only — no string concatenation anywhere.
4. Take a loop that queries once per row and rewrite it as a single join or aggregate; count the queries before and after.
5. Add a migration pair for an index on customers(city), apply it, verify it with EXPLAIN QUERY PLAN, then roll it back.

What's next?

Your SQL now runs safely behind an application, with its history tracked in migrations. Next the series steps back for the finish line: SQL Best Practices and Where to Go Next collects the defaults worth keeping and points at what to learn after this course.