Python Parameterized Queries to Prevent SQL Injection

Scraped data is untrusted input. A product title, a city name, a review body — anything you pull off a web page — can contain quotes, semicolons, or deliberately hostile SQL. If you build your INSERT statements by gluing that text into a query string, you've handed the page author a way to corrupt or wipe your database. The fix is old, boring, and completely reliable: parameterized queries. This article shows how to use them in Python to prevent SQL injection while you store scraped data in a database, with working code for SQLite, PostgreSQL, and MySQL, plus bulk inserts, upserts, and the mistakes to avoid.

Why this matters when you store scraped data

The classic illustration is xkcd's "Bobby Tables": a school loses its database because a student is named Robert'); DROP TABLE Students;--. Swap the student for a scraped listing and it's the same story. Consider a value your crawler picked up:

city = "test city'); DROP TABLE listings;--"

If you drop that straight into query text, the database sees your intended INSERT and a DROP TABLE. A parameterized query treats it as nothing but a string to store. Even ordinary data breaks naive string-building — a place called L'Île-Perrot contains an apostrophe that ends your SQL literal early. Parameterization handles both the malicious and the merely awkward for free. (For the attack side in depth, see SQL injection prevention.)

The rule: never format SQL, always bind parameters

A parameterized query (also called a prepared or bound statement) sends the SQL and the data to the database separately. You write placeholders in the SQL; you pass the values as a second argument. The driver — not your string formatting — binds them, guaranteeing the values are treated as data, never as executable SQL.

# NEVER do any of these — every one is injectable
cur.execute(f"INSERT INTO listings (city) VALUES ('{city}')")
cur.execute("INSERT INTO listings (city) VALUES ('%s')" % city)
cur.execute("INSERT INTO listings (city) VALUES ('" + city + "')")

# ALWAYS do this — SQL and data travel separately
cur.execute("INSERT INTO listings (city) VALUES (%s)", (city,))

The safe version is the whole lesson. The %s there is not Python string formatting — it's the database driver's placeholder, and the tuple is bound safely.

Placeholder styles differ by driver

The one gotcha: the placeholder symbol depends on the database library's "paramstyle." Get this right and everything else follows.

Note that mysqlclient is the maintained descendant of the old MySQLdb — if you're following a decade-old tutorial that imports MySQLdb, that's the modern package name, and on new projects most people reach for PyMySQL (pure Python) or psycopg for Postgres.

Working example: SQLite (no install needed)

sqlite3 ships with Python, so this runs anywhere:

import sqlite3

conn = sqlite3.connect("scraped.db")
cur = conn.cursor()
cur.execute("""
    CREATE TABLE IF NOT EXISTS listings (
        id    INTEGER PRIMARY KEY,
        name  TEXT,
        city  TEXT,
        price REAL
    )
""")

# Values scraped from a page — treat as hostile
name  = "L'Île-Perrot"
city  = "test city'); DROP TABLE listings;--"
price = 199.0

cur.execute(
    "INSERT INTO listings (name, city, price) VALUES (?, ?, ?)",
    (name, city, price),
)
conn.commit()
conn.close()

The apostrophe in name and the injection attempt in city are both stored verbatim as text. listings is not dropped.

Working example: PostgreSQL with psycopg

For a real project you'll usually target Postgres or MySQL. Here's modern psycopg (version 3), using a context manager so the transaction commits on success and rolls back on error — and reading credentials from the environment rather than hardcoding them:

import os
import psycopg  # pip install "psycopg[binary]"

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO listings (name, city, price) VALUES (%s, %s, %s)",
            (name, city, price),
        )
    # leaving the connection block commits automatically

MySQL looks almost identical with PyMySQL — same %s placeholders, same second-argument tuple.

Bulk inserts for scraped datasets

Scraping produces batches, and calling execute once per row is slow. Use executemany with a list of tuples — still fully parameterized:

rows = [(r["name"], r["city"], r["price"]) for r in scraped_items]

cur.executemany(
    "INSERT INTO listings (name, city, price) VALUES (?, ?, ?)",  # %s for Postgres/MySQL
    rows,
)
conn.commit()

For large loads, Postgres users get a big speedup from COPY (exposed via cursor.copy() in psycopg 3, or execute_values in psycopg2's extras) — all of which still keep values as bound data, not concatenated SQL.

Upserts to deduplicate

Re-scraping the same source means you'll re-insert the same records. Combine a unique key with an upsert so repeats update instead of duplicating — again with placeholders, never string-built:

-- SQLite (3.24+) and PostgreSQL
INSERT INTO listings (id, name, price) VALUES (%s, %s, %s)
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;

-- MySQL
INSERT INTO listings (id, name, price) VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE price = VALUES(price);

The one thing parameters can't do: identifiers

Placeholders bind values, not table or column names. This does not work and will error:

cur.execute("INSERT INTO %s (city) VALUES (%s)", (table, city))  # WRONG for the table name

If a table or column name is dynamic, you cannot parameterize it — you must whitelist it against a fixed set of known-good names, then build the identifier safely:

ALLOWED_TABLES = {"listings", "products", "reviews"}
if table not in ALLOWED_TABLES:
    raise ValueError(f"Unexpected table: {table}")

from psycopg import sql  # psycopg's safe identifier quoting
cur.execute(
    sql.SQL("INSERT INTO {} (city) VALUES (%s)").format(sql.Identifier(table)),
    (city,),
)

Never let scraped or user-supplied text decide a table or column name by concatenation.

Let an ORM parameterize for you

Higher-level tools bind parameters automatically, which is a good reason to use them. With SQLAlchemy, both the ORM and raw text() queries are parameterized — you supply named parameters and never touch string formatting:

from sqlalchemy import create_engine, text
import os

engine = create_engine(os.environ["DATABASE_URL"])
with engine.begin() as conn:  # begin() = commit on success, rollback on error
    conn.execute(
        text("INSERT INTO listings (name, city, price) "
             "VALUES (:name, :city, :price)"),
        {"name": name, "city": city, "price": price},
    )

The same principle carries to document stores — a MongoDB pipeline passes values as BSON rather than building query strings, so classic SQL injection doesn't apply, though you still validate and type-check untrusted input.

Checklist

• Always pass values as the second argument to execute / executemany; never format, %, .format(), or concatenate them into SQL.
• Use the right placeholder for your driver (? for sqlite3, %s for psycopg/PyMySQL).
• Whitelist dynamic table/column names — parameters can't cover identifiers.
• Use executemany (or COPY) for batches and an upsert with a unique key to dedupe re-scrapes.
• Keep credentials in environment variables, not in the source file.
• Consider an ORM (SQLAlchemy) so parameterization is automatic and you also get schema management.

Wrap-up

Preventing SQL injection in Python isn't complicated: keep your SQL and your data apart, let the driver bind parameters, and treat every scraped value as hostile until it's safely stored. That one habit — parameterized queries everywhere — is what lets you store scraped data in a database at scale without a stray apostrophe or a booby-trapped listing ever touching your schema. If you'd rather receive clean, validated, deduplicated data instead of building the ingestion layer yourself, scraping.pro delivers exactly that as a managed web scraping service.