Prevent Sql Injection Attacks with Prepared Statements

Jun

10th

Prevent Sql Injection Attacks with Prepared Statements

Author: admin | Files under php, tutorials

What is sql injection?

Recently my web sites got hacked and believe me, every database file and others on my server are deleted by the hacker. After serious search of reason I identified the threat as, sql injection.

One good think I done is, logging the sql queries into a file for auditing and debugging. After Walking thru the files I came across a line like this:
Recently my web sites got hacked and believe me, every database file and others on my server are deleted by the hacker. After serious search of reason I identified the threat as, sql injection.

One good think I done is, logging the sql queries into a file for auditing and debugging. After Walking thru the files I came across a line like this:

……. ‘exec master..xp_cmdshell ‘del *.* /Q’ – ………….

See the line it executes shell command to delete all the files.

This is called sql injection. After a detailed study of sql injection methods and its prevention techniques, I practiced them and now, my web site is running good and secure.

I taken time to write these tutorials to help the needed one and prevent sql injection type of hacking, any more.

SQL injection can be defined in a simple way as follows:

“It is trick of adding the malicious code to the sql query, so that, hacker can retrieve your data, insert, delete and can do anything with your database.”

For example:

<?php
$query = "SELECT * FROM users_table WHERE username=’{$_GET['username']}’ AND password=’{$_GET['password']}’";
mysql_query($query, $link);
if (mysql_affected_rows($link) > 0) {
echo "you are logged in successfully\n";
}
?>

URL:

../Login.php?username=admin&password=xyz

The intention of the above code snippet is to check whether the username (admin) and password (xyz) is matching, if so, the user is logged in to the web site.

But, consider the following URL which affected with sql injection:

../Login.php?username=admin&password=z%20or%201=1—-

Look at the URL carefully, we got like this:

SELECT * FROM users_table WHERE username=admin AND password = ‘z’ OR 1=1 –

So, the hacker can login now as user admin, whether the password is correct or not. Because, the OR statements always return true.

It is simple right! But, giving these kind chances to hackers will bring disaster to the web site.

Why prepared statements

sssTo know why we have to use prepared statements lets consider its alternatives first. Its alternatives and which prevents sql injection are for example,

Alternative 1:

Magic quotes, PHP supports these kinds of input escape mechanisms like magic_quotes_gpc. If enabled, magic_quotes_gpc, adds backslashes in front of single-quotes, double-quotes, and other characters that could be used to break out of a value identifier.

But, magic quotes is a generic solution that doesn’t include all of the characters that require escaping, and the feature isn’t always enabled (it is deprecated in current PHP versions ). Ultimately, it’s up to you to implement safeguards to protect against SQL injection.

Note: Ofcourse, magic quotes are not at all the comparative, but, I taken this for theory sake.

Alternative 2:

Another good alternative is using the sql server extension functions like mysql_real_escape_string, this function is a mysql extension for the PHP to escape input characters that are special to mysql and it also prevents data corruption by taking into account the current character set of the connection. For example, if GBK character set is being used, it will not convert an invalid multibyte sequence 0xbf27 (¿’) into 0xbf5c27.

<?php
$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)
OR die(mysql_error());
$query = sprintf("SELECT * FROM users_table WHERE username=’%s’ ",
mysql_real_escape_string($username,$link));
mysql_query($query);
?>

There are two problems with this kind of sql server extension functions:

All the Databases doesn’t have these kind of extension functions. At the moment only MySQL, PostgreSQL, SQLite, Sybase, and MaxDB extensions provide them. For other databases, including Oracle, Microsoft SQL Server, and others, an alternate solution is required.
It is proved that, in all the scenarios the current character set of the mysql connection is not reflected or not used by the mysql_real_escape_string. Function, for example:
1. SET CHARACTER SET ‘GBK’ – query
These queries can be executed with out the admin level privileges also but, it will not let the escaping facilities know about the character set change. Thus, escape extension functions are faced failure scenarios also.

From the above, we can say that, we have to search for some other option which solves the sql injection problem. The prefect option is the prepared statements or prepared queries.

Prepared statements have many advantages over the others like, it forces type checking of the data before embedded them into query and other important thing is, prepared queries improve performance by executing the precompiled queries.

Explaining prepared statements with example

Prepared statements are used by / supported by major of the programming languages and as well major of databases, i will try show some of them as follows:

Java and JDBC API example

PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS_TABLE WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, “james”);
prep.setString(2, “myth”);

The syntax is straightforward: just insert question marks for any parameters that you’ll be substituting before you send the SQL to the database. Then, call the setstring with the parameter number and corresponding value.

Using prepared statements with C#

using (SqlCommand myCommand = new SqlCommand("SELECT * FROM USERS WHERE USERNAME=@username AND PASSWORD=HASHBYTES(’SHA1′, @password)", myConnection))
{
myCommand.Parameters.AddWithValue("@username", username);
myCommand.Parameters.AddWithValue("@password", password);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader())
……………….
}

In PHP, using PDO Database layer

$db = new PDO(‘pgsql:dbname=database_name’);
$stmt = $db->prepare("SELECT age,sex FROM users_table WHERE username=:username AND password=:password");
$stmt->bindParam(‘:username’, $username);
$stmt->bindParam(‘:password’, $password);
$stmt->execute();

In PHP, using the mysqli extension

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT age, sex FROM users_table WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

High On MicroSoft