Databases are the primary means of storage for most applications, holding data about posts, user credentials, session data and much more. A poorly written backend can easily be exploited by attackers to exfiltrate data through the frontend, which could be used to access sensitive information or chain attacks together.
The root cause of SQL injection attacks is that user-input is directly supplied to form a query without parameterisation. This could occur primarily by using string literals or concatenation to build queries directly without using parameterization to substitute values.
The difficult part of SQL injections is that you have to pay attention to changes in the webpage. Even the most minute change in query can have an adverse effect on the response sent by the server so it is important to always see what part of the page gets affected and keep track of it.
Most modern frameworks and developer tooling protect against SQL injections by default through the use of ORMs or query builders, making it a harder to find exploit on most newer websites. However, it is still an important vulnerability to learn as you may never know when you might encounter it. It is still common place on companies that use legacy tooling.
UNIONs are a standard feature of ANSI SQL. Every SQL-based database has features this operator and it provides us with an easy attack vector for exfiltrating data from the database which could be user credentials, confidential data, and much more if the app is vulnerable.
UNIONs are used to merge the contents of two separate SELECT statements and output the results under two conditions. The first is that the number of columns selected in each SELECT statement is the same. The second being that the data types of the returned values on each column and the column being merged remain the same.
Imagine our simple website has these two tables in our fictional database.
Products Table
| product_name | description | category | in_stock |
|---|---|---|---|
| Hammer | A tool to hit stuff | Tools | TRUE |
| Saw | A tool to cut stuff | Tools | FALSE |
| Apple | Keeps the doctor away | Food | TRUE |
Users Table
| username | passwords | |
|---|---|---|
| admin | admin@123 | adminstrator@website.com |
| user1 | password_123 | user1@website.com |
If a website renders a UI element that wants to list out products, it would usually first retrieve all the items. However, imagine if we had a search functionality that allowed us to filter products by name and check if it's currently in stock which would look something like this.
SELECT product_name, description FROM Products WHERE product_name = '{ input from browser }' AND in_stock = TRUE
As an attacker, the query above is unknown. The first order of business would be to find out what fields are actually vulnerable. The most common spots that an SQLi could be hiding in is primarily within filters, sorts and paginations that use query strings. Others could be text boxes for search and login pages (I'll talk more about this later) or cookie fields.
To test if a field is vulnerable, we can use an apostrophe and two dashes. Two dashes signify that anything after it is a comment. If we search for '--, it should perform a query like this.
SELECT product_name, description FROM Products WHERE product_name = ''--AND in_stock = TRUE
See how everything to the right of the comment gets nullified? It should theoretically render nothing since there usually aren't any empty strings to return from the database. However, it proves that this field is susceptible to SQLi attacks. We can inject any of our own commands here on out.
If this doesn't work, it is also very possible that there are parentheses that aren't being closed properly so don't immediately rule out a field without trying different combination of these. The search input could also be placed inside a WHERE LIKE "%[SEARCH-TEXT]%" so you may also need to place percent signs around.
The next step would be to determine the shape of the query. This involves finding out how many columns the original query is fetching as well as their data types. The first way to determine the number of columns that our unknown query is fetching would be to try using ORDER BY and providing an index.
This mostly involves trial and error so it's a little annoying but it should be quick. If we search up ' ORDER BY 1--, we'll get a query resembling the one below. This should alter the order of the elements being rendered on screen.
SELECT product_name, description FROM Products WHERE product_name = '' ORDER BY 1 --AND in_stock = TRUE
We can increment the index one by one and repeat until we observe an error from the backend. This should allow us to determine how many columns are being fetched from the database. Now we can perform the main UNION attacks.
SELECT product_name, description FROM Products WHERE product_name = '' UNION SELECT username, passwords FROM Users --AND in_stock = TRUE;
The result of our injection would look a little like this. The data below will be processed by the backend and visible in the frontend however, it may be represented.
| Hammer | A tool to hit stuff |
|---|---|
| Saw | A tool to cut stuff |
| Apple | Keeps the doctor away |
| admin | admin@123 |
| user1 | password_123 |
The example attack above may seem easy upon first glance but it is normally a lot more harder than this. I showed a lot of the queries underneath which would otherwise not be visible to an attacker. However, There is another nuance to this that I haven't quite talked about and that is the SQL dialect the backend is using.
More older, enterprise companies might choose to use Oracle DB or Microsoft SQL servers while newer, smaller companies might prefer using MySQL or Postgres as their SQL dialect. Each database offers it's own syntactical differences that can break assumptions and can even mask technically sound injections to appear as invalid queries.
The fastest way to narrow it down would be to always try testing unique syntax for each database and see if any adequate response is returned:
MySQL: A space should be present for comments between the two dashes and the actual message. For e.g. -- This is a comment
Oracle DB: The function for substrings is referred to in an Oracle database as SUBSTR() rather than SUBSTRING() in more traditional databases.
Microsoft SQL Server: String concatenation is performed with a plus symbol. For e.g. 'Micro'+'soft'
PostgreSQL: Supports using hashtag for comments. So instead of '--, Something like ' # can be used.
If we are an attacker, we obviously won't know the exact table and column names to get access to the data. All SQL databases contain a database dictionary and according to the standard, they are all contained within a special table named INFORMATION_SCHEMA. This is again, another ANSI SQL standard so this won't have much change across dialects.
Data about tables can be accessed through:
SELECT * FROM information_schema.tables
Data about columns can be accessed through:
SELECT * from information_schema.columns WHERE table_name = 'TABLENAME'
Each database should have documentations about their specific implementation of information_schema.
Even if we somehow managed to exfiltrate the data through an SQL injection, there are still some other hurdles to face. Most web apps now hash password data so even if the database was somehow dumped through an exploit, assuming the password was properly hashed and salted, the raw data itself would pretty much be useless.
It is also entirely possible that an app doesn't really have any elements that dynamically render database contents. In that case, blind SQL attacks are used which are a tier up in terms of difficulty compared to UNION attacks. We have to make far more assumptions about these and I'll hopefully cover these in a future post.