Saturday, 28 November 2015

Penetration testing of MySQL Database – Webmasters Guide

Hello friends, today we will learn how to perform Penetration testing of MySQL database. As we are aware that most websites use MySQL database as their core database. It is necessary for every webmaster to perform basic penetration testing of MySQL database in order to prevent his website from getting hacked by some malicious hacker. So friends, lets begin our penetration testing tutorial of MySQL database. This guide will be helpful for Hackproofing MySQL database.

Penetration testing of MySQL Database – Webmasters Guide :

MySQL is an open source database but its regularly gets updates and new functionalities are introduced as part of every new version. But these new functionalities are also responsible for opening gates to attackers for performing their malicious tasks. Like Union and subquery functions were introduced in version 4.0x which resulted into union exploitation vulnerability SQL injection. Similarly version 5.0x introduced Information Schema and triggers functionality which resulted into multiple SQL injection attacks like extraction of database tables and contents, time base or trigger based sql injection attacks.

So if a webmaster want to protect his/her website, he must be aware with new functionalities and their impacts and ways to protect their websites from vulnerabilities resulted because of introduction of these new functionalities.

Note: Versions below 4.0 were also vulnerable to multiple SQL injection attacks like Boolean Exploitation SQL injection and time delay based SQL injection. Fixes were released for these but they haven’t patched it 100% as version above 4.0 are also vulnerable to advance version of these attacks.

It is very important to note that most SQL Injection attack occurs because of two things, first input data is not correctly handled or sanitized and second is because of use of dynamic queries. We will cover all possible issues step by step, so keep reading. But before going through this, you must know how to find a website is vulnerable to SQL Injection attack. 

For beginners:
  • To find a site vulnerable to SQL Injection:
  • Open a search engine like Google or Bing
  • In Search box type php?id= / inurl:?id= / ?id  and click on search icon. (Type any one at a time)
  • Then click on a link, for example (
  • Let it open, then replace the id number to ' , example :'
  • If you get an error, then the site is vulnerable to SQL Injection

This article about Pentesting of MySQL will cover below topics in detail :
  • Single Quotes Issue
  • Multiple queries separated by Semi-colon(;)
  • Fingerprinting MySQL database
  • Gathering MySQL Version
  • MySQL server users
  • Gathering Database Name
  • Information SCHEMA
  • Standard SQL Injection
  • Blind SQL Injection Penetration testing

Single Quotes Issue :

If you are using MySQL database then it’s must to take consideration how strings could be represented in a statement like single quotes. Under MySQL, there is a standard way to bypass the need of single quotes, having a constant string to be declared without the need for single quotes.

Note: MySQL interprets escaped apostrophes (\’) as characters and not as meta characters.

Now suppose single quotes are not filtered properly then let’s say we wish to know the value of a field named ‘password’ in a record. This can be easily achieved by any of below combinations :
  1. password like ‘A%’
  2. password LIKE 0x4125 –> using ASCII equivalent of above
  3. password LIKE CHAR(65,37) –> equivalent char function

Now if we are doing filtering of single quotes we also have to keep in mind to filter is corresponding ASCII value and equivalent char() function.

Multiple Queries Separated by Semi-colon or say mixed queries :

Some developers has a habit to writing multiple queries in a single line separated by semi-colon. MySQL library connectors do not support multiple queries separated by ‘;’ so there’s no way to inject multiple non-homogeneous SQL commands inside a single SQL injection vulnerability like in Microsoft SQL Server.  Hence the resulting query will result into error.

For example below injection will result into error and hacker can easily identify that your website is vulnerable to SQL injection attack.

; update tablename set code='javascript code' where 1 --

So always keep in mind before using multiple queries separated by semicolon.

Fingerprinting MySQL database :

Above two points which I explained are from developer perspective which is to be covered by Whitebox penetration testing approach. Now lets focus on black box penetration testing things, means how a hacker will figure out vulnerabilities in your system.

Hacker’s primary step is to identify the database used by web application, that application is using MySQL or something else.  Its very easy to identify the same as there is very interesting fact about MySQL comment mechanism.  There are lot of databases which use /* */ for putting comments but only MySQL which interprets exclamation character (!) if its a part of comments.

For example :
/*! and 1=0 */

So if you want to avoid easy fingerprinting of your database, its recommended to sanitize exclamation character (!) from comments.

Gathering MySQL Version :

After fingerprinting, hacker's next task is to know the version of MySQL database as version can help them to explore known vulnerabilities within particular version.

There are multiple ways of finding version in MySQL, easiest one’s are mentioned below:

  1. By using global variable ‘@@version’ . (for example : union all select @@version /*)
  2. By using predefined function ‘version()’. (for example : union all select version() /*)
  3. Using comments mechanism.

Comments Fingerprinting Mechanism : For example giving version with comments and exclamation character i.e.

Version /*!40210 and 1=0*/

Now above command actually translates to following:

if(version >= 4.2.10)  add 'and 1=0' to the query.

So in order to avoid this issue, its good to block sanitize keywords like version but sometimes we cannot do so as it is used by multiple inbuilt API’s.

MySQL Server Users :

MySQL server supports two different types of users.

  1. User() : The one who is connected to the database.
  2. Current_User() : Internal user who executes the query.

We can easily identify MySQL users by using following injection attacks :

  1. Union all select user() /*
  2. User like ‘root%’ or ‘admin%’

Both above ways will result into same output : user@hostname

Gathering Database Name :

Its quite easy  to gather the database name as in MySQL we have an inbuilt function DATABASE() which extracts the database name details as mentioned below:
  1. union all select database() /*
  2. database () like’%’

Both ways result into same output i.e. database name.

Information SCHEMA – Hacker’s friend:

In SQL version 5.0 , a new view named ‘Information Schema’ was created. It allows users(hackers or developers) to get all information about databases, tables, and columns, as well as procedures and functions.

This in itself is a very huge topic but I wish to cover some basic tables in information schema view:

Schemata – This contains list of all databases that user has.
SCHEMA_PRIVILEGES – It contains privileges that user has on each database.
Tables – it contains all tables that database user has.
Columns – contains all column names that user has.
Table_privileges – contains privileges which db user has on each table.
Routines – all procedure and functions user has.
Triggers – list of all triggers in database.
User_privileges – privileges current user has on database.

There are much more but will cover them in later articles.