An SQL injection attack is the execution of a malicious SQL query to alter data stored in a database or to access data without authentication or authorization. Websites or web applications using SQL databases are vulnerable to SQL injection attacks. The most common approach to launching an SQL injection attack is via user input fields. Hence, it is very important to validate data entered by users before sending it to the server. Refer to SQL Injection to know more.
Union-based SQL Injection:
Union-based SQL injection involves the use of the UNION operator that combines the results of multiple SELECT statements to fetch data from multiple tables as a single result set. The malicious UNION operator query can be sent to the database via website URL or user input field.
https://sqli.com/users/id=geek 'UNION SELECT * FROM users, courses --
The above URL contains a malicious SQL query that can fetch records of all users. “ –” at the end ignores all subsequent statements.
Demonstration using DVWA:
DVWA stands for Damn Vulnerable Web Application, which is developed using PHP and MySQL. It is a good tool for practicing ethical hacking and penetration testing. Union-based SQL injection attacks can also be performed using DVWA.
In the above image, the user ID is the user input field. The above image shows the result of injecting a malicious SQL query in the input field. From the results, we can infer that there are two columns corresponding to “First name” and “surname” respectively. But how did we figure out that there were two columns? The number of columns can be found by injecting either of the following two queries –
USER ID = geek 'UNION SELECT NULL, NULL, ... --
Keep inserting NULL until the database throws an error.
USER ID = geek 'ORDER BY 1, 2, ... --
Similarly, keep increasing the column number by one until the database throws an error.
Moreover, after obtaining details about table names, the attacker can get details of the corresponding columns.
Attackers can also get access to sensitive information like usernames and passwords.
Prevention:
- Sanitizing user input fields and forms – Putting restrictions on acceptable characters, and limiting the max length of the input before sending to the database server are some ways to validate input.
- Limiting data returned by database server – Instead of sending all data, a subset of data could be sent based on date/time interval.
- Parameterized Queries – Instead of appending user input to the SQL query, parameters can be used to pass the user input.
Name = geek Email = geek@example.com; DROP TABLE USERS --
Suppose the following SQL query is executed for storing the above data
INSERT INTO USERS (Name, Email) VALUES (<Name>, <Email>)
The following SQL query will be executed if data is appended to the query
INSERT INTO USERS (Name, Email) VALUES (geek, geek@example.com); DROP TABLE USERS --
To avoid the above scenario, use parameterized query. In this case, the injected query is also considered to be a part of the email. Hence, the SQL injection attack is avoided.
INSERT INTO USERS (Name, Email) VALUES (geek, geek@example.com'; DROP TABLE USERS -- )
