Introduction
A UNION-based SQL injection generally consists of three steps: determining how many columns the query returns, finding compatible data types, and executing the malicious query. The number of columns can be determined by injecting ORDER BY queries with increasing values until an error is returned:
?id=1’ order by 1
?id=1’ order by 2
Discussion
Data types for each column can then be determined by guessing or interpreting error messages from different UNION SELECT arguments:
?id=1’ UNION SELECT 1, “test”, “value”
Finally, the malicious query requesting desired information:
?id=1’ AND 1 = UNION SELECT username, password, null FROM users
To prevent such attacks, an organization should design its databases and web interfaces in ways that prevent a user from executing them. The most common and secure approach is parsing the user’s input to construct safe queries and block unsafe ones rather than passing it directly to the database (OWASP, 2021). This approach is called input validation and uses inputs such as replacing service characters such as quotation marks with their corresponding escape sequences, substituting prepared statements constructed from user input.
This section examines the following injection: ‘ or extractvalue(0x3a,concat(0x3a,(select database())))#. The extractvalue function returns the text content from the first text XML node matching the second parameter (Oracle, 2022). 0x3a resolves to a “:”, the concat() function combines multiple string elements into one, and select database() returns the current database’s name.
Conclusion
Thus, the syntax resolves to extractvalue(:, :importantData)#, with the final # commenting out, and thus bypassing, all the remaining MySQL code. This query returns an error that exposes the database’s name. The database() function can be replaced with another one that similarly returns system information, such as version(), which allows the attacker to determine the database’s version (Oracle, 2022). In turn, knowledge of this information allows one to determine more elaborate attacks, for example, those targeting vulnerabilities in a particular version of the database.
References
Oracle. (2022). MySQL 8.0 reference manual. MySql.com. Web.
OWASP. (2021). SQL injection prevention cheat sheet. OWASP.org. Web.