Dynamic SQL Risks

Every query in this topic has been fixed text. Applications, however, often build queries at run time: conditions get added depending on the fields filled in on a search screen, the sort column comes from the user, and the page number changes with every request.

Building the query text by concatenating it with user input looks, at first glance, like nothing more than a writing convenience. It has three separate consequences: the query breaks on ordinary input, it opens the door for the input to change the meaning of the query, and it makes the planner’s work repeat on every call. This lesson takes up all three.

Data Set

rm -f library.db
cat > setup.sql <<'SQL'
CREATE TABLE branch (
  branch_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL
);
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  name              TEXT NOT NULL,
  city              TEXT NOT NULL,
  registration_date TEXT NOT NULL
);
CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  year      INTEGER NOT NULL,
  branch_id INTEGER NOT NULL
);
CREATE TABLE loan (
  loan_id     INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL,
  member_id   INTEGER NOT NULL,
  pickup_date TEXT NOT NULL,
  return_date TEXT
);

INSERT INTO branch (branch_id, name, city) VALUES
  (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),(3,'Kadikoy','Istanbul'),
  (4,'Beyoglu','Istanbul'),(5,'Konak','Izmir'),(6,'Nilufer','Bursa'),
  (7,'Selcuklu','Konya'),(8,'Cankaya','Ankara');

INSERT INTO member (member_id, name, city, registration_date)
WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 120000)
SELECT n, 'Member ' || n,
       CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir'
                  WHEN 3 THEN 'Bursa' ELSE 'Konya' END,
       date('2015-01-01', '+' || (n % 3200) || ' days')
FROM counter;

INSERT INTO book (book_id, title, author, year, branch_id)
WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 200000)
SELECT n, 'Book ' || n, 'Author ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8)
FROM counter;

INSERT INTO loan (loan_id, book_id, member_id, pickup_date, return_date)
WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 2000000)
SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000),
       date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'),
       CASE WHEN n % 9 = 0 THEN NULL
            ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END
FROM counter;
SQL
sqlite3 library.db < setup.sql

Input That Mixes Into the Text

Consider a screen that searches the library catalog by title. The input is placed into the query text between quotes. A book whose title contains an apostrophe has been added to the catalog.

sqlite3 library.db "INSERT INTO book VALUES (200001, 'Moon''s Daughter', 'Author 7', 1998, 3);"

wanted="Book 4242"
sqlite3 library.db "SELECT book_id, title FROM book WHERE title = '$wanted';"

wanted="Moon's Daughter"
echo "--- same query, with a title containing a quote ---"
sqlite3 library.db "SELECT book_id, title FROM book WHERE title = '$wanted';"
echo "exit code: $?"

4242|Book 4242
--- same query, with a title containing a quote ---
Error: in prepare, near "s": syntax error
  LECT book_id, title FROM book WHERE title = 'Moon's Daughter';
                                      error here ---^
exit code: 1

The first search works. The second, with completely harmless input — an apostrophe found in a book title — produces a syntax error. The error message shows the reason clearly: the apostrophe in the input was read as the quote closing the string, and the rest of the query broke.

The source of the break is not that a quote character is special. The source is this: the input reached the database not as data, but as a part of the query text. The database parses the text that reaches it as a program, and it has no information within that text to distinguish where the input ends and the program begins.

The same mechanism is also the definition of the security vulnerability. If the input is part of the query text, a suitably crafted input can change the meaning of the query: a condition can be made always true, the scope of the query can be widened, a table that was never meant to be reached can be accessed. This is called SQL injection, and its source is not that the input contains special characters, but that the text is built by concatenating it with the input. This is why the solution is not to sanitize characters either: escape lists stay incomplete, encoding differences bypass the list, and every new context demands a new list. The solution is for the input to never mix into the text at all.

Bound Parameter

A bound parameter places a placeholder in the query text where the value should sit; the value is sent through a channel separate from the query. The database first parses and plans the text, then places the value into that plan.

wanted="Moon's Daughter"
sqlite3 library.db <<SQL
.parameter init
.parameter set :title "$wanted"
SELECT book_id, title FROM book WHERE title = :title;
SQL

200001|Moon's Daughter

Same input, same data, different result: the record was found. The query text contains the :title placeholder, and this text is fixed regardless of the input. Whatever is inside the input — an apostrophe, a semicolon, a -- sequence — it all stays a single string value, because parsing finished before the input arrived.

This is not a better form of data sanitization; it is a different approach. When a bound parameter is used, there is nothing left to sanitize, because the input was never program text in the first place. On the application side, this means that the query text stays fixed inside the code, and values are passed through the driver’s parameter interface.

Parse and Plan Cost

The bound parameter’s second gain is on the performance side, and it is measurable. Running a query has two phases: parsing and planning the text, and executing the plan. The cost of the first phase can be isolated in a query that returns no rows at all.

sqlite3 library.db 'CREATE INDEX book_author ON book(author);
CREATE INDEX loan_book ON loan(book_id);'

i=1
: > simple.sql
: > complex.sql
while [ "$i" -le 20000 ]; do
  printf 'SELECT 1;\n' >> simple.sql
  printf "SELECT s.name, count(*) FROM loan l JOIN book b ON b.book_id = l.book_id JOIN member m ON m.member_id = l.member_id JOIN branch s ON s.branch_id = b.branch_id WHERE b.author = 'None %s' AND m.city = 'Bursa' GROUP BY s.name ORDER BY 2 DESC;\n" "$i" >> complex.sql
  i=$((i + 1))
done

printf 'simple   : '; { time sqlite3 library.db < simple.sql > /dev/null ; } 2>&1 | tr '\n' ' '; echo
printf 'complex  : '; { time sqlite3 library.db < complex.sql > /dev/null ; } 2>&1 | tr '\n' ' '; echo

simple   :  real	0m0.023s user	0m0.014s sys	0m0.008s 
complex  :  real	0m0.211s user	0m0.156s sys	0m0.054s 

Both files contain twenty thousand statements. The queries in the complex file return no rows at all: the author condition matches no record, and execution is close to zero thanks to the index. The difference — about 0.19 seconds in this environment, around ten microseconds per statement — is the parsing and planning of the four-table join. The times depend on the environment; what matters is that planning has a measurable cost.

Databases pay this cost with a plan cache: the compiled plan is stored, and when the same query comes in again, it is not planned again. The cache’s key is the query text. When a bound parameter is used, the text is identical on every call, and the cache hits. In a query built by string concatenation, on the other hand, every different value produces a different text: the cache never hits, and it fills up with single-use entries, evicting plans that would otherwise be useful.

A bound parameter has a trade-off too. The plan is chosen jointly for all values, not for a single one; on a skewed column, the same plan is used for both a frequent value and a rare one. This is the same discussion as the average from the previous lesson. Some engines look at the values of the first few calls and apply methods that address this.

Queries With Variable Shape

Values can be passed with a bound parameter; column and table names cannot, because they belong to the structure of the query. If the sort column comes from the user, what needs to be done is to validate the incoming name against a fixed list and put only the name from that list into the text.

column_input="year"
wanted="Moon's Daughter"

case "$column_input" in
  title|author|year) column="$column_input" ;;
  *) echo "invalid sort column"; exit 1 ;;
esac

sqlite3 library.db <<SQL
.parameter init
.parameter set :wanted "$wanted"
SELECT book_id, title, year FROM book
WHERE title = :wanted OR author = 'Author 7'
ORDER BY $column LIMIT 3;
SQL

7|Book 7|1957
12007|Book 12007|1957
24007|Book 24007|1957

The distinction is clear: :wanted is a value and gets bound; $column is an identifier and enters the text, so its value is not the string the user supplied, but one of the constants sitting in the list. If the incoming name is not in the list, the query is never built. The same method applies to searches with a variable number of conditions: the condition fragments sit as fixed text inside the code, the code decides which ones to add, and all of the values get bound.

The performance-side effect of this arrangement is that different combinations of conditions produce different query texts. As long as the number stays limited — a few dozen possible combinations — the plan cache can hold all of them. The limit disappears at the point where a value mixes into the text.

Summary

• Building the query text by concatenating it with the input means the input mixes into the program text; even an ordinary title containing an apostrophe breaks the query with a syntax error.
• The same mechanism is the source of SQL injection; the solution is not to sanitize characters but to keep the input outside the text.
• A bound parameter makes the text independent of the input: parsing finishes before the input arrives, the value settles into the plan afterward, and the same input works correctly.
• Parsing and planning are a measurable cost; in this environment, it measured at around ten microseconds per statement for a four-table query.
• The plan cache works by query text; a bound parameter hits it because it fixes the text, while string concatenation produces new text on every call and renders the cache useless.
• Identifiers cannot be bound; column and table names are validated against a fixed list and used only that way.

Course Wrap-Up

The Advanced SQL course moved through three topics. Compound Queries widened the boundary of what a single statement could express: subqueries, common table expressions, recursive queries, and window functions. Transactions built the discipline that keeps more than one statement behaving correctly together: commit boundaries, isolation levels, deadlocks, and the implicit side effects of triggers. The Query Performance topic asked how much work went into a query that gave the correct result.

This last topic’s method outlasts its answers. Every claim was tested with two measures: plan output showed the decision, step count and time showed the cost; because time depends on the environment, judgments were made through ratios. The shape of plan output and its node names change by engine — what should be read is not the shape but the four decisions in the plan: scan or search, which table drives, whether a return to the table is needed, and whether a separate sort is performed.

Every optimization here was at the query level: index definition, condition syntax, join order, subquery form, statistics freshness, and parameter usage. The turn now belongs to the engine itself. The Relational Database Administration course opens up the engine that has been used as a black box up to this point: process and memory architecture, the way data is written to disk in pages, the write-ahead log and checkpoints, index maintenance and bloat, partitioning, backup and point-in-time recovery, replication and failover. Someone who knows how to measure how much work a query runs with is ready to also learn where that work lands inside the engine.