SQL Injection Basics: Understanding and Preventing Database Attacks

When a Text Box Drains the Database

A text box on a login page. The attacker types: ' OR 1=1 --. The database returns every user row. This is SQL Injection (SQLi) — the most damaging web application vulnerability of the past two decades. When user input is included directly in SQL queries without sanitization, attackers can read, modify, or delete your entire database.

Impact of SQL Injection

The consequences of a successful SQL injection attack can be devastating:

Types of SQL Injection

In-band SQL Injection

The attacker uses the same channel to inject code and retrieve results. This is the most common and straightforward type.

Error-based SQLi: Relies on error messages from the database to gather information about its structure. For example, intentionally causing type errors reveals column names and data types.

Union-based SQLi: Uses the UNION SQL operator to combine results from multiple queries, extracting data from other tables. The attacker must match the number of columns in the original query.

Blind SQL Injection

The attacker cannot see direct query results but can infer information based on the application's behavior.

Boolean-based Blind SQLi: Sends queries that return true or false and observes differences in application responses. For example, checking if a character in a password is greater than a specific value.

Time-based Blind SQLi: Uses database time delay functions like SLEEP() or WAITFOR to infer information based on response times. A delayed response confirms the condition was true.

Out-of-band SQL Injection

The attacker uses a different channel to receive data, such as DNS or HTTP requests. This is used when the application does not display query results or error messages.

How SQL Injection Works

Vulnerable Code Example

Consider a login form that builds a query by directly concatenating user input:

$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";

When an attacker enters admin' -- as the username, the query becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = ''

The -- comments out the password check, allowing authentication as admin without knowing the password.

Data Extraction with UNION

' UNION SELECT username, password FROM users --

This adds the contents of the users table to the query results, potentially exposing all credentials.

Blind Injection Example

Time-based detection in MySQL:

' OR IF(1=1, SLEEP(5), 0) --

If the response takes 5 seconds, SQL injection is confirmed.

Detection and Testing

Manual Testing Indicators

Look for inputs such as search fields, login forms, URL parameters, and API endpoints. Test with simple payloads:

Automated Tools

SQLMap is the most popular automated SQL injection tool:

# Basic scan
sqlmap -u "http://example.com/page?id=1" --batch

# Enumerate databases
sqlmap -u "http://example.com/page?id=1" --dbs

# Extract tables
sqlmap -u "http://example.com/page?id=1" -D database --tables

# Dump data
sqlmap -u "http://example.com/page?id=1" -D database -T users --dump

{@visual sqlmap-exploit-output}

Prevention Techniques

Parameterized Queries (Prepared Statements)

The most effective defense. Placeholders separate SQL code from user data:

cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))

Stored Procedures

Use pre-compiled stored procedures instead of dynamic SQL:

CREATE PROCEDURE GetUser @Username nvarchar(50)
AS
SELECT * FROM users WHERE username = @Username

Input Validation and Output Encoding

Least Privilege

Web Application Firewall (WAF)

WAFs can detect and block SQL injection attempts based on known patterns.

Database-Specific Considerations

MySQL: Comments with -- and #, information schema in information_schema, version with @@version

PostgreSQL: Comments with --, supports stacked queries with ;

MSSQL: Comments with --, system tables in sys.tables, dangerous extended procedures like xp_cmdshell

Oracle: Comments with --, uses dual table, string concatenation with ||

SQL injection remains a critical threat despite being well-understood. Using parameterized queries, validating input, and conducting regular security testing can eliminate this vulnerability from your applications.

References

{@ref owasp-top10}

{@ref owasp-testing-guide}