Using MySQL from code and avoiding SQL injection

Your application is the real client

So far you have typed SQL into a terminal. In a real project the SQL is sent by a program: a PHP site, a Python service, a Node.js API. The program uses a driver, a library that opens a connection to MySQL, sends statements and returns rows as arrays or objects. The commands you have learned are exactly what the driver sends. The new topic is how the program builds those statements, because that is where most database security problems begin.

Connecting

Each language has a standard driver. In all of them you supply the host, port, database, user, password and character set. Read the password from an environment variable, never from the source code.

PHP, using PDO:

<?php
$pdo = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

Python, using mysql-connector-python:

import os
import mysql.connector

conn = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    database="shop",
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    charset="utf8mb4",
)

Node.js, using mysql2:

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "127.0.0.1",
  port: 3306,
  database: "shop",
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  charset: "utf8mb4",
});

Notice utf8mb4 in each, which matches the character set lesson. Web servers handle many requests at once, so Node and many frameworks use a connection pool, a set of ready connections that requests borrow, instead of opening a new connection per request.

How SQL injection works

Imagine a search box for customers. A beginner builds the query by gluing the typed text into a string:

name = request.args["name"]
cursor.execute("SELECT first_name, city FROM customers WHERE first_name = '" + name + "'")

If a visitor types Asha, the query is fine. If they type x' OR '1'='1, the text of the query becomes something different:

SET @user_input = 'x'' OR ''1''=''1';
SET @unsafe = CONCAT('SELECT first_name, city FROM customers WHERE first_name = ''', @user_input, '''');
SELECT @unsafe AS query_that_would_run;

+--------------------------------------------------------------------------+
| query_that_would_run                                                     |
+--------------------------------------------------------------------------+
| SELECT first_name, city FROM customers WHERE first_name = 'x' OR '1'='1' |
+--------------------------------------------------------------------------+

Look at the result. The quote the attacker typed closed the string, and OR '1'='1' is a condition that is always true. Run that text as SQL and it returns every customer:

USE shop;
PREPARE unsafe_stmt FROM @unsafe;
EXECUTE unsafe_stmt;
DEALLOCATE PREPARE unsafe_stmt;

+------------+-----------+
| first_name | city      |
+------------+-----------+
| Asha       | Delhi     |
| Ben        | London    |
| Chloe      | Paris     |
| Daniel     | Seoul     |
| Elena      | Rome      |
| Farid      | Mumbai    |
| Grace      | Lagos     |
| Hiro       | Tokyo     |
| Isabel     | Lisbon    |
| Jack       | New York  |
| Kavya      | Bengaluru |
| Liam       | Dublin    |
+------------+-----------+

This is SQL injection. With a little more cleverness an attacker can read other tables with UNION SELECT, bypass a login form (' OR 1=1 -- as the password), or, if the account has the rights, modify and delete data. It has been among the most damaging web vulnerabilities for decades and it is entirely preventable.

The fix: parameters

A parameterised query sends the SQL and the values separately. The SQL text has placeholders, and the driver (or server) supplies the values as data. There is no way for a value to change the structure, because by the time the value arrives, the structure is already fixed. You saw the raw mechanism in the variables lesson:

PREPARE safe_stmt FROM 'SELECT first_name, city FROM customers WHERE first_name = ?';
EXECUTE safe_stmt USING @user_input;
DEALLOCATE PREPARE safe_stmt;

The same malicious text now returns nothing, because MySQL looks for a customer whose first name is literally x' OR '1'='1. In application code this is the normal way to write every query:

PHP (PDO):

$stmt = $pdo->prepare('SELECT first_name, city FROM customers WHERE first_name = :name');
$stmt->execute(['name' => $_GET['name']]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Python:

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT first_name, city FROM customers WHERE first_name = %s", (name,))
rows = cursor.fetchall()

Node.js:

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

The placeholder style differs (:name, %s or ?), but the idea does not. Note that %s in Python is a placeholder for the driver, not Python string formatting: you pass the values as a separate argument and never write "..." % name.

The same rule applies to INSERT, UPDATE, DELETE, LIKE patterns and IN lists. For a LIKE, put the wildcards in the value ("%" . $term . "%"), not in the SQL text. For an IN list, create one placeholder per value.

What parameters cannot do

Placeholders replace values. They cannot stand for table names, column names, ORDER BY directions or keywords. If a user can choose the sort column, check it against a fixed list in your code:

allowed = {"name": "first_name", "city": "city", "joined": "signup_date"}
column = allowed.get(request.args.get("sort"), "first_name")
cursor.execute(f"SELECT first_name, city FROM customers ORDER BY {column}")

Here the final text is built only from values you wrote yourself, never from the visitor's input.

Defence in depth

Parameters should be your first line of defence, but a careful project adds more layers:

1. A least-privilege database user. The web application should use an account that can only SELECT, INSERT, UPDATE and DELETE on its own database, as in the users lesson. Then an injection flaw cannot drop tables or read other databases.
2. Validate input by type and length (an id must be an integer; a country must be in your list). Validation reduces risk, but it is not a replacement for parameters.
3. Hide database errors from visitors. Detailed messages help attackers. Log them privately and show a generic message.
4. Hash passwords with a purpose-built algorithm (such as bcrypt or Argon2) in your application. Never store plain passwords. The Hash & Encode tool shows what hashing does, but real password hashing should use a library with a salt and a work factor.
5. Keep MySQL off the public internet. Only the application servers should be able to reach port 3306.
6. Keep software updated, including the driver and any ORM.

ORMs and query builders

Frameworks such as Laravel (Eloquent), Django, SQLAlchemy, Prisma and Sequelize build SQL for you and use parameters by default, which prevents most injection. They have escape hatches for raw SQL (DB::raw, .raw(), text()), and those are where injection can come back. The rule is the same: pass values as bindings, never concatenate them.

Handling results and errors

Always check for errors and close or release connections. In PDO set ERRMODE_EXCEPTION, as above. Use transactions for multi-step changes (see the transactions lesson) so a failed request cannot leave half-written data. Read only the columns you need, and limit result sizes with LIMIT and pagination, so a request cannot pull a million rows into memory.

Common mistakes

• Building SQL by string concatenation or f"" formatting with user input.
• Trying to "escape" quotes by hand, which is easy to get wrong.
• Using the root account in the connection settings.
• Storing the database password in the code repository.
• Showing raw SQL errors to visitors.
• Opening a new connection for every query in a busy application instead of pooling.

Exercise

Look at this PHP snippet and rewrite it safely with PDO: $pdo->query("SELECT * FROM orders WHERE customer_id = " . $_GET['id'] . " AND status = '" . $_GET['status'] . "'");. Then explain in one sentence why validating that id is a number is still useful.

$stmt = $pdo->prepare('SELECT id, order_date, status FROM orders WHERE customer_id = :id AND status = :status');
$stmt->execute(['id' => (int) $_GET['id'], 'status' => $_GET['status']]);
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);