Security Best Practices
Introduction
Database security is critical for protecting sensitive data and maintaining system integrity. This guide covers essential security practices for MySQL in WAMP/XAMPP environments.
User Security
1. Never Use Root in Production
-- Bad: Using root in application
$conn = new mysqli("localhost", "root", "", "mydb");
-- Good: Use dedicated application user
$conn = new mysqli("localhost", "app_user", "strong_password", "mydb");
2. Create Application-Specific Users
-- Create user with limited privileges
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd123';
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp'@'localhost';
FLUSH PRIVILEGES;
3. Use Strong Passwords
Good passwords:
- At least 12 characters
- Mix of uppercase, lowercase, numbers, symbols
- Not dictionary words
- Unique per system
-- Generate strong password
-- Use password managers or generators
CREATE USER 'user'@'localhost' IDENTIFIED BY 'xK9#mP2$vL8@qR4!';
4. Limit Host Access
-- Restrictive (localhost only)
CREATE USER 'app'@'localhost' IDENTIFIED BY 'password';
-- Less secure (any host)
CREATE USER 'app'@'%' IDENTIFIED BY 'password';
-- Specific IP/network
CREATE USER 'app'@'192.168.1.100' IDENTIFIED BY 'password';
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'password';
5. Apply Principle of Least Privilege
-- Too permissive
GRANT ALL PRIVILEGES ON *.* TO 'app'@'localhost';
-- Appropriate
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app'@'localhost';
-- Even more restrictive
GRANT SELECT ON myapp.users TO 'readonly'@'localhost';
Password Hashing
Never Store Plain Passwords
-- NEVER DO THIS
INSERT INTO users (username, password)
VALUES ('john', 'mypassword123');
-- Always hash passwords
-- PHP example with bcrypt
$hash = password_hash('mypassword123', PASSWORD_BCRYPT);
INSERT INTO users (username, password_hash)
VALUES ('john', '$2y$10$...');
Use Strong Hashing Algorithms
Recommended:
- bcrypt (PASSWORD_BCRYPT)
- Argon2 (PASSWORD_ARGON2ID)
- scrypt
Avoid:
- MD5 (broken)
- SHA1 (broken)
- Plain SHA256 without salt
Example: Secure Password Storage
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL, -- bcrypt produces 60 chars
password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SQL Injection Prevention
Use Prepared Statements
// Bad: SQL injection vulnerable
$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $query);
// Good: Prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
Never Trust User Input
-- Always validate and sanitize input
-- Use prepared statements
-- Escape special characters if necessary
Examples of SQL Injection
-- If input is not sanitized:
-- Input: ' OR '1'='1
-- Result: SELECT * FROM users WHERE username = '' OR '1'='1'
-- Returns all users!
-- Input: '; DROP TABLE users; --
-- Result: Deletes users table!
Connection Security
1. Use SSL/TLS Connections
-- Require SSL for user
CREATE USER 'secure_user'@'%'
IDENTIFIED BY 'password'
REQUIRE SSL;
-- Check SSL status
SHOW STATUS LIKE 'Ssl_cipher';
2. Configure MySQL for SSL
Edit my.ini/my.cnf:
[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem
3. Secure Connection Strings
// Store credentials securely
// Use environment variables
$db_host = getenv('DB_HOST');
$db_user = getenv('DB_USER');
$db_pass = getenv('DB_PASS');
// Not in code
$db_user = "root";
$db_pass = "password123";
Data Protection
1. Encrypt Sensitive Data
-- Encrypt sensitive columns
INSERT INTO credit_cards (user_id, card_number_encrypted)
VALUES (1, AES_ENCRYPT('1234-5678-9012-3456', 'encryption_key'));
-- Decrypt when needed
SELECT AES_DECRYPT(card_number_encrypted, 'encryption_key') AS card_number
FROM credit_cards
WHERE user_id = 1;
2. Mask Sensitive Data in Logs
-- Don't log sensitive data
-- Log only necessary information
INSERT INTO audit_log (user_id, action, details)
VALUES (1, 'password_change', 'User changed password');
-- NOT: VALUES (1, 'password_change', 'New password: password123');
3. Use Views for Data Access Control
-- Create view without sensitive data
CREATE VIEW public_users AS
SELECT user_id, username, email, created_at
FROM users
WHERE is_active = TRUE;
-- Excludes password_hash, social_security, etc.
-- Grant access to view only
GRANT SELECT ON mydb.public_users TO 'limited_user'@'localhost';
Configuration Security
1. Secure MySQL Configuration
my.ini/my.cnf hardening:
[mysqld]
# Disable remote root login
skip-grant-tables = 0
# Bind to localhost only (if not using remote connections)
bind-address = 127.0.0.1
# Disable LOAD DATA LOCAL INFILE
local-infile = 0
# Set maximum connections
max_connections = 100
# Enable query logging for auditing
general_log = 1
log_error = /path/to/error.log
# Slow query log
slow_query_log = 1
long_query_time = 2
2. File Permissions
Windows:
- Restrict access to MySQL data directory
- Set appropriate NTFS permissions
Linux:
# MySQL data directory
sudo chmod 700 /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
# Configuration file
sudo chmod 644 /etc/my.cnf
sudo chown root:root /etc/my.cnf
3. Disable Unnecessary Features
-- Remove test database
DROP DATABASE IF EXISTS test;
-- Remove anonymous users
DELETE FROM mysql.user WHERE User = '';
-- Remove remote root
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;
phpMyAdmin Security
1. Restrict Access
Using .htaccess (Apache):
# Place in phpMyAdmin directory
AuthType Basic
AuthName "Restricted Access"
AuthUserFile /path/to/.htpasswd
Require valid-user
# Or restrict by IP
Order Deny,Allow
Deny from all
Allow from 192.168.1.0/24
2. Change Default URL
Rename phpMyAdmin directory:
# Instead of /phpmyadmin
# Use /myadmin or /dbadmin or random string
3. Enable Authentication
config.inc.php:
// Require authentication
$cfg['Servers'][$i]['auth_type'] = 'cookie';
// Set secure cookie
$cfg['Servers'][$i]['cookie_samesite'] = 'Strict';
// Blowfish secret for encryption
$cfg['blowfish_secret'] = 'random_32_character_string_here';
4. Disable Root Login
// In config.inc.php
$cfg['Servers'][$i]['AllowRoot'] = FALSE;
Backup Security
1. Encrypt Backups
# Backup and encrypt
mysqldump -u root -p mydatabase | \
gpg --encrypt --recipient your@email.com > backup.sql.gpg
# Decrypt and restore
gpg --decrypt backup.sql.gpg | mysql -u root -p mydatabase
2. Secure Backup Storage
- Store backups in secure locations
- Use encrypted filesystems
- Restrict access permissions
- Keep offsite backups
3. Test Restores Regularly
# Periodically test backup restoration
# Verify data integrity
# Document restore procedures
Monitoring and Auditing
1. Enable Logging
-- General query log
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';
-- View logs
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 100;
2. Monitor Failed Logins
-- Create table to track failed logins
CREATE TABLE login_attempts (
attempt_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
ip_address VARCHAR(45),
attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
success BOOLEAN
);
-- Lock accounts after failures
UPDATE users
SET is_locked = TRUE
WHERE username = 'suspect_user'
AND (SELECT COUNT(*) FROM login_attempts
WHERE username = 'suspect_user'
AND success = FALSE
AND attempted_at > DATE_SUB(NOW(), INTERVAL 15 MINUTE)) >= 5;
3. Regular Security Audits
-- Review users and privileges
SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';
-- Check for users without passwords (bad!)
SELECT User, Host FROM mysql.user WHERE authentication_string = '';
-- Review global variables
SHOW VARIABLES LIKE 'validate_password%';
Security Checklist
Installation
- Change root password immediately
- Remove test database
- Remove anonymous users
- Disable remote root login
User Management
- Create application-specific users
- Use strong passwords
- Apply least privilege principle
- Restrict hosts appropriately
- Regular password rotation
Application
- Use prepared statements
- Validate all input
- Hash passwords with bcrypt/Argon2
- Store credentials securely
- Use SSL/TLS connections
Configuration
- Secure my.ini/my.cnf
- Set appropriate file permissions
- Disable unnecessary features
- Configure firewalls
- Enable logging
phpMyAdmin
- Restrict access by IP
- Change default URL
- Enable authentication
- Keep updated
Monitoring
- Enable audit logging
- Monitor failed logins
- Regular security audits
- Review user privileges
- Check for vulnerabilities
Backup
- Regular automated backups
- Encrypt backup files
- Secure storage
- Test restores
- Offsite copies
Common Security Mistakes
1. Default Passwords
-- Don't leave root password empty
-- Don't use "password" or "admin"
2. Overprivileged Users
-- Don't grant ALL on *.*
-- Grant only necessary permissions
3. SQL Injection Vulnerabilities
// Always use prepared statements
// Never concatenate user input into queries
4. Exposed Error Messages
// Don't display database errors to users
// Log errors, show generic messages
try {
// database operations
} catch (Exception $e) {
error_log($e->getMessage());
echo "An error occurred. Please try again.";
}
5. Unencrypted Connections
-- Use SSL for remote connections
-- Encrypt sensitive data at rest
Incident Response
If Compromised
- Immediately:
- Disconnect from network
- Change all passwords
- Review access logs
- Identify entry point
- Assessment:
- Check for backdoors
- Review user accounts
- Examine file modifications
- Check for data exfiltration
- Recovery:
- Restore from clean backup
- Apply security patches
- Strengthen security measures
- Document incident
- Prevention:
- Analyze root cause
- Implement additional controls
- Train team members
- Update procedures