Safely INSERT Data into a MySQL Database Using Python

A secure INSERT in Python isn’t about “escaping strings” or clever formatting—it’s about using parameterized queries (prepared statements), wrapping changes in transactions, and designing your database access so user input can’t change the meaning of your SQL. This guide shows practical patterns that reduce SQL injection risk, prevent partial writes, and keep inserts fast and maintainable in production.

Table of Contents

What “safe insert” really means

Safe INSERTs are about controlling two things:

  • Meaning: user data must never change the structure or intent of your SQL.
  • Integrity: your database should never end up half-updated if something fails mid-operation.

Most “security issues” in database code are really design issues: string-building SQL, inconsistent transaction boundaries, and overly powerful database credentials.

The threat model in plain English

If any part of an INSERT statement is built by concatenating or formatting strings using user input, an attacker can attempt SQL injection—feeding input that changes what the database executes. OWASP’s guidance is straightforward: prevent SQL injection by using prepared statements / parameterized queries so values are bound as data, not executable SQL.
You also need to consider “accidental damage,” not just malicious actors:

  • Unexpected characters (quotes, emoji, long strings) breaking inserts
  • Retries causing duplicate rows
  • Race conditions when two processes insert “the same logical record”
  • Partial writes when you insert into multiple tables

The 5 rules that cover 95% of cases

  1. Use parameterized queries for values (no f-strings, no % formatting, no .format()).
  2. Keep table/column names out of user input; if dynamic identifiers are required, use an allowlist.
  3. Use transactions for any multi-step write (and decide where commit/rollback lives).
  4. Enforce constraints in MySQL (NOT NULL, UNIQUE, FK, CHECK where applicable) so invalid data can’t “sneak in.”
  5. Use least-privilege DB users: an app that only inserts shouldn’t have DROP/ALTER privileges.

Use parameterized queries (never string formatting)

Parameterized queries work by separating SQL code from SQL data:

  • You write SQL with placeholders.
  • You pass values separately.
  • The driver sends them to MySQL as bound parameters.

OWASP recommends prepared statements with variable binding as the primary defense against SQL injection.

Bad (unsafe) pattern:

# SQL injection risk
sql = f"INSERT INTO users(email) VALUES ('{email}')"
cursor.execute(sql)

Good (safe) pattern:

sql = "INSERT INTO users(email) VALUES (%s)"
cursor.execute(sql, (email,))

Placeholders: %s vs %(name)s and why it matters

Most MySQL Python drivers follow DB-API conventions and use %s-style placeholders for values (even if the value is not a string). For example:

  • PyMySQL: use %s for sequences or %(name)s for dicts.
  • MySQL Connector/Python: supports %s and %(name)s, and documents its default “pyformat” style.

A practical rule:

  • Use positional parameters (%s) for simple inserts.
  • Use named parameters (%(name)s) when you have many columns and want clarity.

Table/column names are not parameters (use allowlists)

You cannot safely bind SQL identifiers (table names, column names) as parameters in standard DB-API drivers. Parameters are for values only. If you allow user input to decide a table or column name, you must validate against a hardcoded allowlist.

Example allowlist approach:

ALLOWED_TABLES = {"events", "audit_logs"}

table = user_selected_table

if table not in ALLOWED_TABLES:
    raise ValueError("Invalid table")

sql = f"INSERT INTO {table} (user_id, payload) VALUES (%s, %s)"
cursor.execute(sql, (user_id, payload))

This is still string formatting, but it’s controlled: the only permitted identifier strings are ones you choose.

Safe INSERT with mysql-connector-python (official MySQL driver)

MySQL Connector/Python documents parameter binding in cursor.execute(), including %s and %(name)s placeholders.

Single-row insert with transactions

Pattern goals:

  • Open connection
  • Start a transaction (or rely on autocommit=False)
  • Execute parameterized INSERT
  • Commit on success, rollback on error
import mysql.connector
from mysql.connector import errorcode


def insert_user(conn, email, created_at):
    sql = "INSERT INTO users (email, created_at) VALUES (%s, %s)"

    try:
        with conn.cursor() as cur:
            cur.execute(sql, (email, created_at))
        conn.commit()
        return True

    except mysql.connector.Error as err:
        conn.rollback()

        # Handle specific errors if you want (duplicate key, etc.)
        if err.errno == errorcode.ER_DUP_ENTRY:
            return False

        raise


conn = mysql.connector.connect(
    host="db.example.internal",
    user="app_writer",
    password="***",
    database="appdb",
    autocommit=False,
)

ok = insert_user(conn, "a@example.com", "2025-12-19 10:30:00")

Notes that keep this “safe” in real systems:

  • Rollback on any exception in the unit-of-work.
  • Don’t log raw SQL with user values embedded.
  • Prefer a single “data access layer” that owns commit/rollback decisions, so you don’t accidentally double-commit across functions.

Bulk insert with executemany

If you need to insert many rows, don’t loop and commit each time. Use executemany and commit once.

rows = [
    ("a@example.com", "2025-12-19 10:30:00"),
    ("b@example.com", "2025-12-19 10:31:00"),
]

sql = "INSERT INTO users (email, created_at) VALUES (%s, %s)"

try:
    with conn.cursor() as cur:
        cur.executemany(sql, rows)
    conn.commit()

except mysql.connector.Error:
    conn.rollback()
    raise

Why this matters:

  • Fewer round trips to MySQL
  • One transaction boundary for consistent integrity

Upserts safely: ON DUPLICATE KEY UPDATE

Upserts are common for idempotency (safe retries). You can still parameterize values normally:

sql = """
INSERT INTO users (email, last_seen_at)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE last_seen_at = VALUES(last_seen_at)
"""

with conn.cursor() as cur:
    cur.execute(sql, (email, last_seen_at))

conn.commit()

This helps when your system might retry writes (queues, serverless retries, flaky networks). The “unique key” becomes your guardrail.

Safe INSERT with PyMySQL / mysqlclient

PyMySQL documents that %s works for tuples/lists and %(name)s works for dict parameters. mysqlclient (MySQLdb fork) follows Python DB-API 2.0 expectations and points to PEP-249 as the primary guide.

PyMySQL insert example

import pymysql

conn = pymysql.connect(
host="db.example.internal",
user="app_writer",
password="***",
database="appdb",
autocommit=False,
)

sql = "INSERT INTO events (user_id, event_type, payload) VALUES (%s, %s, %s)"
data = (123, "signup", '{"source":"ad"}')

try:
with conn.cursor() as cur:
cur.execute(sql, data)
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()

mysqlclient insert example

import MySQLdb

conn = MySQLdb.connect(
host="db.example.internal",
user="app_writer",
passwd="***",
db="appdb",
charset="utf8mb4",
)
conn.autocommit(False)

sql = "INSERT INTO events (user_id, event_type, payload) VALUES (%s, %s, %s)"
try:
cur = conn.cursor()
cur.execute(sql, (123, "signup", '{"source":"ad"}'))
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()

Safer patterns with SQLAlchemy (recommended for larger apps)

If your app is more than a script, SQLAlchemy can reduce “hand-rolled SQL” mistakes, centralize transactions, and give you consistent patterns across databases. You still need to validate identifiers and design your permissions, but day-to-day inserts become harder to mess up.

SQLAlchemy Core insert

from datetime import datetime

from sqlalchemy import (
    Column,
    DateTime,
    Integer,
    MetaData,
    String,
    Table,
    create_engine,
)
from sqlalchemy.sql import insert

engine = create_engine(
    "mysql+mysqlconnector://app_writer:***@db.example.internal/appdb"
)

metadata = MetaData()

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("email", String(255), nullable=False, unique=True),
    Column("created_at", DateTime, nullable=False),
)

stmt = insert(users).values(
    email="a@example.com",
    created_at=datetime.utcnow(),
)

with engine.begin() as conn:
    # Transaction scope: commit/rollback handled automatically
    conn.execute(stmt)

SQLAlchemy ORM insert

from datetime import datetime

from sqlalchemy import DateTime, String
from sqlalchemy.orm import Session, declarative_base, mapped_column


Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = mapped_column(primary_key=True)
    email = mapped_column(String(255), nullable=False, unique=True)
    created_at = mapped_column(DateTime, nullable=False)


with Session(engine) as session:
    session.add(
        User(
            email="a@example.com",
            created_at=datetime.utcnow(),
        )
    )
    session.commit()

Why this is often “safer” in teams:

  • Cleaner transaction boundaries (session / begin blocks)
  • Less string-built SQL scattered around
  • More consistent handling of bulk operations and retries

Transactions, isolation, and avoiding partial writes

Use transactions whenever a logical write spans multiple statements:

  • Insert user, then insert audit log
  • Insert order, then order_items
  • Insert event, then update aggregate counters

A common production failure mode:

  • INSERT succeeds
  • Second INSERT fails (constraint, timeout, deadlock)
  • You forget rollback or commit happened too early
  • Database ends in an inconsistent state

Practical transaction guidance:

  • Keep transactions short (minimize time locks are held).
  • Commit once per unit of work.
  • If you expect contention, design for retries on transient errors (deadlocks/timeouts), but make retries idempotent (unique keys + upserts help).

Input validation and constraints that actually help

Parameterized queries stop SQL injection, but they don’t guarantee data quality. Combine:

  • Application validation (types, lengths, formats)
  • Database constraints (the final gatekeeper)

High-impact database constraints:

  • NOT NULL for required fields
  • UNIQUE for identity (email, external_id, idempotency_key)
  • FOREIGN KEY for relational integrity (when appropriate)
  • Reasonable VARCHAR lengths to prevent unbounded growth

A “safety” mindset:

  • Assume your application code will eventually have a bug.
  • Let MySQL constraints catch what slips through.
  • Handle constraint errors explicitly so the user experience is clean and your logs stay useful.

Secrets management and least-privilege MySQL users

Safe inserts aren’t only about injection—permissions matter:

  • Create a dedicated DB user for your app (or separate reader/writer users).
  • Grant only what’s needed: INSERT/SELECT on specific tables, not global privileges.
  • Avoid using admin/root credentials in application config, ever.

Operational best practice:

  • Store secrets in a secret manager or environment variables injected by your runtime.
  • Rotate credentials and use short-lived credentials where your infrastructure supports it.

Logging, auditing, and error handling without leaking secrets

What to log:

  • Operation name (e.g., CreateUser)
  • High-level identifiers (e.g., user_id, request_id, idempotency_key)
  • Error codes (duplicate key, deadlock, timeout)

What not to log:

  • Raw SQL with values interpolated
  • Passwords, tokens, personal data, payment data

Security angle:

  • Logs are often more broadly accessible than production databases.
  • A “helpful debug log” can become a data leak.

Performance: batch inserts, pooling, and latency traps

Secure can still be fast:

  • Use executemany for batches.
  • Commit once per batch, not per row.
  • Reuse connections or use a pool (especially in web apps).

Common performance traps:

  • Opening a new DB connection per request without pooling
  • Row-by-row inserts inside a loop with commits
  • Doing “read-modify-write” sequences without transactions under concurrency

A practical approach for high-throughput systems:

  • Batch rows into sensible sizes (depends on payload size and latency)
  • Use idempotency keys for safe retries
  • Push heavy writes through a queue if your web tier must stay low-latency

Copy/paste checklist

  • Use parameterized queries for every value.
  • Never build SQL with f-strings / .format() using user values.
  • Identifiers (table/column) must come from an allowlist.
  • Wrap multi-statement writes in a transaction; commit once, rollback on error.
  • Use UNIQUE constraints + upserts for idempotency and safe retries.
  • Run with least-privilege DB credentials.
  • Log errors safely (no secrets, no interpolated SQL).

Top 5 Frequently Asked Questions

No. Escaping is easy to get wrong and tends to fail in edge cases. OWASP’s primary recommendation is prepared statements / parameterized queries so inputs are treated as data, not executable SQL.
In DB-API style drivers, %s is a placeholder for a bound parameter regardless of the underlying type; the driver transmits the proper typed value separately. MySQL Connector/Python and PyMySQL both document %s / %(name)s parameter styles for binding values.
Not with standard parameter binding—parameters are for values. If you need dynamic identifiers, use a strict allowlist and then inject only those known-safe identifiers into the SQL string.
Usually no. Commit per logical unit of work (or per batch). Excessive commits hurt performance and can create inconsistent states when multiple statements are involved.
For many teams: SQLAlchemy + mysql-connector (or a mature driver) with transactions managed by context blocks, plus MySQL constraints and least-privilege DB users. The biggest win is consistent parameterization and consistent transaction scoping.

Final Thoughts

The most important takeaway is this: safe inserts are a system, not a snippet. Parameterized queries stop the most dangerous class of bugs—SQL injection—by ensuring your SQL structure is fixed before any user data is applied. But reliability comes from pairing that with disciplined transaction boundaries, database constraints that enforce reality, and least-privilege credentials that limit blast radius. When those pieces are in place, you get three outcomes at once: fewer security vulnerabilities, fewer integrity bugs, and code that’s easier for teams to maintain as the application scales.

Resources

  • OWASP SQL Injection Prevention Cheat Sheet
  • OWASP Query Parameterization Cheat Sheet
  • OWASP Injection Prevention Cheat Sheet
  • OWASP SQL Injection overview
  • MySQL Connector/Python: cursor.execute() parameter binding
  • MySQL Connector/Python: paramstyle
  • PyMySQL cursor parameter docs
  • mysqlclient user guide (DB-API compatibility)