MySQL-i through unverified user input through PHP and POST
Warning: This post was written in half an hour, if you see mistakes, let me know!
Everyone here probably has heard of SQL injection before, however it surprises me that many developers don't quite understand how SQL injection works. Often, developers also don't know the cause of leaks that allow for SQL injection, often resulting in leaks in applications throughout the web.
I must add, before continuing this blog post, that I created this simple simulation to show how easy it can be, not how easy it always is. I also wrote this as a favor to a friend who is developing a game called Disrupt that is all about hacking, exploits and more cyber-crime.
Also, this post assumes a worst-case scenario. I always assume a worst-case scenario, and always try to close up my servers as much as I can.
The dangers of SQL Injection: Information
Before listing possible causes that allow for easy SQL injection, let's just name a few dangers and possible catastrophic situations that can be caused by SQL injection.
Columns and MySQL version
For one, with a little patience and when error reporting is turned on on a production server running MySQL and PHP, private data can be extracted with much ease. The first few UNION ALL injections are used to determine the MySQL version and the number of columns that have been selected in the (now) left-hand side of the query.
When the MySQL version is known to the hacker, he or she can now simply search Google for a few MySQL exploits and vulnerabilities and try those. These leaks can be all the tools the hacker needs to obtain the information he or she needs.
When the hacker knows how many columns the left hand side of the query selected (it's a left-hand side, because the hacker is adding a UNION making his or her query the right-hand side), he or she can simply start selecting any data they want. The MySQL version helps the hacker understanding the features of the server, aside from the possible vulnerabilities.
A hacker usually starts by obtaining the name of the current database. They select
database() and a few dummy fields to match up with the left-hand side column count. When, for example, the left-hand side selects a collection of rows, the hacker's query will simply be added to that set. Any view displaying the results, will also show the database name.
Table count and table names
When the hacker knows the database name, and if the MySQL installation is a default one, the hacker can use the information_schema database contained in each MySQL installation to obtain a count and list of tables of the
database() through the information_schema.tables table.
The hacker now knows your database's tables. Need I say more?
When the hacker knows the tables, he or she can easily extract a list of columns for each table. When the hacker knows the columns, it is very easy to start extracting rows of data, or simply a group_concat of a single field within a table.
During a security college, we used this technique to extract a list of credit card numbers, user names and password hashes from a database through a HTML form. Once again, worst case scenario, but each default installation is a worst case scenario.
The dangers of SQL Injection: Filesystem
Some servers even have the MySQL outfile directive enabled. When SQL Injection is possible on a server with this directive, and the developer has a directory in the webroot with a 0777 permission setting, you're screwed. I say screwed, because a hacker can then simply write to a php file through a query and create a script that executes code from an URL (or simply from within the script when
eval() is disabled).
This opens up even more doors to the hacker, he can now explore the server for more vulnerabilities, execute commands, obtain more version information (
phpInfo()). At this point, it's probably best to turn off the server and start over.
The first and foremost cause of this leak, is not validating user / third-party input. Every single piece of data that is put into an SQL query should be verified and escaped. It should be impossible to inject quotes, control characters, operators and such.
It also helps to use a unique user per database, and only use writing permissions where needed. When you only display information from a database in a presentation layer (and not a back office application), then why would you even think about using writing permissions on a database. Disable every single setting other than SELECT and perhaps UPDATE and INSERT, the rest opens up doors for hackers.
Disable any command or function in PHP that allows for commands to be executed in the shell. Disable the outfile directive in MySQL, don't ever use the 0777 octal mask. Every directory where writing is required, should be outside of the webroot directory and should deny execution permissions.
I could go on forever, I'm sure my environments have leaks I don't know about as well, however this one is extremely dangerous.
Check out this pen.