Database Security Best Practices — Encryption, SQL Injection Prevention, Access Control

In this tutorial, you'll learn about Database Security Best Practices. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database security is the practice of protecting database systems from unauthorized access, data breaches, and malicious attacks through encryption, access controls, input validation, and continuous monitoring.

What You'll Learn

You'll understand encryption at rest and in transit, parameterized queries to prevent SQL Injection, role-based access control (RBAC), audit logging, secrets management, and Compliance considerations for production database deployments.

Why It Matters

A single unsecured database can expose millions of user records, leading to regulatory fines, reputation damage, and legal liability. Durga Antivirus Pro scans millions of files daily and stores threat signatures in a database; if that database were compromised, the security of every user would be at risk.

Real-World Use

An e-commerce platform stored credit card numbers in plaintext. An attacker exploited a SQL Injection vulnerability in the login form, extracted the entire customer table, and sold the data on the dark web. Proper database security would have prevented this entirely.

Database Security Learning Path

flowchart LR
  A[SQL Basics] --> B[Database Design]
  B --> C[Database Security]
  C --> D[Advanced Security]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Encryption at Rest

Encryption at rest protects data stored on disk. If someone steals the physical server or a backup tape, encrypted data is unreadable without the decryption key.

Transparent Data Encryption (TDE)

TDE encrypts the database files automatically. The database engine handles encryption and decryption transparently.

-- MySQL: Enable TDE with a master key
SET GLOBAL innodb_encrypt_tables = ON;
SET GLOBAL innodb_encrypt_log = ON;

-- PostgreSQL: TDE requires extensions like pg_crypto or pg_tde
CREATE EXTENSION IF NOT EXISTS pg_tde;
SELECT pg_tde_add_key_provider_file('local-key-provider', '/etc/postgresql/encryption.key');
SELECT pg_tde_set_key('my-db-key', 'local-key-provider');

Expected behavior: After enabling TDE, all table data files, logs, and temporary files are encrypted. No application code changes are needed.

Column-Level Encryption

For highly sensitive fields like SSNs or credit card numbers, encrypt specific columns:

-- PostgreSQL: Column encryption with pgcrypto
CREATE EXTENSION pgcrypto;

-- Encrypt sensitive data on insert
INSERT INTO patients (id, name, ssn_encrypted)
VALUES (1, 'Alice Smith', pgp_sym_encrypt('123-45-6789', 'encryption_key'));

-- Decrypt on read (app must provide the key)
SELECT name, pgp_sym_decrypt(ssn_encrypted, 'encryption_key') AS ssn
FROM patients WHERE id = 1;

Expected output:

    name      |     ssn
--------------+--------------
 Alice Smith  | 123-45-6789

Column-level encryption adds complexity: you cannot search or index encrypted columns efficiently unless you use deterministic encryption (which has weaker security).

Encryption in Transit

Data traveling between the application and the database is vulnerable to interception. Always use TLS encryption for database connections.

-- PostgreSQL: Require SSL connections
-- postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

-- pg_hba.conf: Only allow SSL connections
hostssl all all 0.0.0.0/0 md5

# Python application: Force SSL connection to PostgreSQL
import psycopg2

conn = psycopg2.connect(
    host="db.example.com",
    dbname="production",
    user="app_user",
    password="secret",
    sslmode="require"  # Refuses connection if TLS not available
)

Expected behavior: If an attacker intercepts the network traffic, they see encrypted gibberish instead of SQL queries and result sets.

SQL Injection Prevention

SQL Injection is the most dangerous database vulnerability. An attacker inserts malicious SQL into an input field to manipulate queries.

How SQL Injection Works

# VULNERABLE: String concatenation with user input
user_input = "1; DROP TABLE users; --"
query = f"SELECT * FROM users WHERE id = {user_input}"
# Result: SELECT * FROM users WHERE id = 1; DROP TABLE users; --
# The DROP TABLE executes! Users table is gone.

Parameterized Queries (Prepared Statements)

The only reliable defense is parameterized queries, which separate SQL code from data:

# SAFE: Parameterized query with psycopg2
import psycopg2

conn = psycopg2.connect("dbname=test user=admin")
cur = conn.cursor()

# The %s placeholder is NOT the same as string formatting
# The database driver handles escaping
cur.execute("SELECT * FROM users WHERE id = %s", (user_input,))

# SAFE: Using SQLAlchemy ORM
from sqlalchemy import text

# Always use bind parameters
result = session.execute(
    text("SELECT * FROM users WHERE id = :user_id"),
    {"user_id": user_input}
)

// SAFE: Java JDBC PreparedStatement
String sql = "SELECT * FROM users WHERE email = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, userInput);
ResultSet rs = pstmt.executeQuery();

Expected behavior: Even if user_input contains 1; DROP TABLE users; --, the entire string is treated as a literal value for the id parameter. The DROP TABLE command never executes.

Additional SQL Injection Defenses

Access Control (RBAC)

Role-based access control limits what each database user can do. Never use the database superuser for application connections.

-- PostgreSQL: Create a read-only user for reporting
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Create an application user with limited write access
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
-- Explicitly deny DELETE on orders
REVOKE DELETE ON orders FROM app_user;

-- Application user cannot drop tables or modify schema
-- Even if credentials are leaked, damage is contained

Expected behavior: The app_user can read and insert orders but cannot delete them or alter the database schema. A compromised app user cannot escalate to a full breach.

Audit Logging

Audit logs record who did what and when, which is essential for incident investigation and Compliance (GDPR, HIPAA, SOC 2).

-- PostgreSQL: Enable audit logging via pgaudit extension
CREATE EXTENSION pgaudit;

-- Log all DDL and DML statements
SET pgaudit.log = 'write,ddl';
SET pgaudit.log_level = 'notice';
SET pgaudit.log_relation = on;

-- MySQL: Enable general query log (for audit purposes only)
SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'TABLE';

-- View audit log
SELECT * FROM mysql.general_log WHERE command_type = 'Query' ORDER BY event_time DESC LIMIT 10;

Expected output:

event_time          | user_host          | argument
--------------------+--------------------+----------------------------------------
2026-06-22 10:15:00 | root[root] @ localhost | DROP TABLE sensitive_data
2026-06-22 10:14:55 | app_user[app] @ 10.0.0.5 | SELECT * FROM credit_cards

Secrets Management

Never hardcode database credentials in application code or configuration files that end up in version control.

# BAD: Storing credentials in config files committed to Git
database:
  host: localhost
  password: "SuperSecret123!"  # This will be in Git history forever

# GOOD: Read credentials from environment variables or a secrets manager
import os
import boto3
from botocore.exceptions import ClientError

def get_db_password():
    """Retrieve database password from AWS Secrets Manager."""
    session = boto3.session.Session()
    client = session.client(service_name='secretsmanager')
    try:
        response = client.get_secret_value(SecretId='prod/db/password')
        return response['SecretString']
    except ClientError as e:
        raise SystemExit(f"Failed to retrieve secret: {e}")

db_password = get_db_password()

Expected behavior: Database credentials are never stored in code repositories. They rotate automatically and are accessed only by authorized services.

Common Database Security Errors

1. Using Default or Weak Passwords

Default database passwords (like root:root or postgres:postgres) are the first thing attackers try. Automated scanners find these within minutes.

2. Exposing Databases Directly to the Internet

Database ports (3306 for MySQL, 5432 for PostgreSQL) should never be open to the public internet. Use private networks, VPNs, or SSH tunnels.

3. Disabling TLS Because "It's Too Slow"

Running unencrypted connections to save 1-2ms of latency is indefensible. TLS overhead is negligible with modern hardware and connection pooling.

4. Storing Passwords in Plaintext

Always hash passwords with a strong algorithm like bcrypt or argon2. Never use MD5 or SHA-1 for password storage.

-- NEVER store passwords like this:
INSERT INTO users (email, password) VALUES ('alice@example.com', 'myPassword123');

-- ALWAYS hash passwords before storing (in application code)
-- Python example:
import bcrypt
hashed = bcrypt.hashpw(b'myPassword123', bcrypt.gensalt())

5. Granting Excessive Privileges

Giving every application user full database admin access increases the Blast Radius of any breach. Apply the principle of Least Privilege.

6. Neglecting Backup Encryption

Backup files are often stored in different locations (S3, tape archives) with weaker security. Always encrypt database backups.

7. Not Patching Database Software

Unpatched databases are the entry point for many breaches. Subscribe to your database vendor's security announcements and apply patches promptly.

Practice Questions

1. What is the most effective defense against SQL Injection?

Parameterized queries (prepared statements). They ensure user input is always treated as data, never as executable SQL code. Input validation and WAFs are secondary defenses.

2. What is the difference between encryption at rest and encryption in transit?

Encryption at rest protects data stored on disk (database files, backups, logs). Encryption in transit protects data traveling over the network between application and database (TLS/SSL).

3. Why should you never use a database superuser for application connections?

A superuser has unlimited privileges. If the application connection is compromised, the attacker can drop tables, read all data, or shut down the database. Use role-specific accounts with minimal required permissions.

4. How does Transparent Data Encryption (TDE) differ from column-level encryption?

TDE encrypts all database files automatically without application changes. Column-level encryption encrypts specific columns and requires application code changes for encryption and decryption, but provides finer-grained control.

5. Challenge: Design a security architecture for a healthcare application.

Your healthcare app stores patient records, including SSNs, medical history, and insurance details. It must comply with HIPAA. Design the database security architecture. Answer: Use TDE for encryption at rest. Encrypt SSN and insurance columns with application-layer encryption. Require TLS for all connections. Implement RBAC with roles for doctors (read/write their patients), nurses (read-only), and auditors (access logs only). Enable pgaudit. Use a secrets manager for credentials. Set up connection pooling through a proxy with IP whitelisting. Back up encrypted to S3 with server-side encryption.

FAQ

Try It Yourself

Set up a secure PostgreSQL database:

1. Install PostgreSQL and configure TLS with a self-signed certificate
2. Create a read-only user and an application user with limited permissions
3. Write a parameterized query in Python to insert data
4. Attempt SQL Injection on a non-parameterized query to see it fail
5. Enable pgaudit and run queries to verify audit logging works

What's Next

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.