Database Security with PHP

Database security is one of the most critical components of a web application. To secure the database in PHP, you should apply methods such as SQL injection prevention, password security, access control, and CSRF protection.

You can use the methods below to enhance your database security.

1. Preventing SQL Injection

SQL Injection attacks occur when malicious users manipulate SQL queries to gain unauthorized access to the database. Using prepared statements helps prevent such attacks.

1.1 Insecure Usage (Dangerous!)

$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $user_id";
$result = $db->query($query);

This is insecure because it uses direct user input. An attacker could exploit it like:

?id=1 OR 1=1 --

This would return all user data.

1.2 Secure Usage (Using PDO Prepared Statements)

$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = :id";
$stmt = $db->prepare($query);
$stmt->bindParam(':id', $user_id, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

This approach prevents SQL injection attacks.

2. Input Validation and Filtering

Filtering user input is necessary to prevent malicious input.

2.1 Email Validation

$email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
if (!$email) {
    die('Invalid email address!');
}

2.2 Preventing XSS (Cross-Site Scripting)

$safe_input = htmlspecialchars($_POST['input'], ENT_QUOTES, 'UTF-8');

This prevents malicious HTML/JavaScript input.

3. Password Security and Hashing

Never store passwords as plain text. Use strong algorithms like bcrypt or argon2.

3.1 Password Hashing

$hashed_password = password_hash($password, PASSWORD_DEFAULT);

3.2 Password Verification

if (password_verify($password, $hashed_password)) {
    echo 'Login successful';
} else {
    echo 'Invalid password';
}

4. Authorization and Access Control

Use session controls to restrict access to authorized users.

4.1 Session Check

session_start();
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit;
}

4.2 Role-Based Access Control (RBAC)

if ($_SESSION['role'] !== 'admin') {
    die('Access denied!');
}

5. Secure Database Connection

It's critical to establish a secure connection to the database.

try {
    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'root', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    die('Database connection error!');
}

Important: Use error_log() to log errors instead of displaying them.

6. Restrict Database Permissions

Give the database user minimal privileges to enhance security.

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'appuser'@'localhost';

7. CSRF (Cross-Site Request Forgery) Protection

Prevent CSRF attacks by using CSRF tokens in forms.

7.1 Generating CSRF Token

session_start();
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

7.2 Using Token in Form

<form method='POST'>
    <input type='hidden' name='csrf_token' value='<?php echo $_SESSION['csrf_token']; ?>'>
    <button type='submit'>Submit</button>
</form>

7.3 Validating Token

if ($_POST['csrf_token'] !== $_SESSION['csrf_token']) {
    die('Invalid CSRF token!');
}

8. Database Backup and Monitoring

Regularly back up your database to prevent data loss.

mysqldump -u root -p testdb > backup.sql

Encrypt the backups:

gzip -c backup.sql | openssl enc -aes-256-cbc -e -out backup.sql.gz

9. Use HTTPS

HTTPS is essential when transmitting sensitive data. Install an SSL certificate to encrypt all traffic.

<VirtualHost *:80>
    Redirect permanent / https://yourdomain.com/
</VirtualHost>