How SQL injection exploits queries - Mechanics & Internals

Overview - How SQL injection exploits queries

What is it?

SQL injection is a way attackers trick a database by inserting harmful code into a query. It happens when user input is not checked properly and gets mixed with the database commands. This lets attackers see, change, or delete data they shouldn't access. It is a serious security problem for websites and apps that use databases.

Why it matters

Without protection against SQL injection, attackers can steal private information, damage data, or take control of systems. This can lead to loss of trust, money, and even legal trouble. Understanding how SQL injection works helps developers build safer applications and protect users' data.

Where it fits

Before learning about SQL injection, you should know basic SQL queries and how user input is handled in applications. After this, you can learn about secure coding practices like prepared statements and input validation to prevent attacks.

Mental Model

Core Idea

SQL injection happens when untrusted input is treated as code inside a database query, letting attackers change the query's meaning.

Think of it like...

Imagine writing a letter where someone secretly adds extra instructions that change what the letter says, causing unexpected actions.

User Input + SQL Query Template → Combined Query → Database Execution

┌─────────────┐   ┌───────────────┐   ┌───────────────┐   ┌───────────────┐
│ User Input  │ + │ Query String  │ = │ Final Query   │ → │ Database Run  │
└─────────────┘   └───────────────┘   └───────────────┘   └───────────────┘

Build-Up - 7 Steps

1

Foundation

Basics of SQL Queries

Concept: Learn what SQL queries are and how they ask databases for data.

SQL queries are instructions written in a special language to get or change data in a database. For example, SELECT * FROM users WHERE name = 'Alice'; asks for all data about users named Alice.

Result

You understand how a simple SQL query looks and what it does.

Knowing how queries work is essential to see how attackers can change them.

2

Foundation

User Input in Queries

Concept: How user input is added to SQL queries in applications.

Applications often take what users type and put it inside SQL queries to find or save data. For example, a login form might create a query like SELECT * FROM users WHERE username = 'input'; where 'input' is what the user typed.

Result

You see how user input becomes part of the query string.

Understanding this step shows where attackers can insert harmful code.

3

Intermediate

What is SQL Injection?

🤔Before reading on: do you think user input can change the structure of a SQL query or only the data it searches for? Commit to your answer.

Concept: SQL injection is when attackers add code in user input that changes the query's structure.

If user input is not checked, attackers can add SQL commands inside it. For example, if input is ' OR 1=1 --, the query becomes SELECT * FROM users WHERE username = '' OR 1=1 --'; which always returns true and may reveal all users.

Result

The query runs differently than intended, exposing or changing data.

Knowing that input can change query logic reveals the root of SQL injection.

4

Intermediate

Common Injection Techniques

🤔Before reading on: do you think attackers only use simple tricks or can they use complex commands to harm databases? Commit to your answer.

Concept: Attackers use tricks like always-true conditions, comments, and stacked queries to exploit SQL injection.

Examples include: - ' OR '1'='1' -- makes conditions always true - '; DROP TABLE users; -- ends one query and starts another to delete data - Using UNION to combine results from other tables These tricks let attackers read or destroy data.

Result

Attackers can do many harmful things by crafting special input.

Understanding these techniques helps recognize the risks and how attackers think.

5

Intermediate

Why Input Validation Fails

Concept: Why simple checks on input often do not stop SQL injection.

Some developers try to block bad characters like quotes or semicolons, but attackers find ways around these filters. For example, using different encodings or comments can bypass simple checks.

Result

Input validation alone is not enough to prevent injection.

Knowing the limits of input filtering guides us to better protection methods.

6

Advanced

Prepared Statements Prevent Injection

🤔Before reading on: do you think separating code and data in queries can stop SQL injection? Commit to your answer.

Concept: Prepared statements keep user input separate from SQL code, stopping attackers from changing query structure.

Instead of building a query by joining strings, prepared statements use placeholders. For example: PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?'; EXECUTE stmt USING @input; This way, input is treated only as data, never code.

Result

Queries run safely even if input contains harmful characters.

Understanding prepared statements is key to writing secure database code.

7

Expert

Advanced Injection: Blind and Time-Based

🤔Before reading on: do you think attackers always see data directly from injection? Commit to your answer.

Concept: Some injections do not show data but let attackers guess information by observing responses or delays.

Blind SQL injection uses yes/no questions in queries to learn data bit by bit. Time-based injection makes the database wait (sleep) if a condition is true, revealing info by timing. These methods are stealthy and hard to detect.

Result

Attackers can steal data even without visible errors or output.

Knowing these subtle attacks helps build deeper defenses and monitoring.

Under the Hood

When an application builds a SQL query by combining fixed code and user input as plain text, the database engine receives one full string. The database parses this string as commands. If the input includes SQL syntax like quotes or keywords, it changes the command structure. The database then executes the altered commands, which can do unintended actions.

Why designed this way?

Early database systems treated queries as plain text strings for simplicity and flexibility. This design made it easy to write queries but did not separate code from data. Over time, this led to security risks like SQL injection. Modern systems introduced prepared statements and parameterized queries to fix this, but legacy code still uses the old way.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Application   │       │ Database      │       │ Database      │
│ builds query  │──────▶│ receives full │──────▶│ parses & runs │
│ as text       │       │ query string  │       │ query string  │
└───────────────┘       └───────────────┘       └───────────────┘

If input contains SQL code:

User Input: ' OR 1=1 --
Query becomes:
SELECT * FROM users WHERE username = '' OR 1=1 --';

Database runs query with changed logic.

Myth Busters - 4 Common Misconceptions

Quick: Does escaping quotes in input fully prevent SQL injection? Commit yes or no.

Common Belief: Escaping quotes or special characters in user input completely stops SQL injection.

Reality: Escaping helps but is not foolproof; attackers can bypass it with encoding tricks or other SQL syntax.

Why it matters: Relying only on escaping leaves applications vulnerable to clever attacks.

Quick: Can SQL injection only happen in SELECT queries? Commit yes or no.

Common Belief: SQL injection only affects queries that read data, like SELECT statements.

Reality: Injection can affect any SQL command, including INSERT, UPDATE, DELETE, or even administrative commands.

Why it matters: Ignoring injection risks in non-SELECT queries can cause data loss or system compromise.

Quick: Is using prepared statements always enough to prevent SQL injection? Commit yes or no.

Common Belief: Using prepared statements guarantees complete protection against SQL injection.

Reality: Prepared statements prevent injection in query parameters but not if query structure is built dynamically with unsafe input.

Why it matters: Misusing prepared statements can give a false sense of security.

Quick: Can attackers learn data without error messages or visible output? Commit yes or no.

Common Belief: If an application shows no errors or data, SQL injection is not possible or harmful.

Reality: Blind and time-based SQL injection lets attackers extract data silently by observing behavior or delays.

Why it matters: Ignoring silent attacks can lead to unnoticed data breaches.

Expert Zone

1

Some databases allow multiple queries in one command, increasing injection risk if not disabled.

2

Error messages from databases can leak structure details that help attackers craft injections.

3

Even with prepared statements, dynamic SQL parts like table names or column names must be carefully handled to avoid injection.

When NOT to use

Avoid building SQL queries by concatenating strings with user input. Instead, use prepared statements or ORM frameworks that handle parameters safely. For complex queries, use stored procedures or query builders that separate code and data clearly.

Production Patterns

In real systems, developers use parameterized queries everywhere user input is involved. They also apply input validation, least privilege database accounts, and monitor logs for suspicious query patterns. Web application firewalls (WAFs) add another layer of defense against injection attempts.

Connections

Cross-Site Scripting (XSS)

Both are injection attacks exploiting untrusted input to change program behavior.

Understanding SQL injection helps grasp how input can alter code in other contexts like web pages, improving overall security awareness.

Input Validation

Input validation is a related defense but not sufficient alone against injection.

Knowing the limits of input validation clarifies why separating code and data is crucial.

Compiler Design

SQL injection exploits how databases parse and compile query strings.

Understanding parsing and compilation processes reveals why mixing code and data is risky and how prepared statements avoid this.

Common Pitfalls

#1 Building SQL queries by joining strings with user input directly.

Wrong approach:

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

Correct approach:

Use prepared statements:
stmt = db.prepare("SELECT * FROM users WHERE username = ?");
stmt.execute(user_input);

Root cause:

Misunderstanding that user input can contain SQL code that changes query meaning.

#2 Relying only on escaping quotes to prevent injection.

Wrong approach:

escaped_input = user_input.replace("'", "''");
query = "SELECT * FROM users WHERE username = '" + escaped_input + "';"

Correct approach:

Use parameterized queries or prepared statements instead of manual escaping.

Root cause:

Belief that escaping special characters is enough for security.

#3 Using prepared statements but inserting user input into query parts like table names.

Wrong approach:

query = "SELECT * FROM " + user_input + " WHERE id = ?";
stmt = db.prepare(query);
stmt.execute(id);

Correct approach:

Validate and whitelist table names separately; do not insert raw input into SQL code parts.

Root cause:

Confusing parameter placeholders with query structure elements.

Key Takeaways

SQL injection happens when user input is treated as code inside database queries, allowing attackers to change query behavior.

Building queries by joining strings with user input is unsafe; prepared statements separate code from data to prevent injection.

Simple input filtering or escaping is not enough to stop all injection attacks.

Advanced injection techniques like blind and time-based attacks can steal data without visible errors.

Understanding how databases parse queries helps developers write secure code and protect applications from injection.