SQL from Application Code

In a job, you rarely type SQL into a console. A PHP page, a Python script or a Node.js API builds the query, sends it to MySQL with values that came from a user, and turns the rows into a web page or JSON. Most security holes and many slow pages appear at exactly this point, where SQL meets application code.

You saw in Users, Privileges & Backups how SQL injection works and that prepared statements stop it. This lesson goes further into everyday practice: connecting correctly in three languages, the cases where placeholders cannot be used (table names, sort columns, IN lists), escaping LIKE wildcards, binding LIMIT, keeping secrets out of code, and handling errors and transactions.

The rule: SQL text is fixed, values travel separately

A prepared statement sends the SQL text with placeholders, such as ?, and then the values on their own. The database parses the SQL first, so a value can never change the structure of the query, whatever quotes or keywords it contains. Every language has its own spelling of the same idea:

The %s in Python looks like string formatting, but it is not: you pass the values as a separate argument, and the library sends them safely. Writing sql % value or an f-string yourself would bring injection back.

Connecting from PHP with PDO

A good PDO connection sets the character set, throws exceptions on errors, and returns rows as associative arrays:

<?php
declare(strict_types=1);

$dsn = 'mysql:host=localhost;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, getenv('SHOP_DB_USER'), getenv('SHOP_DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT first_name, city FROM customers WHERE state = ? ORDER BY first_name');
$stmt->execute(['Haryana']);

foreach ($stmt as $row) {
    echo $row['first_name'], ' (', $row['city'], ")\n";
}

Aarav (Rohtak)
Isha (Gurugram)
Vikram (Rohtak)

• charset=utf8mb4 in the DSN stops garbled Hindi and ₹ signs (see Character Sets & Collations).
• ERRMODE_EXCEPTION has been the default since PHP 8.0, but writing it makes the intent clear in older projects.
• The user name and password come from environment variables, not from the source code. Code is copied, shared and pushed to Git; a password inside it leaks with it. Use an account with only the privileges the application needs.

The full PHP side, with forms and HTML escaping, is covered in CRUD with Prepared Statements.

Python and Node.js

The same query from Python, using MySQL Connector/Python (pip install mysql-connector-python):

import os
import mysql.connector

conn = mysql.connector.connect(
    host="localhost", database="shop", charset="utf8mb4",
    user=os.environ["SHOP_DB_USER"], password=os.environ["SHOP_DB_PASSWORD"],
)
cur = conn.cursor(dictionary=True)
cur.execute("SELECT first_name, city FROM customers WHERE state = %s ORDER BY first_name", ("Haryana",))
for row in cur.fetchall():
    print(row["first_name"], row["city"])

cur.execute("UPDATE products SET stock = stock - %s WHERE product_id = %s", (1, 8))
conn.commit()   # Connector/Python does not autocommit by default
conn.close()

Note the comma in ("Haryana",): the values must be a tuple, and a single value in brackets without a comma is just a string. Unlike PHP's PDO, Python database libraries start with autocommit off, so changes are lost unless you call conn.commit().

In Node.js with the mysql2 package, use a connection pool, a set of open connections that requests borrow and return, instead of connecting on every request:

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "localhost", database: "shop", charset: "utf8mb4",
  user: process.env.SHOP_DB_USER, password: process.env.SHOP_DB_PASSWORD,
  connectionLimit: 10,
});

const [rows] = await pool.execute(
  "SELECT first_name, city FROM customers WHERE state = ? ORDER BY first_name",
  ["Haryana"],
);
console.log(rows.map((r) => r.first_name));

[ 'Aarav', 'Isha', 'Vikram' ]

A pool also protects the server: MySQL allows a limited number of connections (max_connections, 151 by default), and an application that opens a new one for every request can hit ERROR 1040 (HY000): Too many connections on a busy day.

When a placeholder cannot help

Placeholders stand for values only. They cannot replace table names, column names, ASC/DESC or SQL keywords. A product list with a "sort by" dropdown is a common trap:

// DANGEROUS: $_GET['sort'] goes straight into the SQL text
$sql = "SELECT product_name, price FROM products ORDER BY " . $_GET['sort'];

The fix is a whitelist: map the allowed choices to fixed SQL text, and fall back to a default for anything else:

$sortOptions = [
    'price_low'  => 'price ASC',
    'price_high' => 'price DESC',
    'name'       => 'product_name ASC',
];
$orderBy = $sortOptions[$_GET['sort'] ?? ''] ?? 'product_name ASC';

$stmt = $pdo->prepare("SELECT product_name, price FROM products WHERE category = ? ORDER BY $orderBy");
$stmt->execute([$category]);

User input only ever chooses a key; the SQL text comes from your own array.

An IN list with a variable number of values needs one placeholder per value. Build the ? list, never the values:

$ids = [1, 4, 9];                                            // e.g. from a basket
$marks = implode(', ', array_fill(0, count($ids), '?'));     // "?, ?, ?"
$stmt = $pdo->prepare("SELECT product_name, price FROM products WHERE product_id IN ($marks)");
$stmt->execute($ids);

Check that the list is not empty first: IN () is a syntax error.

LIKE searches and LIMIT values

A bound value is safe from injection, but inside LIKE the characters % and _ still act as wildcards. A customer who searches reviews for "100%" means the text 100%, not "100 followed by anything":

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  review_text VARCHAR(200) NOT NULL
);
INSERT INTO reviews (review_id, product_id, review_text) VALUES
  (1, 7, 'Battery gave me 100% charge in an hour'),
  (2, 7, 'Plays 1000 songs without a break'),
  (3, 9, 'Copied 100 GB of photos quickly');

-- The search text pasted in as typed:
SELECT review_id, review_text FROM reviews WHERE review_text LIKE '%100%%';

-- The % typed by the user escaped with ! :
SELECT review_id, review_text FROM reviews WHERE review_text LIKE '%100!%%' ESCAPE '!';

review_id | review_text
----------+---------------------------------------
1         | Battery gave me 100% charge in an hour
2         | Plays 1000 songs without a break
3         | Copied 100 GB of photos quickly
(3 rows)

review_id | review_text
----------+---------------------------------------
1         | Battery gave me 100% charge in an hour
(1 row)

In code, put ESCAPE '!' in the SQL, escape !, % and _ in the user's text with PHP's str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $term), add your own % at both ends, and bind the result as the value.

LIMIT and OFFSET take placeholders too, but PDO's default emulated prepares send bound values as quoted strings, and LIMIT '10' is a syntax error. Bind them as integers:

$stmt = $pdo->prepare('SELECT order_id, order_date FROM orders ORDER BY order_id LIMIT ? OFFSET ?');
$stmt->bindValue(1, $perPage, PDO::PARAM_INT);
$stmt->bindValue(2, ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();

Errors, transactions and retries

• Never show database errors to users. A message like "Unknown column 'pasword' in 'where clause'" helps attackers. Log the details on the server and show a polite general message.
• Wrap related writes in a transaction, and roll back in the error handler, as shown in Transactions.
• Retry on deadlock. If the error is MySQL error 1213, run the whole transaction again, a small number of times (see Isolation Levels, Locks & Deadlocks).
• Avoid one query per row. Loading 50 orders and then running a separate query for each order's items is the N+1 problem; fetch the items for all 50 orders with one IN list or a join (see Query Performance Tuning).

ORMs and query builders, such as Laravel's Eloquent, Django's ORM or SQLAlchemy, generate prepared statements for you. They still have "raw" methods for hand-written SQL, and the same rules apply there: bind values, whitelist identifiers.

Key points

• Keep SQL text fixed and send values separately with placeholders: ? in PDO and mysql2, %s in Connector/Python.
• Set utf8mb4 on the connection, read credentials from environment variables, and use a least-privilege account.
• Placeholders cannot hold table names, column names or ASC/DESC; choose them from a whitelist.
• Build one ? per value for IN lists, escape % and _ for LIKE, and bind LIMIT values as integers.
• Python libraries need commit(); Node.js apps should use a connection pool.
• Log database errors privately and retry deadlocked transactions.

SELECT product_name, price
FROM products
WHERE category = 'Accessories' AND price <= 2000
ORDER BY price;