Anatomy of Error-Based SQL Injection

What is SQL Injection?SQL injection is a subset of the "code injection" attack method. SQL stands for Structured Query Language and is used by databases to store and retrieve information. Many websites require this capability web developers use a server-side scripting language - often PHP or ASP - to pass user input to a backend database. A very common example of a website using this capability is a site where you need to create a user account and password or one where you provide credit card information.The web developer creates a file that takes your input from a field and "translates" it into SQL so it can store, retrieve or modify information contained in a database. As with all injection-style attacks, by itself this is not an issue. However, if the developer fails to validate user input, a malicious user or other attacker can pass SQL statements directly to the database.Since that's the language the database "speaks," it will output the results to the attacker in much the same way it would if you were sitting on the server and passing SQL statements to the database via a command line. This gives an attacker direct access to the inside of a network because this traffic will pass through the DMZ and run commands on a server that is on the internal side of the network firewall.In 2015, SQL injection was possibly the most significant vulnerability in web applications. As much as one third of all web attacks are SQL injections. If you want to be a web application penetration tester you must understand this attack.There are two main methods of SQL injection: error-based and blind. This tutorial will address a common error-based injection technique, but know that there are other methods to perform this attack.We'll manipulate PHP files from an attacker machine to pass SQL commands to a backend database and reveal confidential data that a website stores. In error-based injection, we'll be looking for errors generated by the database when we pass unsanitized input in the URL. It's important to note that while the technique presented in this tutorial still works on some live victims, most web developers have gotten wise to this technique and try to block it.Blind SQL injection occurs when no errors occur as a result of passing SQL commands, or when a generic error message is displayed as a result of passing SQL commands. Blind injection requires the attacker to determine the underlying SQL database, tables, columns and rows through inference.In this case, the attacker passes commands to the database, guesses what the underlying structure is like, then tests the hypothesis. Blind injection is an advanced technique, but is very slow and inefficient to perform manually. Certain tools like SQLMap can be used to perform a blind injection if error-based injection does not seem to work correctly. Blind injections will not be covered in this tutorial. We are dealing with error-based only. How do networks block SQL Injection?The first and best way to stop a SQL injection attack in its tracks is in the script that passes the data in the first place. If a web developer validates user input, a generic error page can be returned. Generic pages intentionally reveal no information about the nature of the error or the server that returns it, so no information about internal logic or architecture is displayed.Unfortunately, this is often not the case. Most networks utilize an IDS (which stands for Intrusion Detection System) to detect and block SQL injections. An IDS can use established rules to determine if traffic on the network matches an attack pattern and if so, apply a rule to prevent the potential attack. How to bypass an IDSThe main problem with an IDS is that often, the person who manages the system has to write the signatures that detect an attack. This is the case for example with Snort, a very popular IDS. Since the maintainer of the system must manually create the rules, it may be possible to formulate the injection such that the IDS does not detect it, since it does not exactly match any signature.A common method of bypassing an IDS in the kind of attack presented in this tutorial is to encode the URL. There are many different encoding systems, such as decimal, hex, URL, UTF, etc. If the maintainer of the IDS creates a signature in standard SQL, he may have neglected to create a signature for exactly same attack if it's encoded.For example, I once found a SQL injection where an IDS prevented a UNION SELECT query in both standard SQL and encoded as hexadecimal, but not encoded as UTF-8. Encoding the attack can get around attempts by an IDS to block this type of attack and there are very many encoding systems that exist - most of which can be read by a browser or other medium (such as email) and may not be included in an IDS signature.The AttackWe'll present the steps of this attack. Before you try to iterate through each step in this attack, there are two points you must consider:

• First, you should have an understanding of the SQL language. An excellent tutorial is provided by W3Schools. You do not need to be a SQL master, but you should at least understand the ANSI standard commands.
• Second, as always, you must only launch this attack against a system you own or have written permission to test. Attacking a remote system otherwise is a violation of the Computer Fraud and Abuse Act in the United States and may result in a prison sentence and fine.

Step 1Append a tick (AKA single-quote) to the end of the URL: if the displayed webpage changes to display blank content or a SQL error message, it's vulnerable. The end of the URL should show the following:

.php?id=20'

Step 2:Append an " order by [abitrary_number]" to the end of the URL. Note the space: 

.php?id=20 order by 100

This should be after your PHP id. If the page displays body content without error, you need to iterate to a higher number. You can use a number higher than the PHP id provided in the URL. For simplicity, use an even number (if your id is equal to 20 for example, you could use 100 as "[arbitrary_number]"). If it displays with an error or with no body content, you need to iterate lower.I find the binary method to be useful when ordering this kind of data. If "order by 100" gives you an error or a blank page, change the number to 50. If the data is still valid, try 150 in your statement (this would be painful for reasons you'll soon see).The goal here is to find the last number you can use in your "order by" statement that displays a page with valid content that is not a SQL error. For example if:

.php?id=20 order by 30

gives valid content with no error but

.php?id=20 order by 31

gives a blank page or a SQL error, then 30 is the last valid number you can use. This tells you the number of columns in the current database.Step 3:Once you know the number of columns, insert a UNION SELECT statement. The format is "union select 1,2,3" etc. until you get to the highest number you found in step 2. Let's say the highest number you found was 10. If so, the syntax should be:

.php?id=20 union select 1,2,3,4,5,6,7,8,9,10

Somewhere in the page, you should see something that looks unusual: two numbers, one above the other. Usually one is larger and bold. The larger, bold number is the column you are working with. Scan the page for this pattern; don't forget to view the HTML source of the page since this may be included there but not displayed to you.Step 4:In your URL bar, replace the number that's the same as the bold number in step 3 with "user()".A username will appear on the page in place of the bold number.Let's say the large bolded number you found in step 3 was "4". Your syntax should show:

.php?id=20 union select 1,2,3,user(),5,6,7,8,9,10

Step 5:Replace "user()" with "version()" or "@@version". If the server is running a Microsoft SQL Server database you will see the version of SQL Server that's running. Other databases may use a different SQL parameter, so you may need to tweak this until you pass a correct parameter if it's not SQL Server.What's happening here? You are passing a SELECT @@VERSION command to SQL Server and it returns the version number to you. This is exactly what would happen if you ran the command in a CLI on SQL Server itself. Here's your syntax:

.php?id=20 union select 1,2,3,version(),5,6,7,8,9,10

Step 6:Enumerate the tables in the database. First, you need to replace "version()" with "table_name". Second, at the end of your URL you need to append a command " from information_schema.tables". Do not replace "table_name" with some other value - this is a standard and not a placeholder in this tutorial.In the page, you'll now see a list of all tables in the database. What's happening? You need to understand what "from information_schema.tables" means.In SQL Server, MySQL and PostgreSQL, the "information schema" is a standard that displays (in a read-only format) information in the database. Specifically, it displays (primarily) tables and columns in the tables. In this query, you're specifying the table information schema. You can also display the column information schema. We'll do this in the next step.FROM in SQL is used primarily with the SELECT command and is typically used to select a column, but here we are using the information schema. Since "information_schema" is a standardized format FROM, it behaves slightly differently than it normally would. This is a special use case.An important note: Oracle databases do not support information_schema. This injection will fail if the backend DB is Oracle or SQLite.Here's your syntax:

.php?id=20 union select 1,2,3,table_name,5,6,7,8,9,10 from information_schema.tables

Step 7:Select a table that looks interesting. A great choice would be - for example - a table named "users".The format for this query will be similar to the last, except instead of "table_name" we use "column_name", "information_schema.tables" becomes "information_schema.columns" and we add a WHERE statement to the end.The format for the WHERE statement should be "where table_name=[interesting table]". Replace interesting_table with...well, an interesting table, like "users". A list of the columns contained within the specified table will output to the screen.Here's the syntax:

.php?id=20 union select 1,2,3,column_name,5,6,7,8,9,10 from information_schema.columns where table_name=users

This is where things get very, very dangerous. Usernames and passwords could be stored here. Continuing by reading the information out of the relevant tables could reveal this information; it's likely the passwords will be encrypted or at least hashed, but an attacker can still get at that information and brute-force any encrypted passwords or reverse the hash (yes, hashes can be reversed if they are not salted).Even if an attacker can't obtain the password to the account, other information like credit card numbers, names, addresses and phone numbers of users or customers could be obtained. This information is highly valuable to identity thieves and is routinely sold on the black market. 

---

Thanks and please post any comments and questions below.