A small store lists its products by category. The developer heard about SQL Injection and added "protection": before running the query it removes the words union and select from the input, once. The database has another table holding staff notes, including a secret voucher code.
The Ravens supply store is a two-page app. The store page searches products by category, posting the category to /products and printing the query after the filter. A voucher page checks a secret code. The developer heard about SQL injection and added protection: it removes the words union and select from your input, once. A second table, staff_notes, holds a secret voucher code.
Extract the voucher code from the notes table, then enter it in the voucher box.
You enter the correct voucher code extracted from the notes table.
Why the solution worked
The filter was a simple strip of specific words, which is not protection. Removing once leaves a hole: a word nested inside itself comes back clean after the removal. Even with repeated removal, other ways remain (changing letter case, comments inside the word). A word blacklist chases symptoms; the only fix is to separate data from the query with parameters.
The root cause
The protection was a blacklist that strips specific words once. A single-pass strip is defeated by nesting the word inside itself, so a clean copy survives. The root issue remains: the input is still pasted into the query text.
How the code is fixed
Blocking words (a blacklist) is always broken by an attacker in a way the developer did not think of. The fix is parameterized queries: the value is data and never becomes code, whatever it contains.
Vulnerable code
app.get('/products', (req, res) => {
// "Protection": strip the dangerous words, then paste the rest into the query.
const category = req.query.category.replace(/union|select/gi, '');
const sql = "SELECT name, price FROM products WHERE category = '" + category + "'";
res.json(db.all(sql));
});
Safe code
app.get('/products', (req, res) => {
// The value travels as data; no amount of SQL keywords in it can change the query.
const sql = 'SELECT name, price FROM products WHERE category = ?';
res.json(db.all(sql, [req.query.category]));
});
Impact in the real world
Reading another table through UNION means exfiltrating any data in the database: usernames, password hashes, internal secrets. This is among the most exploited paths in large data breaches.
How defenders detect it
Defenders watch for the union keyword, functions like substr, and bursts of near-identical requests, and alert on SQL errors. Code review catches any place a query is built by concatenation instead of parameters.
