SSbits - Home page
Site by Carbon Crayon
Submit a Post >

Snippets - Little bits of code to make you happy

Preventing malicious SQL injections


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: 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 ( 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(

More information

SilverStripe have great information on secruity:

Daniel Hensby avatar

Daniel Hensby

Dan runs a website development company called Better Brief. Better Brief specialise in SilverStripe websites and takes an active role in the UK SilverStripe community, attending SS Meetups and even presenting at them.

  • Hamish Campbell
    13/10/2010 9:19pm (7 years ago)

    A handy convention - from the SilverStripe core and elsewhere - suffix variables that you might use for database lookups with with "_RAW" (pre-converted) or "_SQL" (SQL safe) to remind you what is safe to use and what isn't.
    $id_RAW = Director::urlParam('ID');
    $id_SQL = Convert::raw2sql($id_RAW);

    Casting to an expected data type can be a convenient short cut. Ie, this is a convenient short cut for getting objects by ID (since an ID of zero will never find an object):

    $obj = DataObject::get_by_id("SomeObject", (int)Director::urlParam('ID'));

  • Don St. Troy
    19/01/2011 1:55am (6 years ago)

    Hey, I found this graphic being used on a related article on Smashing Magazine. Check it out @

  • Daniel Hensby
    19/01/2011 9:49am (6 years ago)

    @hamish: Yes, that's a nice convention, but personally I don't like holding variables I'm not going to use. So if the raw var is solely used to be converted to a safe var, then I dont keep it.

    @greg: This graphic isn't mine or SSBits'. Its a very well known comic from XKCD: (which really should be credited), so here is the credit!

Post a comment ...

You cannot post comments until you have logged in. Login Here.


Site of the Month

Find SSbits on

Top Contributers

Rank Avatar Name
1 article image Aram Balakjian
2 article image wfacewomens
3 article image Daniel Hensby
4 article image Marcus Dalgren
5 article image ryduhartchas
6 article image Hamish Campbell
7 article image Ty Barho
8 article image Martijn van Nieuwenhoven
9 article image Darren-Lee
10 article image Roman Schmid

View full leaderboard