Matthew J. Kellett

Website Architect and Developer

Prevent SQL Injection

As we all know, the most secure sites contain no script or database connectivity or areas for user input but in an age where interactions and social media are very much at the forefront of technology we really can't stop providing this functionality.

In the previous article I covered off the basics around HTML Injection with a final premise that all user input should be santisised and not trusted. This article shows you how to take the necessary steps that you can take to prevent SQL attacks and allow for more interactions between your site and your visitors.

The Problem

In the past there have been several large attacks which could have been prevented had a couple of important steps been carried out. The example I will use throughout this article is an online newspaper site who through either a lack of knowledge or lack of skills was taken down by simple SQL injection.

This particular newspaper served articles via an online system by determining the article people want to view based on $_GET parameters in the URL, for example:

				http://www.newspaper-example.com/articles.php?article_id=3456

The way this works is that the article ID to be displayed is determined from the article_id passed through the URL, from a script perspective the SQL used to retrieve the article could look something like this:

$article_id = $_GET['article_id'];
$sql = "SELECT
	*
FROM
	articles
WHERE
	article_id = '" . $article_id . "'";
				

This might look familiar and if it does then conisder the following URL and how it might affect the SQL when the article_id is substituted into the above query:

				http://www.newspaper-example.com/articles.php?article_id=3456';SELECT * FROM articles WHERE 1 = '1

Can you see it? If not then let me show you, this URL would create the following SQL Query:

$article_id = $_GET['article_id'];
$sql = "SELECT
	*
FROM
	articles
WHERE
	article_id = '3456';SELECT * FROM articles WHERE 1 = '1'";
				

As you might have already guessed, this is creates two queries, the second of which will return all of the articles in the table.

Now, take this one step further and imagine you have a page that uses something like:

				http://www.newspaper-example.com/members?member_id=12321

Simply by altering the member_id parameter you could get all of the information about all of the users in the system, including email address and passwords. Hopefully you can see how this is bad.

Consider what would happen if the following URL was used in the above scenario:

				http://www.newspaper-example.com/articles.php?article_id=3456';DELETE FROM articles WHERE 1 = '1

The Solutions

Fortuantley there are several ways to prevent this type of attack:

  • Sanitise the input prior to injecting it into the query
  • Validate the ID before making use of it
  • Use other techniques to retrieve information from the database

Sanitising and Validating

There are several things we know about either the article id or the member id, i.e. they are both integers, so we should check for them smiley

$article_id = $_GET['article_id'];
if (is_int($article_id)) {
	$sql = "SELECT
		*
	FROM
		articles
	WHERE
		article_id = '" . mysql_real_escape_string($article_id) . '";
}
				

This would be the easiest way of ensuring the article ID is valid, but this might not help for the member ID because this would still allow you to retrieve the member details for an administrator.

By using the mysql function mysql_real_escape_string this will add backslashes to anything passed through that would cause issues when the SQL query is executed.

Using PDO to retrieve information

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features.

The biggest advantage to using PDO is the ability to bind values to other identifiers within SQL queries. Using this technique allows for more generic queries to be written and provides a level of confidence that queries are escaped properly prior to being executed.

Below is a simple example of how binding works can be seen below:

$query = "
	SELECT
		*
	FROM
		articles
	WHERE
		article_id = :article_id
";
$statement = $this->PDO->prepare($query);
$bind_identifier = ':';
$param_name = "article_id";
$param_value = 3456;
if (!$statement->bindValue($bind_identifier . $param_name, $param_value, PDO::PARAM_INT)) {
	throw new Exception('Binding of ' . $param_value . ' to '$bind_identifier . $param_name.' failed. QUERY:'.$query);
}
$statement->execute();
$data = array();
while ($data_row = $statement->fetch(PDO::FETCH_ASSOC)) {
	$data[] = $data_row;
}
				

In this example we make use of a colon to determine where in a query the bind operations need to take place, i.e. :article_id.

Preparing and executing queries in this manner increases the confidence that SQL attacks are being prevented when you handle the results from the query being executed.

Conclusion

This concludes this particluar article but remember regardless of how your queries are built, you should always validate and sanitise values before executing and returning the results of the query.

This is only the second article in this series so keep an eye out for the others as they are published, if you have any comments or additions that need to be made to this article then feel free to leave me a comment below grin

Coding for Security - HTML Injection

Prevent XSS Attacks

Comments

There are no comments for this article

Like, follow, share us

Coding For Security

Below are the articles in this coding for security series.