Programming: December 2006 Archives

This guy's post about returning to his original focus (which would be Perl, Python, and PHP) reminded me of a post I had long intended to publish. It's (briefly) titled this: "How to correctly write PHP code in conjunction with databases." Long title, but I can't think of anything shorter.

Foreward: My database of choice would be MySQL (again, it's a PHP focused post). Note, however, that anything I mention here is easily applicable to any database. I have done developement on PGSQL and even, gasp, MSSQL, and likewise I'm quite confident that everything I mention here as far as databases go should apply to pretty much anything.

PHP is probably the most used language on the web. Why? It is fairly easy to pick up, and has a huge amount of support on pretty much any server anywhere, never mind the monstrosities of pre-existing PHP scripts you can download from... pretty much everywhere. Forums, download sites, e-commerce sites, you name it, you can probably find a PHP script doing it. However, as a result, it's one of the most commonly attacked languages out there, and while it is incredibly easy to pick up, it's not as easy to write PHP properly and in a secure manner.

Uh-oh, I wrote "PHP" and "secure" in the same sentence. That's a bad thing. PHP has recently recieved a fair amount of flak for it's security, or lack thereof. One of the lead programmers for PHP whose sole purpose was really just to make PHP more secure, stepped down. This article is a good read on the matter, for anyone curious. From what I gather, he stepped down for one main reason, with another one on the side. The main reason would be the response time to security holes. The side reason would be a view that PHP was built insecure from the ground up, and try as he might, that wasn't going to change.

That debate really comes down to "it's the programmer's fault for coding that way" vs. "you shouldn't even allow that to happen, ever." And it's true: improperly coded PHP is a complete nightmare, riddled with security holes, both in code and in function, and it turns into a complete mess quickly.

Now, let's avoid all of that, shall we?

Point #1: Check all of your data for validity.

Now, this is no small order. PHP is typically used to take user input, process it, and then output it. Right there, you have three places a malicious user can attack your code: the input, the processing/storage of the data, and finally, the output.

The input is one of the most commonly checked and parsed things, and, in my opinion, wrongly so. Assume, for a moment, that everyone everywhere is a non-malicious user. Why shouldn't they be allowed to have a username of "admin' or 1=1--"? Sure, it's a little weird, but then again, so is the internet as a whole. End result, most people code to strip out less commonly used characters. Others call htmlspecialchars(), and other flat-out deny query execution when some special characters are detected. Oh, and then you have the swarm of mysql_real_escape_string() people, and as a result, every other line will be mysql_real_escape_string().

Now, admittedly, the last bit there (mysql_real_escape_string()) is a real and valid solution to the bigger problem of SQL injection. But, it is not optimal. Optimal would be using SQL statements in combination with stored procedures. Currently, only MySQL is supported directly with stored procedures, by ways of the mysqli functions. However, PHP's PDO project is coming along very nicely, and with that you can use SQL statements with pretty much any database out there. SQL statments are, to put it simply, a way of using SQL queries in a manner that guarantees you will never be vulnerable to the most common type of SQL injection attacks (which is generally input manipulation).

Now, before you touch the database (I know, I'm out of order), you can do other sanity checks on your data. Take for instance, the ever common URL, http://example/page.php?id=1. It's that ?id=1 part that is common, and further, non-specific to PHP. My question to you is this: why is it that you have countless webpages where ?id=1 is a security hole? You can make your data checks really, really easy here: is_int(). Really, think about it. You can completly skip all forms of data validation in this example, by checking the results of $_GET["id"] with is_int(). Is there ANY reason why $_GET["id"] should be anything but a number? No? So then why are you spending so much time checking for odd characters that shouldn't be there, when you could merely check the datatype and know for sure that it's legit?

This can also easily apply to strings, or to be more defined, UUIDs (On that page, search for "UUID()", it's roughly 2/3 of the way down). If you know that a UUID will always be in theaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format, you could use a regular expression to check that easily (not to mention to check that it only contains a-Z and 0-9 chars).
Point #2: Proper database security and design

If you've ever installed a PHP script that used SQL, it asked you for a login to a SQL server of your picking. In my opinion, it should ask you for two seperate logins. "Why?" That's an easy answer.

As it stands now, any SQL username and password you provide to a script, generally has complete reign over the database. SELECT, UPDATE, DELETE, the works. This poses the question: Why should someone viewing a page (not editing, adding, removing, etc.) even have the rights to turn a query around and run a DELETE? Does that even make sense? I sure hope not.

The solution to the problem is this: have two users, with differing rights on the SQL server itself. The first user should really only have SELECT, the ability to run a stored procedure, check out a view or two, and... that's it. Even at that, stored procedures that only execute SELECT. I can't name one reason why an anonymous page view should use a connection to the SQL server with enough rights to run an UPDATE. It just makes no logical sense. The second user, however, still shouldn't have UPDATE rights: rather, it should only have the ability to execute stored procedures. Stored procedures protect your data, and can sanitize code everywhere. Again: there is no reason why an anonymous page view should even have the rights, let alone ability, to run an UPDATE or a DELETE query. So, um, please stop allowing such things. It will make the world a nicer place.

Point #3: Taking point #2 a step further, and seperating the code that writes from the code that reads.

PHP 5.0 introduced a lot of very useful object oriented capabilities. Take for example, a mock forum, with the following functions: $forum->view->thread(), $forum->update->thread(). Again, there is no reason at all why view->thread() should be calling update->thread(). Functions and classes can be marked as private, protected, and public: please do so. Before someone goes off and states "but end users can't execute PHP code that I didn't write!", think again. It can happen. It has happened before. It is preventable. Plus, this makes your code cleaner and enforces good coding habits all around.

Point #4: Check your SQL output

As yes, the ever popular login string of "admin' or 1=1--". Commonly used is poorly coded web scripts to gain a login as the user "admin", you can simply google around for this and see countless example of it pretty much everywhere.

But there's a bigger problem with this. Let's assume for a moment that someone used this on a poorly coded website and succeded with a login as user "admin." The real problem? The "or 1=1" bit. You just selected every single user in the database. Here's yet another sanity check, useful for such things: *_num_rows(). This is an incredibly simple check all around: if you're trying to log in one user, and you get four rows back, you know something somewhere is wrong. So.... why do you allow it?

All of the above can really come down to two main points: data validation, and not allowing the database rights, but rather abstracting the rights into stored procedures. Data validation is usually taken too far in the wrong direction (stripping characters, not allowing query execution), while database rights are completly overlooked. All that data validation should be, is simple sanity checks (like is_int() and *_num_rows()). Used in combination with SQL statments and stored procedures (with limited database access on top), you'll quickly come to see just how secure PHP can really be.

The biggest problem with PHP out there currently? People try to secure their scripts, and then they do it incorrectly. "But I stripped out all of the backslashes!..."