--- title: 'Dynamic SQL Risks' source: 'https://academia.sh/en/courses/advanced-sql/dynamic-sql-risks' course: 'Advanced SQL' language: en updated: '2026-08-23T07:00:37+00:00' license: 'CC BY-SA 4.0' --- # Dynamic SQL Risks The correctness and security consequences of building query text by string concatenation, how a bound parameter processes the same input correctly, measuring parse and plan cost, the condition under which the plan cache works, and the safe construction of queries with variable shape. 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 ```sh 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. ```sh 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. ```sh wanted="Moon's Daughter" sqlite3 library.db < 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. ```sh 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 <