Security is one of the major issue we want to take care of other than meeting dateline. Especially when it comes to server data. We always want our data to be correct and secure. No one should be able to manipulate these data and these data should only be confine to people that have access to it. One should need to know the attacks on server data in order to better secure them. In this article, we will discuss SQL injection attack on databases.
SQL Injection Attack
SQL Injection Attack is the most common type of problem most web system face. Let's consider the following code
<?php
$name = ''test'; DELETE FROM users;';
mysql_query('SELECT * FROM users WHERE name='.$name.' ');
?>
Basically, the query above will caused all the data in your the users table to be deleted. This is usually due to inconsistency during development by different people. Lucky, there are many ways to prevent this.
The Solutions
Escape Mechanism
In order to prevent SQL injection attack, PHP's automatic input escape mechanism, magic_quotes_gpc, can provides some rudimentary protection (this is required to be enabled on php.ini). The magic quote will append a backslash to characters that is used to break out of a value identifier. eg, ', ", . etc. However, this is not always automatically enabled due to various reason. If you are using MySQL, it has a built-in function mysql_real_escape_string() to escape input characters that are special to MySQL. However, before calling a database's own escaping mechanism, it is important to ensure that no two escape mechanism is being used at the same time.
Escape Without Discipline
We talk about how escape mechanism can help us prevent SQL injection attacks. However, with the help of escape mechanism doesn't means that we are free from SQL Injection. We can still do some code injection even though we have implemented escape mechanism. Let's consider this example,
<?php
$id= '0; DELETE FROM users;';
$id = mysql_real_escape_string($id);
mysql_query('SELECT * FROM users WHERE id='.$id.' ');
?>
Similarly, SQL Injection attack is still possible since the escape mechanism only add additional backslash to characters that is used to break out of a value identifier such as single or double quote. There are no particular character that will need a backslash on the $id variable string. Thus, nothing was being added and the query will run as usual (danagerous). The solution to this situation is pretty simple, Discipline. Many times developers will like to skip the important process of adding quote in the SQL query. Being discipline and add these particular characters into your query will definitely save yourself from SQL injection attack. Example,
<?php
$id= '0; DELETE FROM users;';
$id = mysql_real_escape_string($id);
mysql_query('SELECT * FROM `users` WHERE id="'.$id.'" ');
?>
This will prevent the SQL from running the second query since there is no such id(int) as '0; DELETE FROM users'. The other way is to valid whether the particular $id is an integer.
<?php
$id= '0; DELETE FROM users;';
$id = (int) $id;
$id = mysql_real_escape_string($id);
mysql_query('SELECT * FROM users WHERE id='.$id.' ');
?>
This will cast $id into integer If the input is not entirely numeric, only the leading numeric portion is retrieved. eg, '999; DELETE FROM users;" will return 999. If the input doesn't start with a numeric value or there is no numeric values, 0 is returned. This is the best way to determine whether a particular value is an numeric value. No escape mechanism is required for this method but this can only be used for numeric validation.
LIKES Operator Danger
The LIKES Operator in SQL is a very powerful filter instruction that allows the query to use '%' for any characters that occurs zero or more times or '_' for a single character. However, both magic quote and built-in escape mechanism will skip these two special character. Thus, denial of services attack can be launch into the web server using SQL injection attack. Consider the following example,
$para= mysql_real_escape_string('%12'); // still %12
mysql_query('SELECT * FROM document WHERE number LIKE "'.$para.'%"');
Imagine this is a transaction table which has millions of documents. Searching number 12 in a particular number for a million times will definitely denial the access of this web portal services. Hence, we will need something that will escape these two special characters. We can use addcslashes() in PHP to add the required backslash onto these special characters!
$para= addcslashes mysql_real_escape_string('%12_')); // it will be \%12\_
mysql_query('SELECT * FROM document WHERE number LIKE "'.$para.'%"');
The above correct solution will provides no result due to the additional of \% and \_ into the query.
MySQL Exception
Fortunately, if you use MySQL, the mysql_query() function does not permit executing multiple queries in a single function call. If you try to stack queries, the call fails. However, other PHP database extensions, such as SQLite and PostgreSQL will permit such action.
Base64-encode
A common techniques is to use base64_encode in PHP to encode all data that are stored in the database. This will prevent any special character from damaging your query statement as any new query to the database will have to be encoded before the database recognize.
<?php
mysql_query('INSERT INTO users (name) VALUES "'.base64_encode('test').'"');
$name = base64_encode('test'; DELETE FROM users;');
mysql_query('SELECT * FROM users WHERE name=''.$name.'"');
?>
However, base64 encode will roughly increase data size by 33%, requiring bigger storage space. Moreover, PostgreSQL query with LIKE will fail due to base64.
Prepared Statements
The most efficient and powerful way to solve SQL injection attack, i would said it would be prepard statements. Prepared statements will only required to set up a statement once, and then it can be executed many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. A typical prepared statement is shown below,
#MySQL SELECT * FROM Users WHERE name = ?
The ? is what is a called a placeholder. You would need to supply the value for it during execution. You can read more about Prepared statements in MySQL at their website. Here is one example,
<?php
mysql_query('PREPARE search_template FROM "SELECT name FROM Users WHERE name = ?"');
mysql_query('SET @param = "test"');
mysql_query('EXECUTE search_template USING @param');
mysql_query('SET @param = "hello"');
mysql_query('EXECUTE search_template USING @param');
mysql_query('DEALLOCATE PREPARE search_template;');
?>
Unlike ad-hoc query string, the second query being executed with 'hello' as parameter doesn't add on the additional overhead of the search template which was prepared previously.
SQL Error Handling
How do hackers know your table name and the query you wrote? The answer is pretty simple and straight forward. It is mainly due to poor SQL error handling by the developers. The hackers will try their very best to break your SQL query in any way that will result in an error. Once the error is displayed, they will have many information to launch a proper attack. Let's consider an example,
<?php
#query=test;DELETE FROM breakplease;
$query = 'SELECT * FROM user WHERE name ='. base64_decode($_GET['query']);
mysql_query('$query);
?>
This will caused a decent error to be display on the page. And the user will have an idea what is being passed into the query string and what table is being used. Both structure, fields and GET parameter were exposed to the hacker in this way.
The best way is to prevent such embarassement by providing a more end-user type of message with a php error handling function.
function sql_failure_handler($query, $error) {
$msg = htmlspecialchars('Failed Query: {$query}<br>SQL Error: {$error}');
error_log($msg, 3, '/home/site/logs/sql_error_log');
if (defined('debug')) {
return $msg;
}
return 'Requested page is temporarily unavailable, please try again later.';
}
#query=test;DELETE FOM breakplease;
$query = 'SELECT * FROM user WHERE name ='. base64_decode($_GET['query']);
mysql_query('$query) or die(sql_failure_handler($query, mysql_error()));
This will provides us developer with relevant message on our TEST environment and provides the end-users with a more standard message on LIVE environment.
Authenticate Data Connection
This concern on how we store our application's database credentials. Some will placed it into an external files and gives it a non-PHP extension such as .inc. This post a problem as the file can be access directly outside of the server and it will be shown with plain text since it is not interpreted by PHP machine. Hence, we will have to better secure these database credentials from unauthorized access. One solution is to restrict the access of .inc in that particular folder defined in your web server (which many might not have in a shared hosting environment).
<Files ~ '\.inc$'> Order allow,deny Deny from all </Files>
Or just changed it into .php extension so that it will not be exposed in plain text. However, if there is code written in that file, the same issue might still happen. If you have root access, you can do the following in your apache configuration file, httpd.conf
<VirtualHost ilia.ws> Include /home/ilia/sql.cnf </VirtualHost>
Now, set the file sql.cnf with the following codes,
SetEnv DB_LOGIN 'login' SetEnv DB_PASSWD 'password' SetEnv DB_DB 'my_database' SetEnv DB_HOST '127.0.0.1'
This way, the details can be access via $_SERVER or getenv() in your PHP script without hardcoding it somewhere in your system.
echo $_SERVER['DB_LOGIN']; // login
echo getenv('DB_LOGIN'); // login
A more powerful way is to hide them even from the script that needs them. How to do that? we stored it into PHP.ini directives by specify the default login. These also can be set inside of Apache configuration file.
php_admin_value mysql.default_host '127.0.0.1' php_admin_value mysql.default_user 'login' php_admin_value mysql.default_password 'password'
Now you will connect to your database without any parameter and it will takes the default value from your apache configuration file.
mysql_connect()
Reduce the damage
It is a good practice to always indicate the number of results needed to retrieved from the table. Consider the following example,
<?php
$name = '"test"; SELECT * FROM users;';
mysql_query('SELECT * FROM users WHERE name='.$name.' LIMIT 1');
?>
Limiting the number of result return can help minimize the damage of SQL injection attack. Especially during authentication process. On the other hand, it is also a good idea to restricting the database permissions. By limiting the users permission, the damage of SQL injection attack can greatly minimized. Eg, only select access should be given to the user on the above query. Therefore, if the attacker tried to change the password by doing a SQL injection, it will fail (unauthorizes access).
Another alternative is to enhance the performance between your database and the script. We see Denial of service attack can be launch against the system due to the usage of LIKES operator. Having a good performance between the database and the web server is strongly advisable to minimize the impact on our business. Thus, to minimize database overloading, we can look at a few simple rules.
- Only retrieved the field you need. '*' is always misused by lazy developers
- try unbuffered query. It speeds up query but limit the work with only one query
- You can speed up connection process by using persistence connection. Eg, in MySql
mysql_pconnect('host', 'login', 'passwd');However, if the database is not configure to allow many connection, further connection request will be rejected (since persistence connection is hooking the line). Hence, denial of services occurs.
MYSQL User account
For different action in the system, different MYSQL user account should be used. This will greatly help minimize the risk of damage done to the database if certain page was compromised. For example, a login page should only have SELECT access as other action is redundant. However, if you provide a full access level to a simple login page where any unauthorized user can access, malicious user can easily change the password through the text box provided to gain access to your portal if SQL injection vulnerability was found. Hence, brute force is not necessary to break down the door. They simply ring the bell! You are just inviting guess into your portal.
Summary
Although SQL injection attack is a common attack launch against many website, web developers have to ensure that these attack is minimize and eliminated. The solutions above might not be full bullet proof solution for future SQL injection attacks. Nonetheless, it can be used for discussion on solutions of future SQL Injection attack.