Bound to succeed

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:


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

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


$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”!

Posted in Databases, PHP, Web development | Tagged | Leave a comment

Saying no to spammers

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.

Posted in PHP, Web development | Tagged , , | Leave a comment

Website planning: a blueprint for success

Once you’ve established the purpose of your website, and who will be using it, you can start to plan the basic structure of the site, which will determine the all-important site navigation system.

Too many sites reflect the internal politics of the committee that created them — don’t let yours fall into this trap! The structure of the site should reflect the perspective of your target audience, not your own internal organization. If you have identified several audiences (e.g. investors, customers, employees) you may need to offer different paths through the site to help them find what they need.

So your next job is to create a structure which will support the site’s objectives. Always keep in mind that visitors to your site are looking for information. Its purpose should be immediately obvious, and it should be easy to navigate. Great content is no use if people can’t find it!

The first thing you need to do is to establish a “content inventory” by asking yourself two questions:

  • What sort of information are my visitors looking for? Remember different audiences may have different requirements.
  • What will visitors want to do? This could include buying goods, searching for information, personalising content, signing up for a newsletter …

Make a list of all the ideas you come up with, if necessary categorized by audience. Then check that these ideas are consistent with the goals of the site — throw out any that aren’t. The next step is to take all these elements and organize them into a logical structure. Use whatever means you are comfortable with for this — it might be a flowchart, a mind map diagram, or even a collection of index cards which you can shuffle about on a table and divide into piles representing different sections of the site. It can also be helpful to visualize your site using a metaphor — for example, if you are selling groceries, a supermarket is an obvious organizational model. It’s a good idea to involve people from different parts of your organization in this process — they will bring valuable perspectives which you may have overlooked.

Once you are happy with your structure, draw up a formal site plan and get everyone involved to agree on it. This document will ultimately determine the basic navigational structure which will be used for every page on the site. You (or your designer) will also be able to use it to create a list of all the pages which need to be created, and the elements they must include. This list will later be used to allocate tasks and manage the development process.

The major navigation elements of the site should now be obvious. For example, you have probably identified major sections of the site which should be accessible from every page. If the site is quite small, this may be all the navigation you need. If you have a deep, many-layered structure, you will probably want to add a sub-menu of items specific to each section, to avoid cluttering every page with a confusing mass of options.

A well-defined site structure means you know from the outset what the scope of the project is, and it makes designing consistent page layouts and templates a much easier task. In the long run, it will simplify maintenance and updating of the site, so you can keep content fresh and add new features in response to customer demand without busting your budget. Result — more happy customers and a boost to your bottom line!

Posted in Web development | Tagged , | Leave a comment

Three questions to ask before you call your web designer

So you’ve decided to create a new Web site, or renovate your existing one. We all know that in Internet time, the best time to launch a new project is always last week, so it’s very tempting to plunge straight in, call your web designer, and fix up a meeting. But stop and reflect before you grab that phone. Here are three questions you should ask yourself first, whether you are creating the site yourself or paying a professional to do it for you. In the long run you’ll save yourself both time and money.

What is the site for?

Yes, this sounds like a dumb question. But you must have visited websites where even after viewing three or four pages, you couldn’t work out what the point was! If you aren’t clear about your goals, your visitors won’t be either. So the very first thing you should do is to define the purpose of your site. Try to distil out the essence of your site into a single short paragraph — or even a single sentence. If you can’t do this, your site will lack focus. For example, “The goal of this site is to generate leads for Product X”. Of course the site may have other, subsidiary objectives — if that’s the case note these down too, so that you can take them into account during the design process.

Who are you trying to reach?

Now you know what your site is for, imagine the people who will use it. Try to get inside their heads — how old are they? Are they male or female? What are their interests? What other sites do they visit? What do they want from you? Some people find it helpful to invent characters representing different types of visitor, and picture them using the site.

If you are revamping an existing site you already have some very valuable information about your customers in your server log files. Use a log analysis package to identify which are the most and least popular pages or sections of your site. The terms people entered into search engines to find you are a good indicator of their interests. And looking at the paths people take through your site can often indicate where navigation is confusing, or suggest areas that could be expanded.

Whatever the overall goal of your site, remember it must please the end users if it is to succeed — look at it from their point of view, and try to provide information and services of value to them.

How will you know you have succeeded?

It’s easy to say “The goal of my site is to sell more widgets”. Or “We want to increase page views and hence advertising revenue.” But how will you know when you have achieved your goal? Try to come up with some specific, measurable objectives related to your primary goal. For example, “We expect the new website to increase sales of widgets by 30% in the first 6 months”. This is important for at least two reasons:

  • It gives you a specific target to aim for. If everyone working on the project keeps this target in mind, it will focus their efforts on the tasks that need to be done to achieve it. And with proper planning, you can evaluate how the designer’s proposals will advance your goals. If that cool 500K Flash movie on the home page doesn’t contribute to a specific objective, then dump it!
  • It gives you something to measure results against. If after 6 months sales have only increased by 10%, what went wrong? Were your objectives realistic? What could you have done differently? Of course if sales increase by 60%, you can give yourself a well-earned pat on the back!

It needn’t take a long time to come up with answers to these questions. Doing this preliminary groundwork will help your web designer come up with an appropriate solution, and save you expensive consultancy time. And while this approach doesn’t guarantee success, it will greatly improve your chances of building a website that works.

Posted in Web development | Tagged | Leave a comment