Connecting from Application Code

Never build SQL by concatenating input#

# Vulnerable
cur.execute("SELECT * FROM customers WHERE email = '" + email + "'")

If email is ' OR '1'='1, the query returns every customer. With stacked statements or privileged accounts, attackers can do far worse. This is SQL injection, and it still tops breach reports.

Parameterized queries#

import mysql.connector

conn = mysql.connector.connect(
    host="127.0.0.1", user="shop_app", password="...", database="shop"
)
cur = conn.cursor(dictionary=True)

cur.execute("SELECT id, name FROM customers WHERE email = %s", (email,))
rows = cur.fetchall()

The driver sends the statement and the values separately, so the value can never become SQL structure. In Node with mysql2:

const [rows] = await pool.execute(
  "SELECT id, name FROM customers WHERE email = ?", [email]
);

You can only parameterize values, not identifiers. For a dynamic column or table name, check it against an allow-list in code.

Connection pooling#

Opening a connection means a TCP handshake, authentication, and session setup. Doing that per request is slow and can exhaust max_connections. A pool keeps connections open and lends them out.

 request ─▶ pool.get() ─▶ [conn1][conn2][conn3] ─▶ MySQL
            wait if all busy        reused

const pool = mysql.createPool({
  host: "127.0.0.1", user: "shop_app", password: "...", database: "shop",
  connectionLimit: 10
});

Size the pool modestly. Ten connections each running queries often outperform a hundred fighting over locks. Always release connections back, ideally in a finally block, or the pool drains and your app hangs.

Transactions in code#

try:
    conn.start_transaction()
    cur.execute("INSERT INTO orders (customer_id) VALUES (%s)", (cust_id,))
    order_id = cur.lastrowid
    for pid, qty, price in items:
        cur.execute(
            "INSERT INTO order_items (order_id, product_id, qty, unit_price) "
            "VALUES (%s, %s, %s, %s)", (order_id, pid, qty, price))
        cur.execute("UPDATE products SET stock = stock - %s "
                    "WHERE id = %s AND stock >= %s", (qty, pid, qty))
        if cur.rowcount == 0:
            raise ValueError("out of stock")
    conn.commit()
except Exception:
    conn.rollback()
    raise

Note AND stock >= %s in the update: it checks and decrements in one atomic statement, and rowcount tells you if it worked. That avoids a read-then-write race.

Wrap deadlock errors (1213) in a retry loop, with a small limit.

Other real-world habits#

• Use utf8mb4 in the connection charset too.
• Set timeouts so a stuck query doesn't hold a request forever.
• Keep credentials in environment variables or a secrets manager.
• Log slow queries with their parameters stripped of secrets.
• Select only the columns you need.

The N+1 problem#

for c in customers:                      # 1 query
    c.orders = query("... WHERE customer_id = %s", c.id)   # N queries

One query to fetch customers plus one per customer. Fetch all in a single query with WHERE customer_id IN (...) or a join, then group in code.

Common mistakes#

• String-built SQL.
• Forgetting to release pooled connections.
• Read-modify-write on counters in app code.
• The N+1 query pattern hidden inside an ORM loop.

Practice#

Write a small script that connects with a pooled, least-privilege account, inserts an order with items inside a transaction, and rolls back when stock is insufficient. Then deliberately try a classic injection string against both a concatenated and a parameterized version and compare the results.