Skip to content
Tutorials Center
My learning

Topics

Lesson 60 of 75 ยท Security and Administration

Securing MySQL

Harden MySQL: TLS connections, network exposure, mysql_secure_installation, SQL injection and prepared statements, data-at-rest encryption, auditing and secrets handling.

A database holds the most valuable thing your application owns, so attackers aim at it directly or through the application in front of it. MySQL security is not one setting. It is a stack of small decisions: who can reach the port, whether traffic is encrypted, what each account may do, whether application code can be tricked into running attacker-supplied SQL, whether the files on disk are readable if a drive is stolen, and whether you would notice a break-in.

This lesson walks through that stack in the order an attacker would meet it: the network, the connection, the server's first-run hardening, the application (SQL injection and prepared statements), storage encryption, auditing, and secrets handling. Account design is in Users, Privileges and Roles. Server settings and admin commands are read-only here; the SQL injection section has a live demo you can run.

Layers of defense

Network Keep the port off the internet.
Connection TLS, plus least-privilege accounts.
Application Prepared statements stop injection.
Storage Encrypt data at rest.
1 / 2

Network exposure

The simplest protection is to make the server unreachable. MySQL listens on TCP port 3306 (the classic protocol) and 33060 (the X protocol). If the application and database run on one host, close both to the network:

INI
[mysqld]
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
# or disable the X protocol entirely if you do not use it
mysqlx = OFF

If the application runs on another machine, bind to a private network address only (for example 10.0.1.5), and add a firewall rule or cloud security group that allows only the application servers' addresses to reach the port. Never publish port 3306 to the whole internet. If a person needs access from outside, make them connect through a VPN or an SSH tunnel (ssh -L 3307:127.0.0.1:3306 user@dbhost) instead of opening the port.

Combine this with narrow host patterns on accounts, such as 'app'@'10.0.1.%'. Both layers should agree: the network blocks strangers, and the account refuses logins from unexpected hosts.

First-run hardening

After installing MySQL from a package or archive, run the bundled script:

Terminal
mysql_secure_installation

It walks you through setting a strong password for root, removing anonymous users and the test database (where present), and disabling remote root login. In recent versions (8.0 and later) many of those defaults are already safe, but the script is still a useful checklist, and you can run it again on a server you inherited. Beyond it:

  • Keep root for administration only, from the local host, and never let an application use it.
  • Remove accounts nobody can explain (SELECT user, host FROM mysql.user).
  • Keep local_infile = OFF unless you need LOAD DATA LOCAL, because it lets a malicious server request files from the client.
  • Set secure_file_priv to a dedicated folder (or empty-disable file import and export), so LOAD DATA INFILE and SELECT ... INTO OUTFILE cannot touch arbitrary paths (Bulk Loading and Exporting Data).
  • Do not grant the FILE privilege to application accounts.
  • Run the server as an unprivileged operating-system user, never as root.
  • Apply security patches: MySQL publishes fixes in quarterly releases, and running an unpatched server is the commonest avoidable risk (Upgrading and Monitoring MySQL).

Encrypting connections with TLS

Without TLS, queries, results and (for some authentication methods) credentials cross the network in a form that anyone on the path can read. MySQL 8 enables TLS automatically and generates a self-signed certificate on first start, but clients still choose whether to use it. Check a live session:

SQL
SHOW SESSION STATUS LIKE 'Ssl_cipher';        -- empty means no TLS
SHOW VARIABLES LIKE 'tls_version';             -- TLSv1.2,TLSv1.3
SHOW VARIABLES LIKE 'require_secure_transport';

To make the server insist on encrypted connections for all TCP clients, set:

INI
[mysqld]
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3
ssl_ca   = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key  = /etc/mysql/certs/server-key.pem

Use certificates signed by your own certificate authority (or your cloud provider's), not the auto-generated ones, so that clients can verify the server. On the client side, encrypting is not enough; you also have to verify that you reached the right server, otherwise a man-in-the-middle can present their own certificate:

Terminal
mysql --host=db.internal --user=shop_app --password \
      --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/certs/ca.pem

The client modes, from weakest to strongest, are DISABLED, PREFERRED (the default, which silently falls back to plaintext), REQUIRED, VERIFY_CA and VERIFY_IDENTITY. Use VERIFY_IDENTITY in production, and you can also demand TLS per account with CREATE USER ... REQUIRE SSL (or REQUIRE X509 for client certificates). Replication links between servers should use TLS as well (The Binary Log and Replication).

SQL injection and prepared statements

SQL injection is the most common attack on database-backed applications, and it has nothing to do with MySQL itself. It happens when application code builds a SQL statement by pasting user input into a string, and the input contains SQL syntax that changes the statement's meaning.

Imagine a login check written like this (PHP, but the idea is language-independent):

PHP
$sql = "SELECT id FROM users WHERE name = '" . $_POST['name'] . "' AND password_hash = '" . $hash . "'";

If the visitor types x' OR '1'='1 as the name, the text sent to the server becomes ... WHERE name = 'x' OR '1'='1' AND password_hash = '...'. Because AND binds tighter than OR, the condition '1'='1' is always true, and the login check passes. Worse input can end a statement and add another, read other tables with UNION SELECT, or write files if the FILE privilege exists.

The demo below runs the injected statement exactly as the server would receive it, and then runs the safe version, in which the same hostile text is treated as a plain value. It is a live demo of the principle in SQLite, whose string handling in WHERE clauses matches MySQL's for this purpose:

SQL Injection in action: concatenated text versus a value

Press Run (or Ctrl + Enter) to see the result here.

The first query returns every row, including secrets for all three users: the input rewrote the logic. The second returns nothing, because MySQL now looks for a user whose name is literally x' OR '1'='1. The third is an ordinary lookup. Notice that in query 2 the quotes had to be doubled by hand. You should never do that by hand in real code: let the driver handle it with prepared statements.

Prepared statements

A prepared statement sends the SQL text and the data separately. The server parses the statement with ? placeholders first, and the values arrive later and can never be reinterpreted as SQL:

PHP
$stmt = $pdo->prepare('SELECT id FROM users WHERE name = ? AND password_hash = ?');
$stmt->execute([$_POST['name'], $hash]);
Python
cursor.execute("SELECT id FROM users WHERE name = %s AND password_hash = %s", (name, pw_hash))
JavaScript
const [rows] = await pool.execute('SELECT id FROM users WHERE name = ? AND password_hash = ?', [name, pwHash]);

You can also see the mechanism in plain SQL with server-side prepared statements:

SQL
PREPARE find_user FROM 'SELECT id FROM users WHERE name = ?';
SET @n = 'x'' OR ''1''=''1';
EXECUTE find_user USING @n;       -- returns no rows
DEALLOCATE PREPARE find_user;

Rules that keep you safe:

  • Always pass values as parameters. Never build SQL by concatenating or formatting strings with user input, including in "internal" admin tools.
  • Placeholders cannot stand for identifiers or keywords. A column name or ORDER BY direction cannot be a ?. Choose them from a fixed allow-list in your code ($dir = $input === 'desc' ? 'DESC' : 'ASC'), and quote identifiers with backticks only after validating them.
  • Watch LIKE. The value is safe, but % and _ inside it still act as wildcards. Escape them if the user should search for literal text.
  • Some drivers emulate prepares by escaping on the client (PDO does this unless you disable emulation). With utf8mb4 set in the connection, this is safe, but turning emulation off is a stricter choice (MySQL from PHP with PDO).
  • ORMs help, but raw query escape hatches reintroduce the risk. Check every whereRaw, execute(f"...") and string-built filter.
  • Limit the damage. Even with careful code, give the application account only the privileges it needs, so injection cannot drop tables or read system tables (Users, Privileges and Roles).

The second demo shows two more effects: a UNION SELECT that steals data from a table the query was never meant to touch, and the LIKE wildcard caveat. A user-supplied % is harmless to the database but changes what the search means, unless you escape it:

SQL UNION theft and LIKE wildcards

Press Run (or Ctrl + Enter) to see the result here.

Query 1 returns the secret keys in the title column: the attacker added a whole second query. Query 2 matches all three products because a lone % is a wildcard. Query 3 finds only 100% cotton shirt, because the % was escaped. Placeholders protect you from query 1 completely; the LIKE case is a separate question about meaning, which you handle by escaping %, _ and the escape character itself.

Types of SQL injection

Attackers use several techniques, and which one works depends on what the application shows them. The names help you recognize them in security reports and tools.

Type How the attacker learns something MySQL-specific detail
Error-based Database error text is shown on the page and contains data EXTRACTVALUE() and UPDATEXML() put a query result into an XPath error message
UNION-based Rows from another query are appended to the normal result The column count and types must match, so the attacker first probes with ORDER BY n
Boolean-blind The page differs between "true" and "false" but shows no data SUBSTRING(secret, 1, 1) = 'a' leaks one character per request
Time-based blind The page looks identical, so the attacker measures response time SLEEP(n) and BENCHMARK() delay the answer when a condition is true
Out-of-band The server is made to send data over another channel Rare in MySQL: LOAD_FILE() on a Windows UNC path, or INTO OUTFILE if file privileges and secure_file_priv allow it
Second-order Hostile text is stored safely first, then pasted into SQL later The first insert is parameterized, but a later report, cron job or admin tool concatenates the stored value

Here is what each looks like against a query such as SELECT id, name FROM users WHERE id = $id. All of these statements were run on a MySQL 8.4 server with a two-row users table (id, name, secret):

SQL
-- UNION-based: first find the column count. ORDER BY 3 fails, so there are 2 columns
SELECT id, name FROM users WHERE id = 1 ORDER BY 3;
-- ERROR 1054 (42S22): Unknown column '3' in 'order clause'

-- A UNION with the wrong number of columns is rejected too
SELECT id, name FROM users WHERE id = 9 UNION SELECT 1, 2, 3;
-- ERROR 1222 (21000): The used SELECT statements have a different number of columns

-- With two columns it works, and the secret appears where a name should be
SELECT id, name FROM users WHERE id = 9 UNION SELECT 1, secret FROM users LIMIT 1;
-- 1 | alpha-key

-- Error-based: the stolen value is inside the error message
SELECT id, name FROM users WHERE id = 1
  AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT secret FROM users LIMIT 1)));
-- ERROR 1105 (HY000): XPATH syntax error: '~alpha-key'

-- Boolean-blind: a row comes back when the guess is right, nothing when it is wrong
SELECT id, name FROM users WHERE id = 1
  AND SUBSTRING((SELECT secret FROM users WHERE name = 'ada'), 1, 1) = 'a';   -- 1 row
SELECT id, name FROM users WHERE id = 1
  AND SUBSTRING((SELECT secret FROM users WHERE name = 'ada'), 1, 1) = 'z';   -- 0 rows

-- Time-based blind: the response is delayed by one second only when the guess is right
SELECT COUNT(*) FROM users WHERE id = 1
  AND IF(SUBSTRING((SELECT secret FROM users WHERE name = 'ada'), 1, 1) = 'a', SLEEP(1), 0);

Blind techniques look slow, but tools automate them and need only a few hundred requests to read a short secret. So "the page shows no database output" is not a defense. Two more notes:

  • Stacked queries (...; DROP TABLE users) work only if the driver allows several statements in one call. Many drivers turn this off by default, but some configurations (for example PDO with emulated prepares) allow it. Do not rely on it being off.
  • Second-order injection defeats code that only escapes at the entry point. A username stored safely as x' OR '1'='1 is harmless until another piece of code builds SQL from the stored value.

All of these have the same root cause, so they have the same fix: never put input into SQL text. A prepared statement sends the query shape first and the values later, so the input can only ever be a value:

PHP
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE id = ?');
$stmt->execute([$_GET['id']]);          // "9 UNION SELECT 1, secret FROM users" is just a failed number match

This applies to values read from your own database too, which is the lesson of second-order attacks. The same fix also removes the need to hide errors, but you should still turn off detailed error output in production, because it helps attackers who find a hole elsewhere (see the checklist below).

The first demo plays out boolean-blind extraction in SQLite: the "attacker" tests candidate characters and the only signal is whether a row exists. The second shows a second-order attack, where the dangerous text enters the database safely and does harm later.

SQL Boolean-blind: yes/no answers leak a secret (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

The first query returns one row (the guess was right) and the second returns none. The last query finds l, the second character of alpha-key. Repeating that for each position reveals the whole secret without a single piece of it ever appearing on the page.

SQL Second-order injection: safe insert, unsafe later query (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

The registration insert is harmless, and the table holds the odd-looking username. The report query that pastes it in returns both private notes, while the parameterized version returns nothing. Time-based, error-based and out-of-band techniques cannot run in SQLite (SLEEP, EXTRACTVALUE and LOAD_FILE are MySQL functions), so they appear only as the read-only statements above.

For the standard SQL view of the same attacks, see SQL Injection.

Encryption at rest

TLS protects data in motion. Encryption at rest protects the files on disk from someone who steals a drive, a backup or a virtual-machine snapshot. InnoDB supports transparent data encryption (TDE): data is encrypted when it is written to disk and decrypted when read into the buffer pool, with no change to your queries.

SQL
-- keyring component must be installed and configured first
CREATE TABLE customers_private (
  id INT PRIMARY KEY,
  email VARCHAR(255)
) ENCRYPTION = 'Y';

ALTER TABLE orders ENCRYPTION = 'Y';

The design uses two keys. A tablespace key encrypts each tablespace file, and a master key, stored in a keyring (a local file component for tests, or a key-management service or hardware module in production), encrypts the tablespace keys. Without access to the keyring, the data files are unreadable. The redo log, undo log and binary log can be encrypted too (innodb_redo_log_encrypt, innodb_undo_log_encrypt, binlog_encryption), and default_table_encryption = ON makes new tables encrypted by default.

Know what it does not do: it does not hide data from someone who can log in and run SELECT, because the server decrypts for authorized sessions, and it does not replace access control or application-level encryption of very sensitive fields. Store the master key outside the database host, back it up separately from the data, and test that you can restore both. Losing the key means losing the data. Cloud-managed MySQL services usually offer disk or database encryption with a click (High Availability: Group Replication, InnoDB Cluster and Managed Services).

For individual fields such as national ID numbers, encrypt in the application with a vetted library before the data reaches MySQL, and keep the keys in a secrets manager. Passwords must never be stored encrypted at all: store only a slow salted hash (bcrypt, Argon2) produced by the application.

Auditing

Security also means noticing. Options, from simple to complete:

  • Error log and general settings. Failed logins and server warnings go to the error log. Watch for bursts of Access denied messages.
  • Performance Schema and sys. You can inspect recent statements, but these are not a durable audit trail.
  • The general query log records every statement. It is useful for short debugging, but heavy, and it also writes secrets from IDENTIFIED BY clauses unless statements are rewritten, so do not leave it on in production.
  • MySQL Enterprise Audit (a commercial plugin) writes structured audit records with filtering rules.
  • Open-source options: the Percona Audit Log plugin, the MariaDB audit plugin adapted for MySQL, and proxy-based or cloud audit services such as those in RDS.
  • Binary log. It records data changes (not reads), which helps investigate what changed.

Decide what you need to answer: "who logged in", "who ran DDL", "who read the payments table". Send audit output to a place the database administrators cannot silently edit, for example a central log service.

Secrets handling

Database passwords leak far more often through carelessness than through cleverness:

  • Do not commit passwords to version control. Keep them in environment variables, a secrets manager (Vault, AWS Secrets Manager, your platform's secret store) or a protected file outside the web root. In Laravel that means .env kept out of git and out of the public folder.

  • Use the client's option files or mysql_config_editor rather than --password=... on the command line, which shows up in the process list and shell history:

    Terminal
    mysql_config_editor set --login-path=shop --host=db.internal --user=shop_app --password
    mysql --login-path=shop
    
  • Give each environment its own credentials; production secrets should not exist on developer laptops.

  • Rotate passwords on a schedule and after any suspected exposure; the dual-account trick (ALTER USER ... RETAIN CURRENT PASSWORD) lets you change the password without downtime while applications are redeployed.

  • Keep secrets out of logs, error pages and backups that are stored with weaker protection than the database itself.

A short hardening checklist

  1. Port closed to the internet, bound to a private address, firewall limited to known clients.
  2. TLS required, verified by clients, with certificates from a proper CA.
  3. No anonymous accounts, root restricted, one least-privilege account per application.
  4. Strong passwords, a rotation plan and secrets kept out of code and logs.
  5. Prepared statements everywhere, with allow-lists for identifiers.
  6. Encryption at rest for disks, backups and sensitive tables, with the keys stored apart.
  7. Audit logging you actually look at, and security patches applied on schedule.
  8. Backups encrypted, stored elsewhere, and restores tested (Backup and Restore).

What ran live here

The injection demos ran in SQLite, showing the effect of pasted text versus a properly quoted value, boolean-blind extraction and a second-order attack. The error-based, UNION and time-based statements in the injection types section were run on a MySQL 8.4 server and are shown as read-only code. Everything about TLS, mysql_secure_installation, accounts, encryption, auditing and server options is MySQL-specific and shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What makes SQL injection possible?

    When input is concatenated into SQL, it can contain syntax that changes the statement. Prepared statements keep code and data separate, so input stays a value.
  2. 2. Which client SSL mode both encrypts the connection and checks that the certificate matches the server name?

    VERIFY_IDENTITY validates the certificate chain and the host name. PREFERRED may fall back to plaintext, REQUIRED does not authenticate the server, and VERIFY_CA skips the host name check.
  3. 3. A search page lets users pick the sort direction. How should you handle ASC or DESC safely?

    Placeholders can only stand for values, not keywords or identifiers. Map the input to a hard-coded ASC or DESC instead.
  4. 4. What does transparent data encryption in InnoDB protect against?

    Data is encrypted on disk and decrypted for authorized sessions. It protects files at rest, while TLS protects traffic and prepared statements protect against injection.
  5. 5. Where should an application's database password live?

    Passwords committed to a repository tend to leak. Keep them in environment variables or a secrets store and give each environment its own credentials.

Summary

  • Keep MySQL off the public internet: bind to a private address, firewall the port and use a VPN or SSH tunnel for people.
  • Run mysql_secure_installation, remove stray accounts, restrict FILE, local_infile and secure_file_priv, and apply patches.
  • Require TLS (require_secure_transport) and have clients verify the server with VERIFY_IDENTITY.
  • SQL injection comes from pasting input into SQL; prepared statements keep values separate, and identifiers need allow-lists.
  • InnoDB tablespace encryption protects data at rest; keep the keyring apart from the data.
  • Audit what matters, and keep passwords in a secrets manager, never in code or command lines.

Next: Backup and Restore.