SQL - Injection

SQL - Injection

SQL Injection is a code injection technique used to attack data-driven applications. It occurs when malicious SQL statements are inserted into an entry field for execution (e.g., to dump database contents to the attacker). It is one of the most common and most dangerous web application vulnerabilities.

How Does SQL Injection Happen?

SQL Injection happens when an application takes user-provided data and embeds it directly into an SQL query string without proper sanitization. This allows an attacker to break out of the data context and inject their own SQL commands.

A Classic Example

Imagine a login form where the application builds the query like this (example in pseudo-code):

username = get_user_input('username')
password = get_user_input('password')

query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "';"

execute_sql(query)

A normal user would enter alice as the username. The query becomes:

SELECT * FROM users WHERE username = 'alice' AND password = 'some_password';

However, an attacker could enter the following into the username field:

' OR '1'='1

The application would then construct this malicious query:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...';

Since '1'='1' is always true, the WHERE clause evaluates to true for every user, and the attacker might be logged in as the first user in the database (often an administrator) without needing a password.

A More Destructive Example

An attacker could input this into a username field:

'; DROP TABLE users; --

The resulting SQL would be:

SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND ...;

If the database allows multiple statements, it would execute the SELECT and then proceed to drop the entire users table. The -- starts a comment, causing the rest of the original query to be ignored.

The Universal Solution: Prepared Statements

The most effective way to prevent SQL injection is to never concatenate user input directly into SQL strings. Instead, use prepared statements (also called parameterized queries).

A prepared statement works in two stages:

1. 1. Prepare: The application sends the SQL query structure to the database with placeholders (?, $1, etc.) for the user-provided values. The database parses and compiles this query structure without the data.
2. 2. Execute: The application then sends the user-provided data separately. The database engine inserts this data into the pre-compiled query, treating it strictly as data, not as executable code.

Prepared Statement Example (Python with psycopg2)

import psycopg2

# User input
user_name_input = "' OR '1'='1'" # Malicious input

# Database connection
conn = psycopg2.connect(...)
cursor = conn.cursor()

# 1. Prepare the statement with a placeholder (%s for psycopg2)
sql = "SELECT * FROM users WHERE username = %s;"

# 2. Execute, passing the user data as a separate parameter
# The database driver handles the sanitization.
cursor.execute(sql, (user_name_input,))

# The database sees the malicious string as just a literal string to search for,
# not as code to execute. The query fails to find a user, as it should.

results = cursor.fetchall()

Key Takeaways

• ALWAYS use prepared statements with parameterized queries.
• NEVER trust user input. Treat all external data as potentially hostile.
• DO NOT try to write your own sanitization or escaping function. It is extremely difficult to get right. Rely on the battle-tested functions provided by your database driver.