What is SQL Injection?

SQL Injection is a web security vulnerability that allows attackers to interfere with the queries that an application makes to its database. It occurs when user input is not properly validated or sanitized before being included in SQL queries, allowing attackers to execute malicious SQL code.

How SQL Injection Works

1. Application takes user input
2. Input is directly concatenated into SQL queries
3. Malicious input alters the query structure
4. Database executes the manipulated query

Basic Example

Vulnerable PHP Code

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// UNSAFE: Direct concatenation of user input
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($connection, $query);
?>

Attack Scenario

If an attacker enters:

• Username: admin'--
• Password: [anything]

The resulting query becomes:

SELECT * FROM users WHERE username = 'admin'-- ' AND password = 'anything'

The -- sequence comments out the rest of the query, bypassing password authentication!

Types of SQL Injection Attacks

1. Authentication Bypass

-- Input: ' OR '1'='1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

-- This returns all users, often allowing login as the first user

2. Data Extraction

-- Input: ' UNION SELECT username, password FROM users --
SELECT * FROM products WHERE name = '' UNION SELECT username, password FROM users -- '

-- This extracts all usernames and passwords from the users table

3. Database Modification

-- Input: '; DROP TABLE users; --
SELECT * FROM products WHERE name = ''; DROP TABLE users; -- '

-- This deletes the entire users table!

4. Blind SQL Injection

-- Input: ' AND SLEEP(5) AND '1'='1
SELECT * FROM articles WHERE id = '1' AND SLEEP(5) AND '1'='1'

-- If the page takes 5 seconds to load, the injection was successful

Real-World Examples

Example 1: Login Bypass

// Vulnerable login code
$user = $_POST['username'];
$pass = $_POST['password'];
$sql = "SELECT * FROM admins WHERE username='$user' AND password='$pass'";

// Attack: username = 'admin'-- & password = anything
// Becomes: SELECT * FROM admins WHERE username='admin'-- AND password='anything'
// Result: Logs in as admin without knowing the password

Example 2: Data Extraction

// Vulnerable product search
$product = $_GET['product'];
$sql = "SELECT * FROM products WHERE name LIKE '%$product%'";

// Attack: product = ' UNION SELECT username, password FROM users --
// Becomes: SELECT * FROM products WHERE name LIKE '%' UNION SELECT username, password FROM users -- %'
// Result: Shows all usernames and passwords instead of products

Example 3: Database Schema Discovery

-- Find number of columns
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3-- 
-- Continue until error occurs to determine column count

-- Then extract information
' UNION SELECT version(), database(), user() --

Preventing SQL Injection in MySQL

1. Use Prepared Statements (Parameterized Queries)

// SAFE: Using prepared statements with MySQLi
$stmt = $connection->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();

// SAFE: Using PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);
$result = $stmt->fetchAll();

2. Use Stored Procedures

-- Create stored procedure
DELIMITER //
CREATE PROCEDURE SafeLogin(IN user VARCHAR(255), IN pass VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE username = user AND password = pass;
END //
DELIMITER ;

-- Call from PHP
$stmt = $pdo->prepare("CALL SafeLogin(:username, :password)");
$stmt->execute(['username' => $username, 'password' => $password]);

3. Input Validation and Sanitization

// Validate input format
if (!preg_match("/^[a-zA-Z0-9_]+$/", $username)) {
    die("Invalid username format");
}

// Use built-in escape functions (less secure than prepared statements)
$safe_username = mysqli_real_escape_string($connection, $username);
$safe_password = mysqli_real_escape_string($connection, $password);

4. Least Privilege Principle

-- Create a database user with minimal privileges
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON database.users TO 'webapp'@'localhost';
-- Don't grant DELETE, DROP, or other dangerous privileges

5. Additional Security Measures

• Use Web Application Firewalls (WAF)
• Regularly update and patch database software
• Implement proper error handling (don't show database errors to users)
• Conduct regular security audits and penetration testing

Testing for SQL Injection Vulnerabilities

Manual Testing

-- Test inputs:
'
''
`
")
')
')
'))
' OR '1'='1
' OR '1'='1'-- 
' OR '1'='1'# 
' OR '1'='1'/* 
' UNION SELECT NULL-- 
' UNION SELECT NULL,NULL-- 

Automated Tools

• SQLMap
• Burp Suite
• OWASP ZAP

Conclusion

SQL Injection remains one of the most dangerous web application vulnerabilities. By always using prepared statements, validating input, and following the principle of least privilege, you can effectively protect your MySQL database from these attacks.

Remember: Never trust user input! Always validate, sanitize, and use parameterized queries when working with databases.