Keeping Joomla! 1.5 Extensions Secure

Front Cover of Joomla! 1.5 Development Cookbook

The following is a recipe extract from Joomla! 1.5 Development Cookbook. This recipe is taken from the second chapter in the book, Keeping Extensions Secure.

Writing SQL safe queries

SQL injection is probably the most high profile of all malicious web attacks. The effects of an SQL injection attack can be devastating and wide ranging. Whereas some of the more strategic attacks may simply be aimed at gaining access, others may intend on bringing about total disruption and even destruction. Some of the most prestigious organizations in the world have found themselves dealing with the effects of SQL injection attacks. For example, in August 2007 the United Nations web site was defaced as a result of an SQL injection vulnerability. More information can be found at http://news.bbc.co.uk/1/hi/technology/6943385.stm.

Dealing with the effects of an SQL injection attack is one thing, but preventing them is quite another. This recipe explains how we can ensure that our queries are safe from attack by utilizing the Joomla! framework. For more information about SQL injection, refer to CWE-89.

Getting ready

The first thing we need is the database handler. There is nothing special here, just the usual Joomla! code as follows:

	$db =& JFactory::getDBO();

How to do it...

There are two aspects of a query that require special attention:

  • Identifiers and names
  • Literal values

The JDatabase::nameQuote() method is used to safely represent identifiers and names. We will start with an easy example, a name that consists of a single identifier.

	$name = $db->nameQuote('columnIdentifier');

We must take care when dealing with multiple-part names (that is, names that include more than one identifier separated by a period). If we attempt to do the same thing with the name tableIdentifier.columnIdentifier, we won't get the expected result! Instead, we would have to do the following:

	// prepare identifiers
	$tableIdentifier = $db->nameQuote('tableIdentifier');
	$columnIdentifier = $db->nameQuote('columnIdentifier');
	// create name
	$name = "$tableIdentifier.$columnIdentifier";

Avoid hardcoding encapsulation

Instead of using the JDatabase::nameQuote() method, it can be tempting to do this: $sql = 'SELECT * FROM `#__foobar_groups` AS `group`'. This is OK as it works. But the query is now tightly coupled with the database system, making it difficult to employ an alternative database system.

Now we will take a look at how to deal with literal values. Let's start with strings. In MySQL, strings are encapsulated in double or single quotes. This makes the process of dealing with strings seem extremely simple. Unfortunately, this would be an oversight. Strings can contain any character, including the type of quotes we use to encapsulate them. Therefore, it is also necessary to escape strings. We do all of this using the JDatabase::Quote() method as follows:

	$tableIdentifier = $db->nameQuote('tableIdentifier');
	$columnIdentifier = $db->nameQuote('columnIdentifier');
	$sql = "SELECT * FROM $tableIdentifier "
	. "WHERE $columnIdentifier "
	. ' = ' . $db->Quote("How's the recipe\book going?");

The JDatabase::Quote() method essentially does the following. The exact output will depend on the database handler. However, most databases escape and encapsulate strings in pretty much the same way.

Original Quoted
How's the recipe\book going? 'How\'s the recipe\\book going?'

Dealing with the LIKE clauses requires slightly different string handling. For more information, refer to the next recipe, Writing SQL safe LIKE string comparison queries.

The other type of literal value we often use in the queries is numbers. In MySQL, there are two types of literal numbers - integers (whole numbers) and floats (decimal numbers). The following examples show how we can cast unsafe values or use the PHP *val() functions to make these values safe for use in a query:

	// integer
	$safeNumber = (int)$unsafeValue;
	$safeNumber = intval($unsafeValue);
	// floating-point

	$safeNumber = (float)$unsafeValue;
	$safeNumber = floatval($unsafeValue);

In most instances, $unsafeValue will have been extracted from the request data; for example, index.php?option=com_foobar&int=unsafeInt&flt=unsafeFlt. In these instances, we can use JRequest to do the work for us as follows:

	// integer
	$safeInt = JRequest::getInt('int');
	$safeInt = JRequest::getVar('int', 0, 'DEFAULT', 'INT');
	$safeInt = JRequest::getVar('int', 0, 'DEFAULT', 'INTEGER');

	// floating-point
	$safeFlt = JRequest::getFloat('float');
	$safeFlt = JRequest::getVar('float', 0, 'DEFAULT', 'FLOAT');
	$safeFlt = JRequest::getVar('float', 0, 'DEFAULT', 'DOUBLE');

For more information about using JRequest and the various methods shown above, refer to the Safely retrieving request data recipe, later in the chapter.

The final and the most complex option is to use JFilterInput. This class allows us to use the same sort of principles as with JRequest as shown here:

	// get filter instance
	$filter = JFilterInput::getInstance();

	// integer
	$safeInt = $filter->clean($unsafeValue, 'INT');
	$safeInt = $filter->clean($unsafeValue, 'INTEGER');
	// floating-point

	$safeFlt = $filter->clean($unsafeValue, 'FLOAT');
	$safeFlt = $filter->clean($unsafeValue, 'DOUBLE');

Quoting numbers

As an extra line of defense, we can also treat a number as a string. For example, we could use $db->Quote((int)$unsafeValue). Although numbers do not require encapsulation, it is acceptable to quote a number.

How it works...

An identifier identifies a database, table, or a column. A literal value is an expression that cannot be broken down any further. Therefore, they are literally equal to themselves, for example 1 == 1. To make identifiers and literal values safe, we encapsulate them in special characters defined by the server. For example, when we are dealing with MySQL identifiers, we encapsulate the identifiers in grave accents such as `identifier`. Of course, we don't need to know this because JDatabase deals with this for us!

A name consists of one or more identifiers separated by a period. Names that contain more than one identifier are known as multiple-part names. Multiple-part names provide the ability to drill down, for example myTable.myColumn. The JDatabase::nameQuote() method cannot handle multiple-part names. Hence, each identifier in a name must be handled separately.

In most database systems, encapsulating identifiers is not technically required, and MySQL is no exception. However, there are occasions when failure to do so will prevent a query from working. SQL has reserved keywords. If any of our identifiers are also reserved words, we must encapsulate them. This tends to be especially noticeable when using aliases. For example, SELECT * FROM #__foobar_groups AS group will fail because group is a keyword. We can easily overcome this in Joomla! as follows:

	$sql = 'SELECT * FROM ' . $db->nameQuote('#__foobar_groups')
	. ' AS ' . $db->nameQuote('group');

There's more...

In MySQL, there are six different types of literal values. Absent from this list are date and time. This is because the date and time values are expressed as strings. For example, November 2nd, 1815 would be expressed as the literal value '1815-11-02'. The six types of literal values are as follows:

  • String
  • Number
  • NULL
  • Hexadecimal
  • Boolean
  • Bit field

The type of the value makes a difference as to how we should handle it. We have already addressed strings and numbers. The following subsections describe how to safely handle the remaining literal types.

NULL

A NULL value represents the absence of data. This is not the same as an empty value. For example, a string with no characters is not a NULL value. NULL values should always be written as NULL or \N. We should never use raw input when we express a NULL value in a query.

	$safeValue = ($unsafeValue == 'NULL') ? 'NULL' : 'NOT NULL';

Hexadecimal

It is unusual to use hexadecimal literal values in Joomla! extensions. It is no surprise that there are no special tricks for dealing with hexadecimal values in the Joomla! framework. An example of when we might want to use hexadecimal is recording colors such as red, FF0000. The following example shows how we can sanitize some hexadecimal data. There are three normal ways of representing hexadecimal data - X'value', x'value', and 0xvalue. The following example uses the standard SQL notation, x'value':

	$matches = array();
	$pattern = "~^([Xx]\'|0x)([0-9A-F]+)\'?$~";
	$safeHex = $defaultSafeHexValue;
	if (preg_match($pattern, $unsafeValue, $matches)) {
		$safeHex = "x'" . $matches[2] . "'";
	}

Boolean

Boolean values are very straightforward. They are represented as the raw strings TRUE and FALSE. We should always use a PHP expression to determine a Boolean value.

	$boolean = ($unsafeValue) ? 'TRUE' : 'FALSE' ;

Bit field

Binary values are also unusual in Joomla! extensions. Values of this type can be useful for storing bit patterns (essentially flags). Again, there are no special tricks for dealing with these types of literal values. To overcome this we can improvise, as shown in the following example. There are two ways of representing binary values - b'value' and 0bvalue:

	$matches = array();
	$pattern = "~^(b\'|0b)([01]+)\'?$~";
	$safeBin = $defaultSafeBinValue;
	if (preg_match($pattern, $unsafeValue, $matches)) {
		$safeBin = "b'" . $matches[2] . "'";
	}

Binary representation is only available in MySQL from version 5.0.3 onwards.

Find more great Joomla! recipes

Buy a copy of Joomla! 1.5 Development Cookbook

book-j15-cookbookGet solutions to common Joomla! 1.5 Development problems, to make your extension development better, faster and more secure. The author's experience with Joomla! 1.5 development enables him to share his insights effectively, in a clear and friendly way, giving practical hands-on solutions to problems, questions, and common tasks encountered in the design and implementation of Joomla! 1.5 extensions.

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Share on RedditEmail this to someone
Posted in Joomla!