SQL Injection: Making Databases Spill Their Guts

SQL Injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself can access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior.

The Core Problem: Mixing code (SQL queries) and data (user input) without proper separation.

How SQLi Works - A Simple Analogy:

Imagine you ask a librarian (the application) to find a book by a specific author (user input). You say, "Find books by 'Smith'." The librarian goes to the card catalog (database) and looks up: SELECT * FROM books WHERE author = 'Smith';

Now, what if you, the sneaky attacker, say: "Find books by 'Smith' OR '1'='1'." If the application naively constructs the query, it becomes: SELECT * FROM books WHERE author = 'Smith' OR '1'='1'; Since '1'='1' is always true, the WHERE clause is always true, and the query returns all books! You just tricked the librarian into giving you the whole catalog.

Types of SQL Injection:

1. In-band SQLi (Classic SQLi):

   • Error-based SQLi: Attacker performs actions that cause the database to produce error messages. These errors can sometimes reveal information about the database structure.
     • Example: Inputting a single quote ' might break a query and leak info.
   • UNION-based SQLi: Uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result set. This allows an attacker to extract data from other tables.
     • Example: SELECT name, description FROM products WHERE id = 1 UNION SELECT username, password FROM users; (This requires knowing column counts and types).

2. Inferential SQLi (Blind SQLi):

   • The application doesn't return data from the database directly in its HTTP responses. The attacker infers information by observing the application's behavior and the database's responses to different queries.
   • Boolean-based Blind SQLi: Attacker sends SQL queries that result in a true/false condition, and observes if the application's response changes (e.g., page loads differently).
     • Example: ... AND SUBSTRING((SELECT password FROM users WHERE username = 'admin'), 1, 1) = 'a'
   • Time-based Blind SQLi: Attacker sends SQL queries that cause a time delay if a condition is true. Useful when Boolean-based doesn't give a clear difference in response.
     • Example: ... AND IF(SUBSTRING((SELECT password FROM users WHERE username = 'admin'), 1, 1) = 'a', SLEEP(5), 0)

3. Out-of-band SQLi:

   • Not very common. Relies on the database server's ability to make DNS or HTTP requests to an external server controlled by the attacker. Data is exfiltrated via these external channels.

Finding SQLi - Common Test Strings:

• ' (single quote)
• ' OR '1'='1
• ' OR '1'='1' -- (comment out the rest of the query)
• ' OR '1'='1' # (MySQL comment)
• 1; SELECT SLEEP(5) -- (testing for stacked queries, if allowed)

Basic Exploitation Steps (Conceptual):

1. Identify Injection Point: Find where user input is used in a query.
2. Determine Number of Columns: Crucial for UNION-based attacks. (e.g., ' ORDER BY 1--, ' ORDER BY 2--, etc., until an error occurs).
3. Find Columns that Accept String Data: For displaying exfiltrated data.
4. Extract Database Information: Database version, user, table names, column names.
5. Extract Data: Get the juicy stuff!

Tools: SQLMap is a powerful automated SQL injection and database takeover tool. Burp Suite can also help identify and manually exploit SQLi.

Defense: Parameterized queries (prepared statements) are the primary defense! Input validation and output encoding also play roles. More on that in the Web App Security module!