week 8

8.5 SQL Injection — Attack and Prevention

SQL injection is one of the most common and dangerous web application vulnerabilities. An attacker inputs malicious SQL code into a form field that gets incorporated into a database query.

 

ATTACK EXAMPLE: A login form sends the query:

SELECT * FROM USERS WHERE Username='' + input_username + '' AND Password='' + input_password + ''

If the attacker enters: Username = ' OR '1'='1 and Password = ' OR '1'='1

The query becomes: SELECT * FROM USERS WHERE Username='' OR '1'='1' AND Password='' OR '1'='1'

'1'='1' is always TRUE, so the attacker gains access without a valid password!

 

PREVENTION METHODS:

1.    Parameterised Queries (Prepared Statements) — NEVER concatenate user input directly into SQL

2.    Input Validation — Validate and sanitise all user inputs

3.    Stored Procedures — Use pre-compiled SQL

4.    Principle of Least Privilege — DB user for the app should have minimal rights

5.    Web Application Firewalls (WAF) — Detect and block injection attempts

 

SAFE QUERY (Java PreparedStatement):

PreparedStatement stmt = conn.prepareStatement('SELECT * FROM USERS WHERE Username=? AND Password=?');

stmt.setString(1, inputUsername);

stmt.setString(2, inputPassword);