Simple Machines Blogs > Developers' Blog

Database Changes in SMF 2.0 - Part 2

(1/15) > >>

Grudge:
Righty,

Thought that with a few minutes time on my hand I’d make a bit of a post about the recent changes we’ve made to 2.0 – specifically to the database engine (99% of the forum population may wish to turn away now to save themselves five minutes of boredom).

Right, I’ll start with the motivation behind this. There are two real motivations, one of them is security and the other improved abstraction. In my mind security has been the real driver for this and as such I’ll explain why. You see, there are two main ways of someone “exploiting” web based software (Or certainly we’ve had no others ever affect SMF), they are:
1) SQL Injection attacks – where through some means someone is able to run arbitrary SQL commands on your box, revealing passwords, destroying data etc.
2) XSS (Cross Site Scripting) – where through injection of javascript (Or otherwise) a user may be able to steal your session and “take over” your account.

SMF obviously has always protected against both of these and continues to do so. Whilst both can have major implications in general SQL attacks are the ones that, in my mind, are the cause for most concern. So, how did SMF used to protect against these, well – in two ways:
1) Strings are “escaped” (the means by which variables are made safe) before they are inserted into a database. As the normal source for an injection is malicious data coming into the script from a URL/Form etc SMF used to take the approach of “escape everything” whereby anything coming into the script was made safe – and we’d only make it “dangerous” if we needed to do some manipulation of it – and would make it safe again afterwards.
2) The database query function did some additional checks to ensure if someone *did* manage to exploit SMF that they were limited into the extent of the damage they could cause. This is what I’d call the “second line” of defence – or damage limitation.

When developing 2.0 we’d started to become concerned about our approach to the first of these protection mechanisms. SMF 2.0 has generally become cleverer, more complex. We do more things with incoming variables before we put them in the database and in general that meant making them dangerous more and more often. Every time you make them “dangerous” there is a risk that you forget to make them safe again. We decided that this approach was no longer appropriate as it was beginning to increase as opposed to decrease the security risks in 2.0. So – like all good developers – we changed it.

Compuart, the clever chap that he is, came up with a new scheme for doing this security. The problem with our old security model was all the security was on the “gates”. Once something was “in” SMF it was assumed to be secure – we wanted to change this. Now, instead of the security being on the gates it stands directly in front of the database itself. You can no longer talk to the database without doing through Bertie the Bouncer (Or whatever he is called). To get something into (Or for that matter out of) the database you need to tell it exactly what it is you’re doing – and he’ll make it secure for you.

So, now if I want to set my name to ‘Grudge – King of the Newts’ when I send it to the database I also tell it ‘It’s a string’. If it’s not a string security won’t let it if. Also, security – knowing how dangerous strings are – will clean it up before it gives it to the database. The point is that we can do whatever we want to variables within SMF – I can feel with all kinds of dangerous things – but when it comes to feeding it into the database it takes care of security there and then. Now, I’ll move on from the silly examples and go into a it of practical stuff – those who vomit at the site of a it of PHP best look away now.

So, here’s the old way to update my name: (If you’re confused by the $smfFunc stuff see one of my previous blogs on databases)

--- Code: ---$smfFunc[‘db_query’](‘’, “
UPDATE {$db_prefix}members
SET member_name = ‘grudge’”, __FILE__, __LINE__);

--- End code ---

Now, things would look quite different:

--- Code: ---$smfFunc[‘db_query’](‘’, ‘
UPDATE {db_prefix}members
SET member_name = {string:name}’,
array(
‘name’ => ‘grudge’,
)
);

--- End code ---

So, let’s look at things bit by bit:
1)   __FILE__ and __LINE__ are gone. We know use backtracing functions in the case of an error. It doesn’t work properly on very old versions of PHP so debugging won’t be quite so hot on 4.3.1 but we thought that they are in the absolute majority and the new method makes queries much cleaner.
2)   No single quotes (‘) within the query. In fact, if I were to put a single quote in the query – anywhere – you’d get a big error message.
3)   Instead the place the string is meant to go has an inject name which references the key of the array passed as the next parameter. When the query is processed every item in the array is reinserted into the query before being passed to the database. Variables are cast and cleaned as required. This means if you inserted an int it will be cast as an int. If you insert a string it will be escaped and surrounded by quotes.

Another examples:

--- Code: ---$new_location = “England’s Home Land”;
$smfFunc[‘db_query’](‘’, ‘
UPDATE {db_prefix}members
SET location = {string:new_location}
WHERE id_member IN ({array_int:my_friends})’,
array(
‘new_location’ => $new_location,
‘my_friends’ => array(3,6,7,3),
)
);

--- End code ---

As for the first example, the string $new_location is tested to be a string, escaped, surrounded with quotes and fills the gap of {string:new_location}. Meanwhile the other type array_int looks at the array element of my_friends, checks it’s an array, check’s all it’s contents are ints, then explodes into a list of numbers into the query.

Of course it’s much easier to see this by actually viewing the code in real examples but I’m hoping this will help. So, how will this change impact people?

Well – for “normal” users – you won’t see a thing. Everything will work as usual – but you should benefit from adding security that will (hopefully) also improve the security of mods as mod authors now can rely on SMF for their database security and not have to think too much about their own protection for SQL issues.

For “mod” users things aren’t too much of a problem. They simply remove (I suspect) all calls to escaping functions like addslashes etc that they currently use. They need to rewrite their queries (In addition all inserts should now use a new function db_insert but I’ve spoken about that before) – but it’s not *that* much work as all mods need to be rewritten for 2.0. Besides which – even though Compuart wrote a great tool for converting the main SMF files I still spent about 6 days over Christmas checking, and in many cases rewriting, every query in SMF so no-one can moan to me about the length of the task as I’ve done it first hand ;)

The main people this will impact is people who integrate SMF deeply within their site. People who use SMF for integrations (For example including SSI.php) and then doing their own queries need to be aware that variables like $_POST and $_GET are no longer escaped by SMF. This means if you previously had this code.

--- Code: ---$_POST[‘test’] = ‘test\’s’;
require_once(‘SSI.php’);
mysql_query(“update smf_members SET member_name = $_POST[test]”);

--- End code ---

In the past that would have worked. Now that will create a very dangerous security hole. Therefore anyone using SMF for an integration needs to make one of three decisions, either:
1)   Convert their queries to use the SMF query function (Highly recommended)
2)   Escape $_GET, $_POST type variables individually before they are used in their queries
3)   Escape all $_GET, $_POST type variables straight after including SSI.php by doing something like:

--- Code: ---require_once(‘SSI.php’);
$_GET = escape__recursive($_GET);
… etc for $_POST

--- End code ---

I really wouldn’t however recommend doing this. Certainly if you ever use any SMF functions *after* the latter example there is a risk that data will be “double escaped” resulting in lots of slashes appearing in posts, member names etc unintentionally.

There are a few more things about the system but I’ve covered off the bulk of it here. I very much believe that these changes will benefit everyone, particularly users who should always be looking for better and better security. We should be upgrading our site here this weekend so I’ll soon be able to report first hand how easy or otherwise converting an integration is ;)

Grudge

Runic:
i cant believe i read that, i cant also believe i understood over 50% of it, im curious when smf2 becomes stable will the ssi help pages be updated to work with the new db  strings.

karlbenson:
A very good explanatory post.
I see can it taking a bit of time to get used to, but overall it seems very positive.

LMAO, Bertie the Bouncer.

Thantos:
Earl > Bertie :P

metallica48423:
are the types the same as php's resource types, or is there a list of them available?

Navigation

[0] Message Index

[#] Next page