September 21, 2015
Anatomy of Error-Based SQL Injection
September 21, 2015
Learn more about SQL core concepts and attacks today by taking these free courses:
- 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.
.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 100This 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 30gives valid content with no error but
.php?id=20 order by 31gives 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,10Somewhere 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,10Step 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,10Step 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.tablesStep 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=usersThis 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.