Union-based SQL Injection is a type of SQL injection attack that exploits the “UNION” operator in SQL to retrieve data from two or more tables in a database. This type of attack is often used to extract sensitive data from a database, such as usernames, passwords, and credit card numbers.
Just like the word “UNION” consider it as the coming together of two or more things.
To understand how a Union-based SQL injection works, it is important to first understand the “UNION” operator in SQL. The UNION operator is used to combine the results of two or more SELECT statements into a single result set. The SELECT statements used in a UNION must have the same number of columns, with the corresponding columns in each SELECT statement having the same data type.
I will give you a real-life scenario to better understand what I spoke about just now.
Imagine that you have two toy boxes, one with red toys and one with blue toys. You want to put all the toys together in one big box. You can do this by using the “UNION” operator, which is like a magic tool that can help you combine the toys from both boxes into one big box.
However, to use the “UNION” operator, you need to make sure that the toys in each box are organized in the same way. For example, if you have three red balls in one box, you need to make sure that the other box also has three blue balls, because the “UNION” operator requires that the boxes have the same number of toys and the same type of toys.
Now let’s say that you want to find all the toys that are round in shape. You can use a “SELECT” statement to look for all the round toys in the red toy box, and another “SELECT” statement to look for all the round toys in the blue toy box. Then, you can use the “UNION” operator to combine the results of both “SELECT” statements into one big list of round toys.
In the same way, a Union-based SQL injection attack uses the “UNION” operator to combine the results of two or more SQL statements into one big result set. The attacker injects a malicious SQL statement that includes a “UNION” statement and a “SELECT” statement that retrieves data from a different table in the database. To make the attack work, the attacker must ensure that the columns in the original SQL statement match the columns in the attacker’s “SELECT” statement.
Just like in the toy box example, the attacker needs to make sure that the data in both tables are organized in the same way, with the same number of columns and the same type of data. If the attacker can successfully inject the malicious SQL statement and retrieve sensitive data from the database, they can use that information to do bad things, like steal your personal information or take control of your accounts.
In a Union-based SQL injection attack, the attacker attempts to inject a malicious SQL statement into the original query, with the goal of retrieving additional data from a different table in the database. The injection typically involves appending a UNION statement to the end of the original query, followed by a SELECT statement that retrieves data from the attacker’s chosen table. The SELECT statement is carefully crafted by the attacker to ensure that the columns in the original query match those in the attacker’s SELECT statement.
Here’s an example of a Union-based SQL injection attack:
Let’s assume that there is a vulnerable website that has a search form that takes users’ input and queries a database. The search query might look something like this:
SELECT * FROM products WHERE name = ‘input’;
The query above might look quite confusing but again, I will break it down.
Imagine that you are playing with a big box of toys, and you want to find a specific toy in the box. You can ask someone to help you by saying, “Can you please find the toy that is called ‘input’ and give it to me?”
In the same way, when you use the SQL statement “SELECT * FROM products WHERE name = ‘input'”, you are asking the computer to find a specific record in a table of information (which is like a big box of data). The table is called “products”, and you want to find the record that has a name that matches the word “input”.
The “*” in the statement means “everything”, so you are asking the computer to give you all the information about that specific record, not just the name.
Overall, this SQL statement is like a special instruction that you can give to a computer to help you find specific information that you need, just like you would ask someone to find a specific toy in a big box.
With the query above, just assume you’re on eBay and you are under the fashion section and you search for “scarf”. The query to eBay’s database might look something like SELECT * FROM Products WHERE name = ‘scarf’;
The SQL statement used by eBay’s website might look something like “SELECT * FROM Products WHERE name = ‘scarf’;”.
In this statement, “Products” is the name of the table that contains information about all the products on eBay’s website, and “name” is the name of the column in that table that contains the names of the products. The statement is telling the database to look for all the products in the “Products” table where the name of the product is exactly equal to the search term “scarf”.
The website then displays the search results to you based on the information returned by the database. This is just one example of how websites use SQL statements to retrieve and display data from a database based on user input.
An attacker can exploit this vulnerability by injecting a Union statement that retrieves data from a different table in the database, such as the users’ table. The injection might look something like this:
‘ UNION SELECT username, password FROM users —
Again, I’ll explain what the above means.
Let’s say you are playing a game like Tetris or even Candy Crush online and the platform you’re playing this game on has a list of players and their scores. You can see your score, but you want to know the scores of all the other players. You can ask the game to show you the scores of all the players, not just your own.
In a similar way, the SQL statement “UNION SELECT username, password FROM users –” is asking a database to give you access to information about users that you are not supposed to see.
The statement has two parts: “UNION” and “SELECT username, password FROM users –“. “UNION” is used to combine the results of two or more SELECT statements. In this case, the SELECT statement is asking the database to give you the usernames and passwords of all the users in the “users” table.
This SQL statement is dangerous because it can allow unauthorized users to see sensitive information such as usernames and passwords, which can be used to log into other accounts or cause other security issues.
However, there is something new at the end of our query up there and that is the “–” at the end. There’s a reason for that and I’ll address it.
Let’s say you are writing a letter to a friend, and you want to add a secret message to the letter. You can write the secret message in a different color ink, but you don’t want your friend to see it.
To keep the message hidden, you can put a piece of paper over it so your friend can’t see it.
In a similar way, the double dash “–“ is used to hide the injected code in a SQL injection. When you add the “–“ at the end of the injection, it tells the database to ignore the rest of the original query. This ensures that the injected code is executed, but the rest of the query (which might include security measures to prevent SQL injection) is ignored.
It’s like covering up the original query so that the injected code can be executed without triggering any security measures that might be in place.
This is another example of how hackers can use clever techniques to get around security measures and gain unauthorized access to sensitive information.
When the vulnerable search form is submitted with the injected code, the resulting query would look like this:
SELECT * FROM products WHERE name = ” UNION SELECT username, password FROM users –‘;
This query will retrieve the usernames and passwords from the users table and return them to the attacker. The attacker can then use this information to gain unauthorized access to the website or other sensitive information.
Knowing how dangerous and disastrous this form of vulnerability is, how can this be remediated by security teams.
There are several ways that security teams can prevent Union-based SQL Injection attacks and other similar types of vulnerabilities:
- Input validation: One of the best ways to prevent SQL injection attacks is to validate user input. This means ensuring that the user is only entering the type of data that is expected, and that any special characters are properly encoded or filtered out.
- Parameterized queries: Another way to prevent SQL injection attacks is to use parameterized queries, which allow the application to send input to the database in a safe and controlled manner.
- Database access control: Security teams can also prevent SQL injection attacks by controlling the access that applications and users have to the database. This means limiting the privileges of users and applications to only what they need to perform their intended functions and by using strong authentication and access controls.
- Regular security assessments: Security teams can also conduct regular security assessments and penetration testing to identify vulnerabilities in the application and database, and to ensure that security measures are working effectively.
- Regular updates and patches: Keeping software and systems up-to-date with the latest security patches and updates are also important to prevent known vulnerabilities from being exploited.
By using these techniques and others, security teams can help prevent Union-based SQL Injection and other types of attacks, and keep applications and data safe from unauthorized access and other security threats.
There is a saying that no system is 100% secure and also when there is a will, there is a way. With the measures laid down, you’ll be surprised that hackers still evade all these measures. The big question would be how do they do it?
The truth is Hackers are constantly evolving their techniques to get around the security measures put in place by security teams. They use various techniques to exploit vulnerabilities in software and systems, including Union-based SQL Injection.
Some of the ways hackers get around security measures include:
- Evading input validation: Hackers can use techniques like obfuscation and encoding to get past input validation checks, making it possible to inject malicious code into the application.
- Bypassing parameterized queries: In some cases, hackers can find ways to bypass parameterized queries and inject malicious code into the application.
- Exploiting application and system vulnerabilities: Hackers can also exploit vulnerabilities in the application or system itself to gain unauthorized access to data or to execute malicious code.
- Social engineering: Hackers may use social engineering techniques like phishing emails to trick users into providing sensitive information or clicking on links that install malware.
- Zero-day attacks: Hackers can also use previously unknown vulnerabilities in software or systems, known as zero-day vulnerabilities, to carry out attacks before security teams have had a chance to identify and patch them.
The security landscape is constantly changing, and hackers are always looking for new ways to get around security measures. It’s important for security teams to stay up-to-date with the latest security threats and to use a variety of security measures to defend against attacks. This includes not only preventative measures but also proactive detection and response measures to identify and respond to attacks when they occur.
I hope you enjoyed reading my blog post! If you found the content useful or informative, I would really appreciate it if you could take a moment to leave a comment and share the post with your friends and colleagues. Your feedback and support help me to continue creating valuable content for you readers. Thank you for considering and I look forward to reading your thoughts!
Best
Tobi
