Preventing malicious SQL injections

Tweet

12 March 2010 | Daniel Hensby | Security, DataObjects, Database | Supports v2.4, v2.3

3 6

PHP has a very shallow learning curve, it's free and anyone can have a go at making a website by following a few tutorials and implementing their experience with other languages. However, coding for the web can be a risky business, especially with dynamic websites that take some kind of user (or external) input and use that to get data from a database.

Old school websites will use an id to get a pages content, eg: www.example.com/index.php?id=3. This can lead to a few problems if the id is not sanitised before being added to an SQL query.

Sanitising Variables

Here is how it would work if calling /index?id=1

<?PHP $id = $_GET['id']; $sql = 'SELECT `Content` FROM `Page` WHERE ID = ' . $id; ... 

This leaves us in a very sticky situation. If someone were to put in a non-numeric id into the GET var or were to do something worse like '1; DROP TABLE `Page`;' then you will find yourself with a big mess to clean up.

SQL Injection

This is known as SQL Injection and is just one of the many security holes that people can get into. If a user submits data you should always make sure it is valid and sanitised.

Solving the problem

Validating your inputs is very simple and effective way of stopping this problem for the id example, if you are expecting a number, check it is by using the is_numeric function.

Sanitise your variables before they are put into the SQL query by using silverstripes Convert class. The Convert class converts strings from one format to another so that they can be included in different document types without security or parsing implications.

Here is how to deal with a typical situation in SilverStripe, when getting a DataObject from the DB depending on an actions URL:

	function getPageByIDParam() { 
	
		$action = Director::urlParam('ID'); // in this case it is a string 
		
		return DataObject::get('Page','URLSlug = "' . Convert::raw2sql($action) . '"',null,null,1); 
	} 

Now you wont be a victim of SQL injection.

Controller Actions

In SilverStripe, methods in the controller are called by using the action part of the URI (www.example.com/URLSegment/Action/ID/OtherID) and this can be bad for security as we don't really want people to be able to call any method via the action.

To stop users from being able to call any action, use the Controllers Static var $allowed_actions. Which is an array that lists the actions that can be called through the action parameter in the URL:

class Page_Controller extends ContentController { 
	static $allowed_actions = array(
		'SomeFunction',
		'AnotherFunction'
	);
}

More information

SilverStripe have great information on secruity:

http://doc.silverstripe.org/doku.php?id=secure-development

http://doc.silverstripe.org/doku.php?id=security