SQL injection remains one of the most persistent and dangerous vulnerabilities in web applications. Despite decades of awareness, it continues to compromise data, disrupt services, and expose organisations to significant risk. If you’re building or defending web applications, understanding SQL injection, especially how it works, how attackers exploit it, and how to defend against it, is not optional. This guide explains SQL injection in a simple way, showing how it’s done, giving real-life examples, and suggesting ways to protect yourself.

The Role of SQL in Modern Web Applications

At its core, SQL (Structured Query Language) is how most web applications interact with their databases. Whether you’re using MySQL, PostgreSQL, or SQL Server, your application likely builds queries dynamically, often incorporating user input.

Consider a typical PHP snippet:

$conn = new mysqli(”localhost”, “root”, “password”, “users”);

$searchInput = $_POST[’findUser’];

$query = “SELECT * FROM logins WHERE username LIKE ‘%$searchInput’";

$result = $conn->query($query);

This code takes user input, drops it directly into an SQL query, and executes it. If you don’t sanitise or parametrise that input, you’re inviting attackers to manipulate the query and potentially control your database.

What is SQL Injection?

SQL injection (SQLi) is a vulnerability that allows an attacker to interfere with the queries your application makes to its database. By injecting malicious SQL code into input fields, attackers can:

• Bypass authentication
• Extract sensitive data
• Modify or delete data
• Execute administrative operations

Essentially, if your application processes user input as code instead of data, it’s insecure.

Types of SQL Injection

Understanding the types of SQL injection is crucial for both attackers and defenders:

The Classic Attack

Let’s walk through a real-world authentication bypass using SQL injection.

Scenario: An admin login page executes:

SELECT * FROM logins WHERE username=’admin’ AND password=’p@ssw0rd’;

If you enter admin’ OR ‘1’=’1 as the username, the query becomes:

SELECT * FROM logins WHERE username=’admin’ OR ‘1’=’1’ AND password=’p@ssw0rd’;

Because ‘1’=’1’ is always true, the query returns all users, and you’re logged in as the first user in the database—often the admin.

Key takeaway: The OR operator allows attackers to bypass authentication checks by forcing the WHERE clause to evaluate as true.

Discovering SQL Injection Vulnerabilities

Before exploiting, you need to confirm that an input is vulnerable. Here’s how:

• Inject special characters: Try a single quote (’). If you get an SQL error, the input is likely unsanitised.
• Try known payloads: Use payloads like admin’– or admin’ OR ‘1’=’1 to see if authentication can be bypassed.
• Observe application behaviour: Errors, unexpected outputs, or changes in page content are strong indicators of SQL injection.

Pro tip: Always URL-encode payloads when injecting via GET requests. For example, %27 for ‘.

Using Comments to Subvert SQL Logic

SQL comments are a powerful tool for attackers. In MySQL, you can use — or # to comment out the rest of a query.

Example:

Injecting admin’– as the username transforms the query:

SELECT * FROM logins WHERE username=’admin’-- ‘ AND password=’irrelevant’;

The database ignores everything after –, so only the username check remains. If admin exists, you’re in.

Caveat: In MySQL, — must be followed by a space to be recognised as a comment.

Parentheses, Precedence, and Advanced Bypass

Some applications use parentheses to enforce operator precedence:

SELECT * FROM logins WHERE (username=’admin’ AND id > 1) AND password=’hash’;

If the admin’s id is 1, this query blocks admin logins. But with SQL injection, you can close the parenthesis and comment out the rest:

• Inject admin’)– as the username.
• The query becomes: SELECT * FROM logins WHERE (username=’admin’)
• The rest is ignored, and you log in as admin3.

Union-Based SQL Injection

The UNION operator allows attackers to combine results from different tables.

How it works:

Suppose you have two tables:

• ports (code, city)
• ships (Ship, city)

A query like:

SELECT * FROM ports UNION SELECT * FROM ships;

Combines results from both tables, as long as the number and types of columns match.

Key rules:

• The number of columns in both SELECT statements must match.
• Data types in each column position must be compatible.

If not, you’ll get errors like:

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Handling Column Mismatches

Often, the original query and your injected query have different numbers of columns. You can fill extra columns with junk data or NULL.

Example:

If the original query selects two columns, but you only want one (e.g., username), inject:

UNION SELECT username, 2 FROM passwords

Or, for four columns:

UNION SELECT username, 2, 3, 4 FROM passwords--

Using numbers or NULL helps you match the required column count and track which columns are displayed.

Detecting the Number of Columns

Before crafting your payload, you need to know how many columns the original query returns. Two main methods:

• ORDER BY technique: Inject ORDER BY 1, ORDER BY 2, etc., until you get an error. The highest successful number is your column count.
• UNION SELECT technique: Try UNION SELECT 1, UNION SELECT 1,2, etc., until the query succeeds.

Example:

If ORDER BY 4 works but ORDER BY 5 fails, the table has four columns.

Finding Output Columns

Not all columns are displayed to the user. To find which ones are, inject distinct values and see which appear in the output.

Example:

UNION SELECT 1,@@version,3,4-- -

If you see the database version in the output, you know which column is reflected. Place your payloads accordingly.

Real-World Defences

Now that you understand how SQL injection works, let’s talk defence. The best approaches are:

• Parametrised queries (prepared statements): Never concatenate user input into SQL queries.
• Input validation and sanitisation: Reject or sanitise unexpected input.
• Least privilege: The database user should have only the permissions it needs.
• Error handling: Don’t leak database errors to users.
• Regular security testing: Use automated scanners and manual testing to find vulnerabilities.

Never rely solely on blacklists or escaping input. Attackers are creative, and new bypasses are discovered regularly.

SQL Injection is a Mindset Problem

The problem of SQL injection is less about technology and more about approach. If you treat user input as trustworthy, you’re already behind. The solution is to assume all input is hostile, use parametrised queries by default and stay current with changing attack techniques.

For more insightful and engaging write-ups, visit kosokoking.com and stay ahead in the world of cybersecurity!