Is Your Site Vulnerable? Understanding the Common SQL Injection Attack

Background

SQL injection is a prevalent web‑application attack where an attacker injects malicious SQL statements into input parameters, causing the backend database to execute unintended queries.

Typical three‑tier web architecture (MVC) consists of:

Presentation layer – the front‑end UI

Business logic layer – processes input parameters

Data access layer – interacts with databases such as MySQL, Oracle, SQL Server

SQL Injection Vulnerability Details

When user‑supplied data is concatenated into SQL without proper validation, the resulting query can be altered to retrieve or modify data.

2.1 Example 1 – Database Enumeration

Using the DVWA penetration‑testing platform, the attacker first observes the normal query:

SELECT first_name, last_name FROM users WHERE user_id = '1';

By submitting 1' order by 1# the query becomes:

SELECT first_name, last_name FROM users WHERE user_id = '1' order by 1#

The # starts a comment, so the database returns the first column ordering, confirming the number of columns. Repeating with order by 2# succeeds, while order by 3# fails, indicating the table has two columns.

Next, a UNION‑based payload extracts database name and current user:

SELECT first_name, last_name FROM users WHERE user_id = '1' UNION SELECT database(),user()#

The response reveals the database name dvwa and the MySQL user root@localhost.

Further UNION queries retrieve table names and finally usernames and passwords:

SELECT first_name, last_name FROM users WHERE user_id = '1' UNION SELECT user,password FROM users#

The returned rows contain usernames and MD5‑hashed passwords, which can be cracked using online tools such as www.cmd5.com.

2.2 Example 2 – Authentication Bypass

On a simple login page the original query is:

select * from users where username='123' and password='123';

Injecting 123' or 1=1 # for both username and password changes the query to:

select * from users where username='123' or 1=1 # and password='123' or 1=1 #

Everything after # is ignored, leaving a condition that always evaluates to true, so login succeeds.

Even without the comment character, using 123' or '1'='1 yields a query where two or clauses make the whole predicate true, again bypassing authentication.

2.3 Example 3 – Identifying Injection Points

Typical vulnerable URLs look like http://example.com/abcd.php?id=XX. To test, append and '1'='1 and observe whether the page still works. If it does, the parameter is likely injectable.

Tools such as Postman can automate these requests.

How to Prevent SQL Injection

Key defenses include:

Validate and sanitize all user inputs, preferably using a whitelist.

Use regular expressions to reject suspicious patterns, e.g. ^(.+)\sand\s(.+)|(.+)\sor\s(.+)$.

Apply global string replacement to strip dangerous characters.

Prefer prepared statements or parameterised queries (e.g., MyBatis #{}) over string concatenation ( ${}).

When using MyBatis, the #{} syntax automatically escapes single quotes, turning a' or '1=1 into a\' or \'1=1\', which prevents injection.

References

Jewel591 – Fundamentals of SQL Injection (Jianshu)

Wu Neng – Differences between MyBatis ${} and #{} (Juejin Community)