Of PHP Applications, Security, and Modern Database Concepts - Part 1
Here we are again with another geek entry in the weblog, photography stuff will be back soon 
If PHP or Databases is not your thing, please tune out now.
It’s come to my attention that there’s a significant amount of PHP/MySQL (why I focus on MySQL will become apparent as the post unfolds) application code out there that far more complex than need be. This is partly due to an insistence about backwards compatibility; i.e. I still have 2 users of my application that are using MySQL 3.21, or simply because there are many part-time developers out there that don’t feel that learning about how real databases can improve their productivity is important, because they can squeeze all the functionality we need out of PHP. So hopefully this will be a polite wake-up call to those of you that fall into one of the previous categories, to help you see why having a full understanding of database concepts is not only a “good thing”, it could help you with writing faster code, easier to maintain code, whilst maintaining data integrity, and improving overall security of your application.
I originally wrote this post as a single post, which started to look more like an essay the further along I got. So I’ve split it into multiple posts simply to make it easier to read and digest.
Now the reason I’m talking about MySQL primarily here is something of a history lesson. Love it or hate it, MySQL started out life as a relatively lightweight database, it sacrificed some of the more traditional database features and functionality, for gains in speed, and agility. The downside is that if you were developing real applications with real data, some of the work around methods to regain or replicate some of the missing functionality was quite painful involving some serious incantations of SELECT spaghetti!!. The reason I’m not talking about PostgreSQL, Oracle, Sybase, or any of these other database products, is that anyone developing on top of them without the legacy MySQL baggage, really should know better!! Database abstraction layers are all fine and dandy, but really, what’s the point of making your app work with Oracle if it’s never going to exploit the advantages offered by that particular platform….. anyway… I digress..
The problem is that there are a lot of PHP or <insert favourite web scripting language here> apps out there that have their roots in the early MySQL era and have suffered sprawl and organic growth, don’t get me wrong this is a good thing, it just brings a bunch of baggage with it. You see over the last few years MySQL has made advances in leaps and bounds, it’s barely a shadow of the young upstart database that it once was, however many owners and developers of legacy applications have simply been too busy keeping on top of refactoring and recoding for the base version changes in PHP and MySQL releases to allow the existing code to work with MySQL both old and new. This means that many of the longest running most popular applications are almost certainly running with MySQL spaghetti SQL under the bonnet. Even many of the apps with extensive Web 2.0 and Ajax and other front-end bling, fall into this category. I would imagine that if you think of the top 3 most popular applications in the categories of Weblog, eCommerce Store, CMS, CRM or other mainstream apps, if they’ve come from a MySQL legacy background, that 2 out of 3 (or in some cases 3 out of 3) are running with Spaghetti SQL. In my next post I’ll talk about some of the ways that the features MySQL has added in the last 5 years can help to improve your applications. None of this is rocket science and I’m no database expert, so any DBA’s or Database Guru’s reading this by all means read and correct me where required, but don’t expect to get any useful info from me, I’m mostly harmless in regard to databases (and PHP for that matter!).
And if you still have 0.5% of your users that run MySQL 3.23 or older on their servers, which means that they can’t use a newer version of the application, then I suggest that you point them at an application with similar functionality that has ceased development, then they’ll be able to carry on working with that, whilst editing the files with ed, and viewing the html with lynx, reading their emails with elm, and before I get lambasted, I realise that there is a time and place for these apps and they are still valid, but NOT all the time in everyplace (oh and vi is so much better than emacs too!!).
We’ll start to dive into the detail in the next post, which will cover Triggers.
