The HigherSites Blog

Choosing between PHP and MySQL for quick jobs

I recently imported a database which included a selection of French postcodes. Because of a bug with the original export (received in a spreadhseet) some of the postcodes were missing the leading zeros, and some of them actually had more leading zero’s than were required.

Unfortunately the problem wasn’t raised until the data was imported, and a fresh import was going to take me hours. Fortunately all French postcodes are 5 characters long so it was possible to fix this problem programmatically.

When performing a task like this, there’s nearly always two options. Write an SQL query to do it:

UPDATE customers SET postcode = CONCAT('0',postcode) WHERE country = 'france' AND CHAR_LENGTH(postcode) = 4;

or

UPDATE customers SET postcode = SUBSTRING(postcode,-5) WHERE country = 'france' AND CHAR_LENGTH(postcode) = 6;

Or do exactly the same thing using a short PHP script:

$sql = "SELECT id,postcode FROM customers WHERE country = 'france'";
$rs = mysql_query($sql);
while($row = mysql_fetch_assoc($rs)) {
$replacement_postcode = "";
if(strlen($row['postcode']) == 6) {
$replacement_postcode = substr($row['postcode'], -5);
} elseif(strlen($row['postcode']) == 4) { {
$replacement_postcode = "0" . $row['postcode'];
}
if($replacement_postcode  != "") {
mysql_query("UPDATE customers SET
postcode = '" . mysql_real_escape_string($replacement_postcode) . "'
WHERE id = '" . intval($row['id]) . "');
}
}

Although the SQL query is obviously far shorter, neater and more efficient the PHP code offers the benefits of much easier debugging and modification.

I have to admit that 9 times out of 10 I’ll write a PHP script. It may seem like a lot more characters to type, but I value the ease of debugging and find that I tend to write PHP as quickly as I can think it.

Whether to perform quick tasks like this with an SQL query or by writing a short PHP script is a decision which sounds insignificant, but if making the right decision saves me half an hour over the course of a week it can be the difference between a relaxed Friday afternoon game of Quake in the office, or working my socks off right up until 5:30.

Writing secure form to email scripts with the PHP mail() function

Author: David Hurst

Spammers all around the globe are profiting on a daily basis from poorly written form to email scripts. Scripts like this can be abused by spammers, who will inject new email headers into the form fields, effectively allowing them to send whatever message they want, to whomever they want, courtesy of your mail server. Once they find a vulnerable script page, they will mercilessly hit it with their spam robots and send as much spam as they can until your server inevitably keels over and dies, possibly taking your websites with it. Cue phone calls from lots of unhappy customers and a time-consuming mail queue clean-up job.

The vulnerability is due to the way email messages are constructed. Each message has headers, including: To; From; Subject, etc. - the problem is that the headers can be sent in any order and in any quantities. As the headers are just plain text lines split by line breaks, the spammers can paste their content (including the required line breaks) into your form box and it will be passed in the POST array. This only creates a problem if you are then allowing any of the form content to form part of the email headers. Let me elaborate:

If you have a box on your form for the visitor to enter their email address, and then you use that data as your From address in the email header, you have effectively given the spammers easy access to the email header, and as a result, carte blanche to send whatever they like through your mail server.

It is very convenient to have an enquiry come through to your inbox with the customers email address in the header, so all you have to do is hit “reply”. This convenience comes at great cost.

It’s relatively simple to write a script that parses through the form data looking for line breaks that shouldn’t be there, or header information where it shouldn’t be. So, there really is no excuse for allowing insecure PHP mail scripts on any website.

Bear in mind that whilst the above measures will stop spammers abusing your mail server, it won’t stop them from trying, and their attempts may be sent through to your inbox like any other enquiry. A simple CAPTCHA system can knock this on the head and help keep your inbox clean, while the changes to your script ensures everyone else’s inbox stays clean too!