MySQL SQL Injection

Overview

SQL injection is a code injection technique where malicious SQL statements are inserted into application queries. Understanding SQL injection and how to prevent it is crucial for database security.

SQL Injection Types

• Classic SQLi: Basic query manipulation
• Blind SQLi: No visible query results
• Time-Based: Based on response time
• Union-Based: Combining query results
• Error-Based: Extracting data from errors

SQL Injection Examples

Basic Injection

-- Vulnerable query (application code)
"SELECT * FROM users WHERE username = '" + userInput + "'";

-- Malicious input
"admin' OR '1'='1"

-- Resulting query
SELECT * FROM users WHERE username = 'admin' OR '1'='1';
-- Returns all users (bypasses authentication)

Login Bypass

-- Vulnerable login query
"SELECT * FROM users WHERE username = '" + user + "' AND password = '" + pass + "'";

-- Malicious input (any password works)
username: admin' --
password: anything

-- Resulting query (comment bypasses password check)
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

Union-Based Injection

-- Vulnerable query
"SELECT * FROM products WHERE id = " + userInput + "";

-- Malicious input
"1 UNION SELECT username, password FROM users"

-- Resulting query
SELECT * FROM products WHERE id = 1 
UNION SELECT username, password FROM users
-- Exposes user credentials

Error-Based Injection

-- Vulnerable query
"SELECT * FROM products WHERE id = " + userInput + "";

-- Malicious input
"1 AND (SELECT 1 FROM (SELECT COUNT(*), CONCAT(username, ':', password) FROM users LIMIT 1) AS x)"

-- Extracts username:password from error message

Identifying SQL Injection

Testing for Vulnerabilities

-- Test with single quote
Input: test'
Expected Error: Syntax error indicates vulnerability

-- Test with logical operators
Input: test' OR '1'='1
Expected: Returns all rows if vulnerable

-- Test with comments
Input: test'--
Expected: Comment bypasses remaining query

-- Test with mathematical operators
Input: test' AND '1'='2
Expected: Returns no rows if not vulnerable

Automated Testing

-- Common injection payloads
' OR '1'='1
' OR '1'='1'--
' UNION SELECT NULL--
' AND 1=1--
'; DROP TABLE users;--

-- Boolean-based testing
' AND 1=1
' AND 1=2

Preventing SQL Injection

Prepared Statements

MySQL Prepared Statements

-- Vulnerable query
SET @query = CONCAT('SELECT * FROM users WHERE id = ', @user_id);
PREPARE stmt FROM @query;
EXECUTE stmt USING @user_id;
-- Still vulnerable if @user_id contains malicious input

-- Secure query (parameterized)
SET @sql = 'SELECT * FROM users WHERE id = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @user_id;
-- User input treated as data, not code

PHP / PHP

<?php
// Vulnerable
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];
$result = $conn->query($sql);

// Secure with prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $_GET['id']);
$stmt->execute();
$result = $stmt->get_result();
?>

Python / Python

# Vulnerable
sql = "SELECT * FROM users WHERE id = " + user_id
cursor.execute(sql)

# Secure with prepared statement
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (user_id,))

Java / Java

// Vulnerable
String sql = "SELECT * FROM users WHERE id = " + userId;
Statement stmt = conn.createStatement(sql);

// Secure with prepared statement
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
ResultSet rs = pstmt.executeQuery();

Node.js / Node.js

// Vulnerable
const sql = `SELECT * FROM users WHERE id = ${userId}`;
connection.query(sql);

// Secure with prepared statement
const sql = 'SELECT * FROM users WHERE id = ?';
connection.query(sql, [userId]);

Input Validation

<?php
// Validate input type
if (!filter_var($_GET['id'], FILTER_VALIDATE_INT)) {
    die('Invalid ID');
}

// Validate length
if (strlen($_GET['username']) < 3 || strlen($_GET['username']) > 50) {
    die('Invalid username length');
}

// Whitelist allowed values
$allowed_status = ['active', 'inactive', 'pending'];
if (!in_array($_GET['status'], $allowed_status)) {
    die('Invalid status');
}
?>

Escaping

<?php
// Vulnerable
$sql = "SELECT * FROM users WHERE name = '" . $_GET['name'];

// Escaped (less secure, use prepared statements instead)
$name = $conn->real_escape_string($_GET['name']);
$sql = "SELECT * FROM users WHERE name = '" . $name;

// Better: Use mysqli_real_escape_string
$name = mysqli_real_escape_string($conn, $_GET['name']);
?>

Least Privilege

-- Create limited user for application
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant only necessary privileges
GRANT SELECT, INSERT ON app_db.users TO 'app_user'@'localhost';

-- Avoid GRANT ALL PRIVILEGES
-- Application can't drop tables, modify schema, etc.

Advanced SQL Injection

Blind SQL Injection

-- No visible results
"SELECT * FROM users WHERE id = " + input;

-- Boolean-based blind
Input: 1' AND SUBSTRING(VERSION(),1,1)='5'
If page loads normally: 5 is first character
If error or different: 5 is not first character

-- Time-based blind
Input: 1' AND SLEEP(5)--
If response takes 5+ seconds: Vulnerable

Second-Order Injection

-- Stored input used in another query
-- Step 1: Insert malicious name
INSERT INTO users (name) VALUES ('admin'--

-- Step 2: Name used in vulnerable query
SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE name = 'admin'--')

-- Executes: SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE name = 'admin'--)

SQL Injection Prevention Tools

Web Application Firewalls

• ModSecurity: Apache module
• SQLMap: Automated testing
• OWASP ZAP: Security scanner

Code Analysis

-- Static analysis tools
-- PHP: RIPS, Pixy
-- Python: Bandit
-- Java: FindBugs, SpotBugs

-- Review common patterns
-- String concatenation with user input
-- Dynamic SQL construction
-- Lack of input validation
-- Missing prepared statements

Best Practices

Secure Coding Practices

-- 1. Always use prepared statements
-- 2. Never concatenate user input
-- 3. Validate input type and format
-- 4. Use whitelist when possible
-- 5. Escape output (XSS prevention)
-- 6. Limit error messages
-- 7. Use least privilege accounts
-- 8. Regular security audits

Database Security

-- Disable dangerous functions (if not needed)
-- SET GLOBAL sql_mode = 'TRADITIONAL';

-- Restrict file access
SET GLOBAL secure_file_priv = OFF;

-- Disable symbolic links
SET GLOBAL have_symlink = OFF;

-- Monitor query logs
SET GLOBAL general_log = ON;

Testing and Auditing

Regular Security Testing

-- Test with automated tools
-- SQLMap: sqlmap -u "http://example.com/page?id=1"

-- Manual testing checklist
-- - Single quote test
-- - Boolean logic test
-- - Time-based test
-- - Error-based test
-- - Union-based test

Logging and Monitoring

-- Enable query logging
SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'TABLE';

-- Monitor suspicious patterns
SELECT * FROM mysql.general_log
WHERE argument LIKE '%UNION%'
   OR argument LIKE '%DROP TABLE%'
   OR argument LIKE '%--%';

Summary

SQL injection prevention involves:

• Prepared Statements: Parameterized queries
• Input Validation: Type and format checking
• Least Privilege: Limited database accounts
• Web Security: WAF and monitoring
• Regular Testing: Automated and manual checks
• Security Audits: Code and infrastructure reviews

Always use prepared statements and validate all user inputs to prevent SQL injection attacks.

---

Previous: Handle Duplicates

Next: Export Data