Security and hardening · Intermediate · 14 min
How to Prevent SQL Injection in Python and Node.js
By Padding · Updated 15 Nov 2026
Python 3.15 · Node.js 26 · SQLite
You will write a login lookup that an attacker can break with one line of input, watch it fail, fix it with parameterised queries in both Python and Node.js, and learn the cases where parameters are not enough.
On this page
SQL injection happens when a program builds a SQL statement by pasting user input into a string. The database cannot tell which part of the string the programmer wrote and which part the user typed, so a user who types SQL gets it run. It is one of the oldest web flaws, and it is still found in new code every week, because string building is the first thing that comes to mind.
The fix is also old and simple: send the SQL and the values separately, so the database never reads a value as code. In this tutorial you will create a small database, write the broken version, break it, and fix it, first in Python and then in Node.js. Both use SQLite, which needs no server. The lesson is the same for PostgreSQL and MySQL, and the drivers' syntax differs only in the parameter style.
Step 1 · Create a small database
Make a folder and a Python script that builds a database with two tables: users, and a table of secrets that no login lookup should ever show.
mkdir sqli-demo
cd sqli-demo
cat > setup_db.py <<'PY'
import sqlite3
db = sqlite3.connect("app.db")
db.executescript("""
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS api_keys;
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL);
CREATE TABLE api_keys (id INTEGER PRIMARY KEY, owner TEXT NOT NULL, key TEXT NOT NULL);
INSERT INTO users (name, role) VALUES ('ada', 'admin'), ('grace', 'member'), ('linus', 'member');
INSERT INTO api_keys (owner, key) VALUES ('ada', 'sk_fake_ada_0001'), ('grace', 'sk_fake_grace_0002');
""")
db.commit()
db.close()
print("created app.db")
PY
python3 setup_db.pyThe script prints created app.db. It uses Python's built-in sqlite3 module, so there is nothing to install.
Step 2 · Write the vulnerable lookup in Python
Here is the code that creates the hole. The function finds a user by name, and it builds the SQL with an f-string:
cat > vulnerable.py <<'PY'
import sqlite3
db = sqlite3.connect("app.db")
def find_user(name):
sql = f"SELECT id, name, role FROM users WHERE name = '{name}'"
print("SQL:", sql)
return db.execute(sql).fetchall()
if __name__ == "__main__":
print(find_user("grace"))
PY
python3 vulnerable.pyFor a normal name, it works. The output shows the statement and one row:
SQL: SELECT id, name, role FROM users WHERE name = 'grace'
[(2, 'grace', 'member')]Step 3 · Break it
Now pass input that no user lookup should accept. The first one closes the quote and adds a condition that is always true:
cat > attack1.py <<'PY'
from vulnerable import find_user
print(find_user("nobody' OR '1'='1"))
PY
python3 attack1.pyThe statement the database receives is ... WHERE name = 'nobody' OR '1'='1', and it returns every user. The same trick turns a login check such as WHERE name = '...' AND password = '...' into one that accepts any password, which is how many break-ins started.
The second attack is worse. A UNION appends the results of a different query to the first. The attacker reads a table the page was never meant to show, by matching the number of columns:
cat > attack2.py <<'PY'
from vulnerable import find_user
print(find_user("x' UNION SELECT id, owner, key FROM api_keys --"))
PY
python3 attack2.pyThe output includes (1, 'ada', 'sk_fake_ada_0001'): the API keys came back in a result that was meant to list users. The -- starts a SQL comment, which hides the closing quote the programmer's string adds at the end. Attackers use this to read password hashes, tokens and personal data.
Step 4 · Fix it with a parameterised query
Replace the f-string with a ? parameter marker and pass the value as a separate argument:
cat > safe.py <<'PY'
import sqlite3
db = sqlite3.connect("app.db")
def find_user(name):
sql = "SELECT id, name, role FROM users WHERE name = ?"
return db.execute(sql, (name,)).fetchall()
if __name__ == "__main__":
print(find_user("grace"))
print(find_user("nobody' OR '1'='1"))
print(find_user("x' UNION SELECT id, owner, key FROM api_keys --"))
PY
python3 safe.pyThe first call still returns grace's row. The other two return []. The database looks for a user whose name is literally nobody' OR '1'='1, finds none, and executes nothing the attacker wrote. The SQL text is fixed before the value arrives, and the value can only ever be data.
The details to remember:
- The second argument is a tuple, so a single value needs a trailing comma:
(name,). - The marker is never wrapped in quotes. Write
= ?, not= '?'. - Python's
sqlite3uses?or:name. The PostgreSQL driverpsycopguses%s, andmysql-connector-pythonuses%stoo. In each, the values still go in a separate argument. Python's%string formatting and f-strings are never the right tool.
Named parameters read better when a query has several values:
cat > named.py <<'PY'
import sqlite3
db = sqlite3.connect("app.db")
rows = db.execute(
"SELECT name, role FROM users WHERE role = :role AND name != :excluded",
{"role": "member", "excluded": "linus"},
).fetchall()
print(rows)
PY
python3 named.pyThe output is [('grace', 'member')].
Check it worked
Run the three lookups against both versions side by side. The vulnerable one leaks, and the safe one does not:
python3 attack2.py
python3 safe.pyThe first command prints a row with sk_fake_ada_0001. The second prints one row for grace and then two empty lists.
Step 5 · Do the same in Node.js
The fix has the same shape in Node.js. Node.js 26 has a built-in SQLite module, node:sqlite, so again there is nothing to install, and it runs without any flag. For a production app you would more likely use a driver such as pg for PostgreSQL, which has the same pattern with $1 parameters.
First the vulnerable version, with a template string:
cat > vulnerable.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite'
const db = new DatabaseSync('app.db')
export function findUser(name) {
const sql = `SELECT id, name, role FROM users WHERE name = '${name}'`
console.log('SQL:', sql)
return db.prepare(sql).all()
}
console.log(findUser("x' UNION SELECT id, owner, key FROM api_keys --"))
JS
node vulnerable.mjsThe output lists the rows of api_keys again. Now the fix, with prepare and a bound value:
cat > safe.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite'
const db = new DatabaseSync('app.db')
export function findUser(name) {
return db.prepare('SELECT id, name, role FROM users WHERE name = ?').all(name)
}
console.log(findUser('grace'))
console.log(findUser("nobody' OR '1'='1"))
console.log(findUser("x' UNION SELECT id, owner, key FROM api_keys --"))
JS
node safe.mjsThe first call returns grace's row, and the other two print []. The value goes to .all(name), apart from the SQL passed to prepare. The principle holds in every Node.js driver: ? or $1 in the SQL, and the values in the second argument. For example, with pg it is client.query('SELECT ... WHERE name = $1', [name]).
Troubleshooting
ProgrammingError: Incorrect number of bindings supplied: the number of?markers does not match the number of values. Count both, and remember the tuple comma.ERR_UNKNOWN_BUILTIN_MODULE: node:sqlite: your Node.js is too old (this tutorial uses 26). Install Node.js 26, or use thebetter-sqlite3package, which has the sameprepare(...).all(value)shape.- A query that works with a value fails when you bind a table name: parameters hold values only. The next step covers it.
Step 6 · Handle what parameters cannot cover
A parameter can stand in for a value, such as a name or a number. It cannot stand in for a table name, a column name or a sort direction, because those are part of the statement's structure. Sorting a list by a column the user picks is the usual case. Do not paste the user's choice in. Check it against a fixed list instead:
cat > sorted.py <<'PY'
import sqlite3
db = sqlite3.connect("app.db")
ALLOWED_COLUMNS = {"name", "role", "id"}
ALLOWED_DIRECTIONS = {"asc", "desc"}
def list_users(sort_by="name", direction="asc"):
if sort_by not in ALLOWED_COLUMNS or direction not in ALLOWED_DIRECTIONS:
raise ValueError("invalid sort")
sql = f"SELECT id, name, role FROM users ORDER BY {sort_by} {direction}"
return db.execute(sql).fetchall()
print(list_users("name", "desc"))
try:
list_users("name; DROP TABLE users", "asc")
except ValueError as e:
print("rejected:", e)
PY
python3 sorted.pyThe first call prints the users in reverse name order. The second prints rejected: invalid sort. The f-string here is safe, because the only values that can reach it are strings that you wrote in the allow-list.
Two other values need care:
- Numbers.
LIMIT ?takes a bound value, but convert it first withint(value), and cap it, so a user cannot ask for ten million rows. - LIKE searches. A bound value stops injection, but
%and_inside it still act as wildcards, so a user can match more than you intended. Escape them with anESCAPEclause if that matters for the feature.
Step 7 · Add the defences around the code
Parameterised queries stop injection at its source. Add layers so a mistake elsewhere does less damage:
- Least privilege. The database user your app connects as should hold only the rights it uses:
SELECT,INSERT,UPDATEandDELETEon its own tables. It should not be a superuser or the owner of the schema. Then an injection cannot drop tables or read other databases. The tutorials on installing PostgreSQL, MySQL and MongoDB each create such a user. - Validate input for its purpose. A field for an age should be an integer, and an email field should look like an email. This does not replace parameters, but it reduces what reaches the database.
- Raw queries in ORMs. Django, SQLAlchemy, Prisma and others build parameterised queries for you. The risk returns when you use their raw-SQL escape hatch with string building, such as Django's
raw()orextra(), SQLAlchemy'stext()with an f-string, or Prisma's$queryRawUnsafe. Use the parameter forms of those:text("... WHERE name = :name"), or Prisma's tagged-template$queryRaw. - Do not show database errors to users. An error page that prints the SQL and the driver's message teaches an attacker your table names. Log it on the server and show a generic message.
- Look for the pattern in code review. Search your project for SQL keywords next to
f",+,%or `` `${ ``. Each hit is worth a look.
grep -nE "f\"[^\"]*(SELECT|INSERT|UPDATE|DELETE)" *.pyThis search finds the SQL built with f-strings in the demo files: vulnerable.py and sorted.py. The first is the flaw. The second is the allow-listed case from the previous step. Running it over your own project gives you a list to review.
Next steps
- Keep keys and database passwords out of your code and your repository:
/tutorials/keep-secrets-out-of-git. - Create a database user with limited rights:
/tutorials/install-postgresql-18-on-ubuntu-26-04. - The OWASP SQL Injection Prevention Cheat Sheet at cheatsheetseries.owasp.org covers more database drivers and edge cases.
Related tutorials
- How to Set Up a UFW Firewall on Ubuntu 26.04You will turn on UFW with a deny-by-default policy, allow SSH before you enable it so you stay connected, open only the web ports your server needs, and learn to list, remove and limit rules.
- How to Keep Passwords and API Keys Out of GitYou will keep secrets in a git-ignored .env file, commit a safe template instead, stop a commit that contains a key with a pre-commit hook, and learn what to do when a secret has already been committed.
- How to Harden SSH on Ubuntu 26.04: Keys Only, No Root, Custom LimitsYou will switch an Ubuntu 26.04 server's SSH daemon to key-only logins, turn off root login, limit who can connect and how many guesses they get, and test the change without locking yourself out.
Need this done for you?
Padding does this work for growing companies, from the first diagnosis to a system that keeps working.
Get new tutorials