Learnings and notes from my #100daysofhacking
As a developer, I think the first security vulnerability I ever heard about was SQL injection. But nothing very complicated: "use this function to validate the user input and avoid SQL injection".
For a long time I thought SQL injection was an outdated attack and no one would fall for that because programming languages have resources to prevent it and there were practices that everyone followed to avoid this attack ( how naive hahaha)
I never stopped to learn about SQL injection in depth until the first week of my #100daysofhacking. And I'm very surprised about how it goes beyond what I thought it would be! 🤯
SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database allowing it to view data that would not normally be accessible.
The impact of this attack can have in an application or a business varies, but it can result in loss, corruption or disclosure of unauthorised data. Sometimes can lead to the total application takeover.
How it works
A SQL query is a command used to query an application's database. In general the way web applications construct SQL statements involving SQL syntax written by the programmer is mixed with user-supplied data. For example:
SELECT title, text FROM news WHERE id=$id
We are looking for title and text from news table where id is the one provided by the user.
The way this user provided data is handled can open the possibility of a SQL injection attack: the user can insert or "inject" either a partial or complete SQL query that makes the original SQL statement execute other actions.
How to know if an application is vulnerable to SQL injection?
The place in an application where a SQL query can be injected can vary, it's not only in fields or forms where the user insert data, almost any source of data can be an injection vector: environment variables, parameters, external and internal web services.
The first step is to identify where the application interacts with the database, list input fields whose values can be used in a SQL query and then test them separately, trying to interfere with the query and to generate an error.
The initial test can be to add a single quote ' or a semicolon ; to the field or parameter and observe the server response, depending on the response it's possible to identify that the field or parameter under test is vulnerable to a SQL injection attack. Note not only the server responses but also the source code, sometimes the response might be present but not visible to the user.
Example
Imagine an application that list products and filter them by category. The URL with a filter would be something like this:
https://insegure-website.com/products?category=shoes
Test injecting the single quote:
https://insegure-website.com/products?category=shoes'
The error message after the quote injection would be the following:
An error occurred: PG::SyntaxError: ERROR: unterminated quoted string at or near "'shoes'' limit 1" LINE 1: ...ts where category ='shoes'... ^ : select * from products where category = 'shoes'' limit 1.
This error gives us an indication that:
- This is a point in the application that interacts with the database.
- The single quote changed something in an unexpected way.
What happened was that the single quote was inserted directly in the SQL string and terminated the query early. This caused the syntax error we can see in the error message. This behaviour indicates that the application might be vulnerable to SQL injection.
Exploration
After identifying the vulnerability, an attacker can use various techniques to explore an application.
Even though SQL language has a pattern, each database management system (DBMS) has different ways of interacting with data. Commands, functions, comments and other things can vary from one to another, so it is important to identify the application database type. I'm not going to cover this here, but you can use this article from PortSwigger as reference to learn more about this: Examining the database in SQL injection attacks
Visible SQL Techniques
Used in cases where the output of the SQL query is returned within the application's response.
UNION
Technique where the UNION keyword(used in SQL to execute one or more SELECT queries and append the result to the original query) is used to inject a query forged by the attacker to obtain values from other columns and tables.
Is important to note that for this attack to work the injected query has to have the same quantity of parameters/columns as the original query to avoid a syntax error.
So the first step is to identify the quantity of columns that are being returned by the original query.
There are a few ways to do this, one of them is inject a series of ORDER BY commands and increment the column index until an error occurs. In this command a column can be specified by its index, so we don't need to know the column names. Example:
https://insegure-website.com/products?category=shoes'ORDER BY 3
In this example if we increment the index one more (to 4) we can see an error in the response. This means that the original query return 3 columns.
After finding out the quantity of columns, the second step is to identify the data type of the columns. In general the interesting data to explore are in string format, so we need to find out which columns return this data type. To do this we can test each column to check if it can receive a string, using a UNION SELECT placing a string in different columns until we confirm which ones accept this data type:
https://insegure-website.com/products?category=shoes'UNION SELECT 'a',NULL,NULL--
https://insegure-website.com/products?category=shoes'UNION SELECT NULL,'a',NULL--
In the example a string is placed between nulls, being alternated one column at time, the -- are comments in SQL, meaning that the rest of the query will be ignored.
Once identified which columns return string, we can inject the new query:
https://insegure-website.com/products?category=shoes'UNION SELECT username, password, NULL from users --
Example where there is a table users in the application and through SQL injection we can have access to usernames and passwords.
There will be cases where only one column is returned in one application or only one of the columns are of the data type desired (in our case string), when this happens it is possible to extract one information at time or concatenate the results of two columns in one.
Not Visible SQL techniques
Are ways to explore a SQL vulnerability even when the SQL query result is not visible to the attacker, known as Blind SQL or Inferential SQL injection. I found this attack to be the more interesting and surprising one when studying about this vulnerability, it made me reflect a lot about the fact that hacking is also about creativity. That's why I'll write about this attack in another article.
References
- PortSwigger: https://portswigger.net/web-security/sql-injection
- PortSwigger labs are great for hands on learning about SQL injection.
- OWASP testing guide: https://owasp.org/www-project-web-security-testing-guide/latest/4-Web_Application_Security_Testing/07-Input_Validation_Testing/05-Testing_for_SQL_Injection
- OWASP top 10 (2017): https://owasp.org/www-project-top-ten/2017/A1_2017-Injection
Cover image: Photo by Diana Polekhina on Unsplash
Top comments (0)