SQL injection: attacks and defenses.

Share

***Introduction:

This is awesome that attackers are using several methods to get into some restricted part of web applications. They are discovering new methods day by day.

But SQL injection is now widely spreading method to hack your web sites or web apps.

So here is the question, what is SQL injection?

## SQL injection is a code injection technique that helps attacker to get some vulnerable information from your database. Actually it happens on database layer. This vulnerability occurs on PHP and ASP scripts for mistake of its coder.

***Where and why this vulnerability happens:

## Your coder may forget or lazy to handle SQL query errors.

## Where malicious users can insert SQL syntax on form requests.

## Forget to filter SQL syntax.

## Can not determine type of SQL query string.

## This attack can be perform on anywhere of your application, even attacker don’t have to get a privileged account.

## Attack can be performing via cookie.

## Those dynamic pages which only pulls data from database directly.

## This vulnerability may be causes by your database server also.

***Types of SQL injection:

There are two types of SQL injection.

1.Injection into the variables that contain strings.

2.Injection into numeric variables.

Both are different than each other. I will describe about both and also how to avoid them.

*** 1. Injection into the variables that contain strings.

## Vulnerable code:

OK, just imagine that you wrote an application that have a page that fetches member’s credits according to his login name. The username will gone one page to another via URL, actually method of $_ GET [‘variable’];

$cred = $_GET['name'];

$result = mysql_query("SELECT credit FROM user WHERE username='$cred'");

This script has SQL injection vulnerability. An attacker can abuse it, just putting a SQL query beside the username in URL.

‘ UNION SELECT password FROM admin WHERE id=1

This will pull password of an admin user who has id number 1 as an example.

## Solution:

To avoid this type of attack is very simple. So many times it has been described by so many security experts. You have to use a function mysql_real_escape_string().What does it do? mysql_real_escape_string() function adds “\”  to following characters:

NULL, \ x00, \ n, \ r, \, ', " and \ X1A

So, look out that vulnerable code with mysql_real_escape_string() .

//

$cred = mysql_real_escape_string( $_GET['name']);

$result = mysql_query("SELECT credit FROM user WHERE username='$cred'");

This page is completely secure now.

## Attack again:

Now an attacker will attack on it like before.

‘ UNION SELECT password FROM admin WHERE id=1

But after using  mysql_real_escape_string() funtion, this query looks like this.

\’ UNION SELECT password FROM admin WHERE  id=1

There is “\” character just added because of mysql_real_escape_string().

Somebody will advice you to use addslashes() function. But this function has some limitations.

##Please read these topics from PHP manual:

http://www.php.net/manual/en/function.addslashes.php

and

http://www.php.net/manual/en/mysqli.real-escape-string.php

and also.

http://www.php.net/manual/en/function.pg-escape-string.php

*** 2. Injection into numeric variables.

These types of SQL attacks are less know than first one. And not so well described by security experts. But it also comes with same disaster on your web applications.

## Vulnerable code:

Now time to describe this attack. Imagine again, you wrote a page that will display user’s credit according to his user id. The user id will gone one page to another via form posting, actually method of $_ POST [‘variable’];

$id = $_POST['id'];

$result = mysql_query("SELECT credit FROM user WHERE id=$id");

On this situation mysql_real_escape_string() will not protect you from SQL injection attack. Because now the $id variable is not quoted. This variable is containing numerical value. Now an attacker will use this type of SQL query.

2 UNION SELECT password FROM admin WHERE id=1

So, how could you protect web application from this attack? There are two things you can do.

1. You can change content to number, so that your variable will hold only numerical content.

2. You can check if the content is numerical then it may proceed to make a query.

## Solution:

To avoid this type of attack you can use these functions.

A. intval() function.

B. is_numeric() function.

Here I am describing about the benefit of intval() function.

//

//

$var='7ack3r'; //variable contain alphanumerical content

$var2=intval($var); // filtered by intval()

print $var2; // this will print only 7

Now you can change your code as like below.

$id = intval($_POST['id']);

$result = mysql_query("SELECT credit FROM user WHERE id=$id");

Yap, you have done your job. This will secure your web applications more than enough.

Now, I am going to describe the function name is_numeric().This function will check, is your variable contains a numerical content?

$id = $_POST['id'];

if (is_numeric($id))

{

$result = mysql_query("SELECT credit FROM user WHERE id=$id");

}

else

{

log_the_attack(); //this function will log all the information eg: ip, browser, time, date etc

echo "Ha ha ha, trying do something with me dude? You are logged honey";

}

function log_the_attack()

{

// bla bla bla

}

Here a question will arise that “What can I do now, what will use…. Intval() or is_numeric() ?”

Answer will be “Its up to you, intval() is easy and saves the coding time but is_numeric() may kills some time. But this will help you to keep all the attack logs”.

Please read details from here:

1. http://www.php.net/manual/en/function.is-numeric.php

2. http://www.php.net/manual/en/function.intval.php

*** Conclusions:

Finally I am going finish this article. If you follow these steps, your application will be secure.

## Verify data types of all non-string user inputs.

## Escape all string inputs.

## Keep track of security violation attempts.

## Limit SQL user rights.

## Take database backups.

## Double check your codes.

## Always keep updated your database server.

###################

I AM CONFESSING THAT, I AM BAD IN ENGLISH.

IF I WROTE ANY WRONG INFORMATION IN MY ARTICLE, PLEASE INFORM ME .

###################

You can follow any responses to this entry through the RSS feed. You can leave a response, or trackback from your own site.

Tagged attack, hack, php, Security, sql