The Back Burner

The goal for students on the intermediate PHP and MySQL course that I’m running for IWA/HWG is to create a working shopping cart. The course starts in only three days, and I decided I’d better walk the walk and prove I could do it. So I have quickly mocked up a working version of the cart they are expected to build and put it online here. I think it will be helpful to students to see what they are aiming at, and it will make it easier for them to work out the logic if they can click through a real example. The design may not win any prizes, but the basics are all there.

All course materials now written and double-checked, quiz questions written and checked, SQL scripts generated and tested … just need to put week one’s materials up now!

Since 1999 I’ve been teaching online for the Open University. It may not pay brilliantly, but it’s regular income and I really enjoy doing it; OU students often overcome major barriers to achieve a qualification they missed out on earlier in life, for whatever reason, so they can be very rewarding to teach. And the fact that it’s online means I can conveniently fit it around other work. Currently I teach three courses in the Certificate in Web Application Development.

I can now announce that I’m also going to start teaching an online course with eClasses.org, the online learning branch of IWA/HWG. The course uses PHP and MySQL to build a shopping cart, and is aimed at intermediate PHP developers (you don’t have to know anything about MySQL, but it certainly helps if you do). I’m really lookng forward to this; you can see the full course description here, and registration is now open.

It’s always a real headache manipulating dates between PHP and MySQL, because PHP’s date functions assume Unix timestamps, while MySQL has its own internal date format. They both have excellent built-in functions for handling dates, but they are fundamentally incompatible. I’ve torn my hair out before now trying to manipulate dates between the two, because pretty well every application I write needs to do date arithmetic.

Well, duh, I don’t know why I didn’t think of this before, but MySQL has a UNIX_TIMESTAMP function which sorts it all out for you by converting a MySQL date to a timestamp. Simon Willison explains it, so I don’t need to. So easy when you know how!

Want to add one (or more) days to a date in PHP? Timestamps are simply numbers of seconds, so it seems so easy and obvious to do this:
$mydate = (60*60*24)+$mydate;
or even just
$mydate = 86400+$mydate;
Don’t be tempted! Twice a year, because of daylight saving, the day doesn’t have 24 hours in it; it has 23 (spring) or 25 (autumn). So all of a sudden you are likely to find your code behaving in a manner which is downright weird. Been there, done that. The approved way is just as simple and far less likely to result in hair-tearing:
$mydate = strtotime("+1 day", $mydate);

Edit: here’s a function for calculating differences between times. Untested for resistance to DST, but could be useful in some situations. Ironically, the post he links to about calculating date difference more precisely in PHP (also untested) looks as if it does not take this problem into account. Go figure

Scriptaculous is one of the better-known Javascript/AJAX libraries, with useful built-in objects for adding Web 2.0 functionality to your pages. I’ve tinkered with it but hadn’t found a real-life application for it until I needed to allow a client to reorder a grid of thumbnail photos by dragging and dropping them. Added complication: she uses Safari.

Oh how I hate coding Javascript! I got something kind-of working, including an Ajax callback to update the database, but it wasn’t very satisfactory. So I put it aside for a while and looked at it again when I had some spare time. This time I came across Greg Neustaetter’s PHP wrapper class for Scriptaculous lists. Aha, the power of Scriptaculous without the pain of Javascript! With the aid of his well-documented examples it only took me about half an hour to create a proof-of-concept. So Web 2.0 here I come …

I’ve been developing websites with PHP for about 5 years now and I blush to confess that I have only just discovered I can use bind parameters with SQL, instead of painstakingly building up my SQL statements by concatenating strings, while remembering to deal appropriately with all potentially dangerous characters to avoid the risk of crashes or hacks.

Bind parameters are an unreservedly Good Thing. Not only do they make your SQL more readable, maintainable, and portable, but they also protect against the dreaded SQL injection which is the scourge of badly coded PHP websites developed by people who don’t know any better. And as if that wasn’t enough they can improve performance too.

I am no fan of reinventing the wheel, so since I first started using PHP I have used the open-source ADODB database abstraction library for database access. I like it because it’s fast and lightweight compared to its better-known competitor PEAR::DB. Tutorials are thin on the ground though, and I confess I only discovered its support for bind parameters recently, by reading between the lines of the documentation while looking up something else. Since then I’ve been gradually refactoring existing code — whenever I need to edit some code for some other reason I have a look for any embedded SQL and add some data-binding goodness to it. This is simple to do with ADODB. For example:

Before:

$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = '$myvalue'";
$Result = $DB->GetRow($SQL);

having first carefully cleaned the contents of $myvalue of course!

After:

$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = ?";
$Result = $DB->GetRow($SQL, array($myvalue));

Note I didn’t need to quote the string in the SQL statement, and the database library deals with any escaping necessary, in the appropriate style for the database being used — no MySQL-specific code here! In addition the SQL statement is pre-compiled — if you are going to execute the same statement a number of times with different values each time (e.g. when doing multiple inserts), this can really boost performance.

So what’s not to like? I don’t know why this technique isn’t better known among PHP developers. For example here is a lengthy thread discussing SQL injection, involving some apparently quite knowledgeable developers, and yet bind parameters are never mentioned; everyone pins their hopes on the stupidly named PHP function mysql_real_escape_string. Perhaps this will change now that support for binding is built into the PDO database access layer in PHP 5. Even if you are still using PHP 4.x you owe it to yourself to try a database abstraction library instead of endlessly hard-coding for one specific database engine.

Of course I should add that none of this means you can dispense with proper data validation before writing it to the database: the golden rule still remains “Never trust user input”!

These days practically every website has a “contact us” form allowing visitors to send email to the site owner without exposing the recipient’s email address to the world.

Lately I’ve been investigating ways that spammers can hijack these forms to send spam without the site owner’s knowledge. The fact is that it’s alarmingly easy to use an insecure form as a spamming engine, using a technique known as “header injection” — and the resulting spams will be clearly identified as coming from your site, resulting in possible blacklisting of the server, or even having your site shut down by your webhost for spamming.

The good news is that once you know exactly how the spammers can exploit the holes, it’s quite easy to guard against them. My researches soon turned up a page with a technical explanation of the topic. And from there I moved on to sites with examples of techniques for addressing vulnerabilities. Armed with this information it didn’t take me long to knock up a PHP function for processing all input from forms to strip out anything potentially dangerous. And knowing what I know now, I’d be reluctant to use any of the many free “form to mail” scripts without carefully checking whether they are vulnerable to this exploit.

I should stress that although the examples I’ve used are PHP, this problem is not a weakness of any particular scripting language or web server software — it’s simply a result of the fact that when standards for email headers were developed, the Internet was a kinder, gentler place than it is now, and spam wasn’t even a blip on the horizon.