As applications are getting more complex and manipulating data in more complex ways it’s becoming more important to optimise our database queries to be as efficient as possible. I’m not going to cover caching in this article, instead I’m going to cover a more fundamental rule that should be followed when querying any database and follows this Python rule:

“Explicit is better than implicit.”

Lets start by looking at MySQL query performance from the command line. Lets imagine we had a user table with the following fields:

CREATE  TABLE  `article_db`.`users` (
`id` INT( 6  )  UNSIGNED NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY ,
`group_id` INT( 6  )  UNSIGNED NOT  NULL ,
`username` VARCHAR( 30  )  CHARACTER  SET utf8 COLLATE utf8_general_ci NOT  NULL ,
`email` VARCHAR( 100  )  CHARACTER  SET utf8 COLLATE utf8_general_ci NOT  NULL ,
`password` VARCHAR( 36  )  CHARACTER  SET utf8 COLLATE utf8_general_ci NOT  NULL ,
`created` DATETIME NOT  NULL DEFAULT  '0000-00-00 00:00:00',
`updated` DATETIME NOT  NULL DEFAULT  '0000-00-00 00:00:00',
`active` TINYINT( 1  )  NOT  NULL DEFAULT  '0',
UNIQUE (
`username` ,
`email`
)
)  ENGINE  =  MYISAM  CHARACTER  SET utf8 COLLATE utf8_general_ci;

Normally to retrieve data from this table you would execute a query like the following. please note that I am using a remote database to create some lag. If you use a local database on your development machine you will probably retrieve the data in 0 seconds.

mysql> SELECT * FROM users;
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
| id | group_id | username | email                 | password                         | created             | updated             | active |
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
|  1 |        1 | admin    | admin@example.com     | 21232f297a57a5a743894a0e4a801fc3 | 2010-04-11 23:08:51 | 2010-04-11 23:08:51 |      1 |
|  2 |        2 | user     | user@example.com      | ee11cbb19052e40b07aac0ca060c23ee | 2010-04-11 23:08:51 | 2010-04-11 23:08:51 |      1 |
|  3 |        3 | test     | test@example.com      | 098f6bcd4621d373cade4e832627b4f6 | 2010-04-11 23:13:50 | 2010-04-11 23:13:50 |      0 |
|  4 |        4 | temp     | temp@example.com      | 3d801aa532c1cec3ee82d87a99fdf63f | 2010-04-11 23:13:50 | 2010-04-11 23:13:50 |      1 |
|  5 |        2 | me       | me@example.com        | ab86a1e1ef70dff97959067b723c5c24 | 2010-04-11 23:15:50 | 2010-04-11 23:15:50 |      1 |
|  6 |        2 | you      | you@example.com       | 639bae9ac6b3e1a84cebb7b403297b79 | 2010-04-11 23:15:50 | 2010-04-11 23:15:50 |      0 |
|  7 |        1 | john     | john@example.com      | 527bd5b5d689e2c32ae974c6229ff785 | 2010-04-11 23:17:04 | 2010-04-11 23:17:04 |      1 |
|  8 |        1 | master   | master@example.com    | eb0a191797624dd3a48fa681d3061212 | 2010-04-11 23:17:04 | 2010-04-11 23:17:04 |      1 |
|  9 |        1 | theboss  | theboss@testing.com   | b248e08d5c23541514558eea059c08cf | 2010-04-11 23:23:43 | 2010-04-11 23:23:43 |      1 |
| 10 |        2 | susan    | susan@localdomain.com | ac575e3eecf0fa410518c2d3a2e7209f | 2010-04-11 23:23:43 | 2010-04-11 23:23:43 |      1 |
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
10 rows in set (0.05 sec)

In the previous query we used the wild-card selector ‘*’ to return all columns from the ‘users’ table. This in turn queried the database and returned the results as above. What I’m going to do next is query the database for the same data but this time I’m going to name the fields that I require in my returned dataset:

mysql> SELECT id,group_id,username,email,password,created,updated,active FROM users;
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
| id | group_id | username | email                 | password                         | created             | updated             | active |
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
|  1 |        1 | admin    | admin@example.com     | 21232f297a57a5a743894a0e4a801fc3 | 2010-04-11 23:08:51 | 2010-04-11 23:08:51 |      1 |
|  2 |        2 | user     | user@example.com      | ee11cbb19052e40b07aac0ca060c23ee | 2010-04-11 23:08:51 | 2010-04-11 23:08:51 |      1 |
|  3 |        3 | test     | test@example.com      | 098f6bcd4621d373cade4e832627b4f6 | 2010-04-11 23:13:50 | 2010-04-11 23:13:50 |      0 |
|  4 |        4 | temp     | temp@example.com      | 3d801aa532c1cec3ee82d87a99fdf63f | 2010-04-11 23:13:50 | 2010-04-11 23:13:50 |      1 |
|  5 |        2 | me       | me@example.com        | ab86a1e1ef70dff97959067b723c5c24 | 2010-04-11 23:15:50 | 2010-04-11 23:15:50 |      1 |
|  6 |        2 | you      | you@example.com       | 639bae9ac6b3e1a84cebb7b403297b79 | 2010-04-11 23:15:50 | 2010-04-11 23:15:50 |      0 |
|  7 |        1 | john     | john@example.com      | 527bd5b5d689e2c32ae974c6229ff785 | 2010-04-11 23:17:04 | 2010-04-11 23:17:04 |      1 |
|  8 |        1 | master   | master@example.com    | eb0a191797624dd3a48fa681d3061212 | 2010-04-11 23:17:04 | 2010-04-11 23:17:04 |      1 |
|  9 |        1 | theboss  | theboss@testing.com   | b248e08d5c23541514558eea059c08cf | 2010-04-11 23:23:43 | 2010-04-11 23:23:43 |      1 |
| 10 |        2 | susan    | susan@localdomain.com | ac575e3eecf0fa410518c2d3a2e7209f | 2010-04-11 23:23:43 | 2010-04-11 23:23:43 |      1 |
+----+----------+----------+-----------------------+----------------------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)

As you can see, by defining a more explicit query you have reduced the execution time from 0.05 to 0.01 seconds. This may not be too impressive, but imagine the “now” common application queries a database. During the life cycle of a page execution in a content management system you could be looking at around 10 – 40 (this is just an example figure) database queries being executed during this time. In turn each of these queries take .05 seconds to execute (40 * .05) = 2 seconds. So you go through your application and modify it to select one the columns you need. If we take the examples above we would have (40 * .01) = .04, thats a massive 1.96 seconds faster!

Hope this helps!

In Django when creating form templates it’s good practice in the field’s of accessibility and usability to mark a field as “required”. Django provides us with numerous methods to render forms into custom templates but most of the time it is quite nice to be able to just iterate over all of the fields in the form object and render them out in turn. In cases like this it might be required denote some form fields as “required”. Below I will demonstrate how you would typically render a form in a loop and detect if the current form element is required:

{% extends 'base.html' %}
{% load i18n %}

{% block content %}
{% trans "Contact Us" %}


{% trans "Contact Form" %}

		{% for field in form %}

				{% if field.errors %}
{{ field.errors|striptags }}

				{% endif %}
				{% if not field.is_hidden %}{% trans field.label %} {% if field.field.required %}({% trans "Required" %}){% endif %}{% endif %}
				{{ field }}
			

		{% endfor %}      





{% endblock content %}

The important part of the snippet above is the “{% if field.field.required %}”. The required attribute of the current element object, in this case “field”, resides in “field.required”. So just to firm this in your mind it in “{object}.field.required” or “{form_object}.{object}.field.required”.

Hope this helps.

Well I got a little bored and decided to code this function. It creates the old Twitter like date format i.e. About 4 minutes ago, etc.. As you can see the code below has a few tests so you can decipher how to use it.

<?php
	#If your running PHP > 5.3 you need to set this or your app will throw errors
	date_default_timezone_set('Europe/London');

	function twitter_time_format ($date) {

		$blocks = array (
			array('year',  (3600 * 24 * 365)),
			array('month', (3600 * 24 * 30)),
			array('week',  (3600 * 24 * 7)),
			array('day',   (3600 * 24)),
			array('hour',  (3600)),
			array('min',   (60)),
			array('sec',   (1))
		);

		#Get the time from the function arg and the time now
		$argtime = strtotime($date);
		$nowtime = time();

		#Get the time diff in seconds
		$diff    = $nowtime - $argtime;

		#Store the results of the calculations
		$res = array ();

		#Calculate the largest unit of time
		for ($i = 0; $i < count($blocks); $i++) {
			$title = $blocks[$i][0];
			$calc  = $blocks[$i][1];
			$units = floor($diff / $calc);
			if ($units > 0) {
				$res[$title] = $units;
			}
		}

		if (isset($res['year']) && $res['year'] > 0) {
			if (isset($res['month']) && $res['month'] > 0 && $res['month'] < 12) {
				$format      = "About %s %s %s %s ago";
				$year_label  = $res['year'] > 1 ? 'years' : 'year';
				$month_label = $res['month'] > 1 ? 'months' : 'month';
				return sprintf($format, $res['year'], $year_label, $res['month'], $month_label);
			} else {
				$format     = "About %s %s ago";
				$year_label = $res['year'] > 1 ? 'years' : 'year';
				return sprintf($format, $res['year'], $year_label);
			}
		}

		if (isset($res['month']) && $res['month'] > 0) {
			if (isset($res['day']) && $res['day'] > 0 && $res['day'] < 31) {
				$format      = "About %s %s %s %s ago";
				$month_label = $res['month'] > 1 ? 'months' : 'month';
				$day_label   = $res['day'] > 1 ? 'days' : 'day';
				return sprintf($format, $res['month'], $month_label, $res['day'], $day_label);
			} else {
				$format      = "About %s %s ago";
				$month_label = $res['month'] > 1 ? 'months' : 'month';
				return sprintf($format, $res['month'], $month_label);
			}
		}

		if (isset($res['day']) && $res['day'] > 0) {
			if ($res['day'] == 1) {
				return sprintf("Yesterday at %s", date('h:i a', $argtime));
			}
			if ($res['day'] <= 7) {
				return date("\L\a\s\\t l \a\\t h:i a", $argtime);
			}
			if ($res['day'] <= 31) {
				return date("l \a\\t h:i a", $argtime);
			}
		}

		if (isset($res['hour']) && $res['hour'] > 0) {
			if ($res['hour'] > 1) {
				return sprintf("About %s hours ago", $res['hour']);
			} else {
				return "About an hour ago";
			}
		}

		if (isset($res['min']) && $res['min']) {
			if ($res['min'] == 1) {
				return "About one minut ago";
			} else {
				return sprintf("About %s minuts ago", $res['min']);
			}
		}

		if (isset ($res['sec']) && $res['sec'] > 0) {
			if ($res['sec'] == 1) {
				return "One second ago";
			} else {
				return sprintf("%s seconds ago", $res['sec']);
			}
		}
	}

	$now            = date(DATE_RFC2822, time());
	$anhourago      = date(DATE_RFC2822, time() - (3600));
	$fourhoursago   = date(DATE_RFC2822, time() - (3600 * 4));
	$yesterday      = date(DATE_RFC2822, time() - (3600 * 24));
	$lastweek       = date(DATE_RFC2822, time() - (3600 * 24 * 6));
	$lastmonth      = date(DATE_RFC2822, time() - (3600 * 24 * 31));
	$threemonthsago = date(DATE_RFC2822, time() - (3600 * 24 * 31 * 3));
	$ayearago       = date(DATE_RFC2822, time() - (3600 * 24 * 365));
	$fiveyearsago   = date(DATE_RFC2822, time() - (3600 * 24 * 365 * 5));

	echo 'Now (should be empty): '              . twitter_time_format($now) . '';
	echo 'An Hour Ago: '      . twitter_time_format($anhourago) . '';
	echo 'Four Hours Ago: '   . twitter_time_format($fourhoursago) . '';
	echo 'Yesterday: '        . twitter_time_format($yesterday) . '';
	echo 'Last Week: '        . twitter_time_format($lastweek) . '';
	echo 'Last Month: '       . twitter_time_format($lastmonth) . '';
	echo 'Three Months Ago: ' . twitter_time_format($threemonthsago) . '';
	echo 'A Year Ago: '       . twitter_time_format($ayearago) . '';
	echo 'Five Years Ago: '   . twitter_time_format($fiveyearsago) . '';
?>

Hope this helps!

In this article I will cover how to convert a date from such things as a database like MySQL, RSS feed or other formats into a UNIX timestamp. Firstly for some people it will be handy to know what a UNIX timestamp actually is. A UNIX timestamp is the number of seconds that have passed since midnight on the 1st January, 1970 also known as the birth of modern computing.

In PHP there two; nice, simple functions for generating UNIX timestamps. The first is the “time()” function used as follows:

    <?php
        //Get the current UNIX timestamp
        $now = time();
        //Get the date one week from now as a UNIX timestamp
        $nextweek = $now + (3600 * 24 * 7);
        //Get the date last week as a UNIX timestamp
        $nextweek = $now + (3600 * 24 * 7);
        //Compare the dates
        if ($lastweek < $nextweek) echo 'Next week is greater than last week';
    ?>

I think you can gather from that that UNIX timestamps are quite useful when you need to calculate or compare dates. Because a UNIX timestamp is in seconds it is treated like any other integer number.

Now for the function we would use to generate a UNIX timestamp from a date:

    <?php
        //The following date could be any date in
        //ISO 8601, RFC 2822 and even non ISO
        //or RFC standards
        $date = date('Y-m-d h:i:s');
        //Get the UNIX timestamp
        $timestamp = strtotime($date);
    ?>

As you can see it’s really quite simple to get a UNIX timestamp from a date. But that’s not all you can do all sorts of amazing things with dates in PHP. To see more great examples of just how flexible the strtotime function really is just look at the PHP function reference at http://php.net/manual/en/function.strtotime.php.

Hope this helps.

I keep seeing people asking the question “How do I get code completion to work in Aptana and Eclipse PDT?”. A lot of the time I see the Helper fix but nothing else! With a bit of fiddling I’ve finally got a complete solution that works! Here how I did it:

Follow this guide to enable the .thtml or .ctp file extentions: Setting up Eclipse to work with Cake

Create a dummy file in your cake root directory with the following code:

<?php
   exit();
   //declare and define all of the helpers
   //to helper enable codecompletion
   if(1 == 0) {
        $ajax = new AjaxHelper();
        $cache = new CacheHelper();
        $form = new FormHelper();
        $html = new HtmlHelper();
        $javascript = new JavascriptHelper();
        $number = new NumberHelper();
        $session = new SessionHelper();
        $text = new TextHelper();
        $time = new TimeHelper();
    }
?>

Then to get the Model code completion from within controllers create an app_controller.php file in you app directory and add a dummy function with the following code:

<?php
    class AppController extends Controller {
        public function dummy () {
            //for each of your models add a definition and declaration
            //like the ones that follow
            $this->User = new User();
            $this->Post = new Post();
        }
    }
?>

So with a bit of luck this will help you on your way. Thanks for reading!

The MySQL REPLACE function is one of the most useful function MySQL has to offer. It’s great if you need to do a bulk find and replace on text in your database table. It’s very useful for migrations from one domain name to another and I have used it countless times to move Wordpress installations from a testing to a production environment to re-point images. Here the code:

UPDATE table_name SET column_name = REPLACE(column_name, 'find_string_in_quotes', 'replace_string_in_quotes');

Hope you find this useful. If you know a better way please leave a reply!

Version 1.5.0 has now been released.

* This Plugin Requires CURL To Work *

This Wordpress Twitter plugin is a nice simple plugin. All it does is post updates from your blog to your twitter account when you create a fresh post and allows you to tweet from you blog. Some other features are:

• Auto shorten URL’s with tinyurl
• Validation to make sure you can tweet or warns you if you cant
• Automatic character limitation to make sure you don’t post anything over 140 characters
• Good for SEO, creating automatic links to your blog and it’s posts

Installation

All you have to do is download version 1.1.0 from the link below, unzip it, upload it to your plugins folder and activate it from the plugins menu.

Hope you like it :)

Download WP Post To Twitter

Have you ever heard of an SQL injection attack? Ever wondered how it’s actually done? Well here’s a great video showing you how:

Blind SQL Injection Attack Video

You don’t often get to see a demonstration of how an attacker will actually use techniques such as this the infiltrate a site. My belief is that if you need to defend yourself or your application against it you need to see it in action. milw0rm is a great place to see what applications are vulnerable to attack, how, why and has it been fixed.

What could have been done to prevent this attack?

Lots of things could have been done to prevent this from happening, but often is the case in large applications or small one’s built by an inexperienced developer holes are left in the security of an application. One thing to remember when developing an application that any user submitted data should be treated as hostile. It should be checked via Regex, cleaned, Type Casted and validated to ensure what your getting from the user is what you expected.

In the video a function as simple as mysql_real_escape_string could have prevented this but here are some other things you should know about:

Hash your passwords

I don’t know if you noticed in the video but the attacker acquired a plain text password. This was the application developers first mistake. If the password had been hashed, even if the attacked had got the entire hash it would have been useless. If you put another hash in it will just be converted to guess what, another hash. This would have rendered this attack fruitless. MD5 is a great Hashing function that comes pre-packed with most programming languages and would turn this “password” into this “5f4dcc3b5aa765d61d8327deb882cf99″. Even if one character changes the hash would be completely different.

Use a Database abstraction layer

In PHP a great example of this would be the MDB2 class. This class comes with a whole list of security features including some great sanitisation functions. Using a single gateway to your database will greatly reduce the margin for error. You could even develop your own with automatic data cleaning.

Use an odd admin section name

One issue with applications with an integrated CMS is the admin section is simply called “admin”. In this case if the attacker armed with a username and password couldn’t find your admin section he would have had to employ some more sophisticated tools to monitor where you where going to update your site. At this point they may get bored or more often than not, it’s an automated attack. In this case the application may try to guess where it is, fail and move on. So try to name your admin sections more carefully!

Well I hope someone finds this of use and I look forward to your responses!

At the start of the year I wrote an article over on another site detailing the different methods you could use to select elements using the powerful jQuery attribute selectors. Because I no longer work for that company any more and the article is no longer being maintained, I’ve decided to create a second version of the article so I can update it and extend it’s usefulness.

jQuery selectors and attribute selectors are some of the best features jQuery has to offer when it comes to DOM manipulation. They allow you to quickly select all elements or groups of element of a given tag name, attribute name or their contents and allow you to manipulate them as a group or a single node. This article can be used as a beginners guide to selectors or as a cheahsheet or reference.

The following table lists the different methods you have available to you to select nodes when using jQuery. All of the listed selectors should be wrapped in the following to stop your jQuery scripts conflicting with other libraries:

    (function ($) {
        //Your code here
    })(jQuery);

jQuery selectors and examples

Below are a slip tabular list of the different selector and groups they seem to fit into.

• jQuery Simple selectors
• jQuery CSS style sudo selectors
• jQuery XPath style selectors
• jQuery form selectors

Well I hope that version two of “jQuery selectors and attribute selectors reference and examples” is more helpful than V1. With a bit of luck you’ll find the grouping a little better too. Thanks for visiting! Please leave a message!