Security Best Practices

Introduction

Building a database that works is only half the job. A database that quietly leaks customer data, gets wiped by an attacker, or is hijacked through a careless query is a disaster no amount of clever schema design can fix. Security is not an optional extra you bolt on at the end — it is a habit you build into how you configure MySQL and how you write the queries that talk to it.

In the User Management and Roles & Privileges lessons, you learned how to create accounts and grant precise privileges instead of handing out blanket access. This lesson builds directly on that foundation and rounds it out with the other pillars of a secure MySQL setup: never using root for applications, defending against SQL injection, staying patched, protecting data in transit and at rest, and locking down network access.

Never Use Root for Application Connections

The root account in MySQL has unlimited power — it can read, modify, or delete any data, create and drop users, and change server configuration. That is exactly why it should never be the account your application code connects with.

If an application using the root account has a bug, or an attacker finds a way to inject SQL through it, they inherit full control of the entire server, not just the one database the application actually needs. A dedicated, narrowly-scoped account limits the damage a mistake or an attack can cause.

-- Bad: the application connects as root
-- (never do this in real applications)

-- Good: create a dedicated account with only
-- the privileges the application actually needs
CREATE USER 'school_app'@'%' IDENTIFIED BY 'Str0ng!AppPass_2026';

GRANT SELECT, INSERT, UPDATE, DELETE
ON school.*
TO 'school_app'@'%';

FLUSH PRIVILEGES;

SQL Injection and Prepared Statements

SQL injection happens when untrusted input (like text typed into a login form) is concatenated directly into a SQL string. If an attacker types SQL syntax instead of a normal value, that syntax can be executed by the database exactly as if it had always been part of the query.

Prepared statements (also called parameterized queries) solve this completely. The SQL structure is sent to MySQL first, with placeholders standing in for values. The actual values are sent separately afterward, so MySQL always treats them as pure data — never as SQL code, no matter what characters they contain.

// DANGEROUS - never do this
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '" . $username .
       "' AND password = '" . $password . "'";

// If $username is: admin' -- 
// the query becomes:
// SELECT * FROM users WHERE username = 'admin' -- ' AND password = '...'
// Everything after -- is now a SQL comment - the password check is skipped!

// SAFE - values are always treated as data
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE username = ? AND password = ?'
);
$stmt->execute([$username, $password]);

// Even if $username is: admin' -- 
// MySQL simply looks for a user literally named  admin' -- 
// No injection is possible.

Keeping MySQL Updated

Like any software, MySQL occasionally has security vulnerabilities discovered in it. The MySQL team releases patched versions to fix them. Running an old, unpatched version of MySQL means known, publicly documented vulnerabilities are sitting open on your server.

Encryption and SSL/TLS

Two different things need protecting: data sitting in the database ("at rest") and data traveling between the application and the database ("in transit").

For sensitive columns — passwords, national ID numbers, payment details — never store the raw value. Passwords should be hashed with a one-way algorithm designed for the job (like bcrypt) at the application layer, not just encrypted. Other sensitive fields can be encrypted using application-level encryption libraries, or MySQL's built-in AES_ENCRYPT()/AES_DECRYPT() functions, so that even someone with raw file access to the database cannot read the plain values.

For data in transit, MySQL supports SSL/TLS connections so that traffic between your application and the database server is encrypted and cannot be read or tampered with if intercepted on the network. You can require it per account:

ALTER USER 'school_app'@'%' REQUIRE SSL;

Restricting Network Access

A database server should almost never be reachable directly from the public internet. The fewer places a connection can come from, the fewer chances an attacker has to even try to log in.

-- Restrict root to local connections only
SELECT user, host FROM mysql.user WHERE user = 'root';
-- host should be 'localhost', not '%'

Security Checklist

Common Mistakes

Key Takeaways

Summary

MySQL security is not one single setting — it is a set of habits layered together: least-privilege accounts, prepared statements, patching, encryption, and network restrictions. Each layer catches problems the others might miss.

In this lesson, you learned why root should never power an application, how SQL injection works and how prepared statements neutralize it, why staying patched matters, how encryption and SSL/TLS protect data, and how to restrict who can even reach your database server. Next, you will turn to performance — making sure a secure database is also a fast one.