Why would MySQL crash when WordPress attacked?

WordPress brute force attacks are increasingly common at the time I write this – September 2014. Recently a server I look after was coming under attack and then after a few minutes the site would display ‘Error establishing a Database Connection’. error-establishing-database-connectionWhen I logged into the server the MySQL service wasn’t running.

My first thoughts were that something in the attack was causing MySQL to crash, There was no information in the MySQL error.log to indicate why though so I spent time looking at plugins to help mitigate a brute force attacks, which tend to focus on the WordPress wp-login.php and xmlrpc.php files:

Disable XML-RPC will disable WordPress XML-RPC functionality meaning that nothing can be done to your content, however this does nothing to prevent initial database queries so MySQL can still be affected when running this, and it does nothing about wp-login.php either.

BruteProtect – I’ve seen comments that it will protect xmlrpc but I’ve seen it in action during an attack and as of version 2.2.2 it did nothing to stop it.

NinjaFirewall – there are a lot of configuration options but this one does the job. It sits in front of WordPress and so it can intercept the attack before all the database activity starts up. This worked great when I used it during an attack.

However you may host multiple WordPress sites on a single server and it could be tedious having to install this and configure it for each site, plus there is going to be duplication of what the code is doing and Apache still has to handle all the incoming requests.

It may be better to stop any attacks at the firewall because that’s the single point of entry to your server. This approach still requires a plugin – WP fail2ban but this one will log attacks to your systems auth.log which can be picked up by fail2ban and automatically added for a temporary period to your firewall rules. It is more complicated than the other plugins to install but once a malicious IP has been blocked it can’t affect any of the sites on your server will keep the system load much lower.

After getting the fail2ban solution in place the database was brought down yet again. I ended up looking in kern.log and at the time of the attack was the line ‘apache2 invoked oom-killer’ – that revealed the problem… Apache was using so many processes that it was running out of memory and as this particular server runs off an SSD the swap file is disabled. As there was nowhere else to get memory from it started killing off other processes, the biggest of these was MySQL.

What a relief to find the cause – MySQL wasn’t crashing at all. The solution was just to reduce the MaxClients value in apache2.conf – the default value of 150 is way too big for a modest sized server. If each process takes 40 MB then 150 processes require 6 GB of RAM. Getting a realistic value for this requires a little load testing plus the use of some tools to see how the memory usage increases as Apache has to handle more requests.

MySQL NO_ZERO_DATE and minimum timestamp default

It is a good idea to use MySQL with sql-mode = NO_ZERO_DATE,NO_ZERO_IN_DATE (among other options)

If the NO_ZERO_DATE flag is not set, MySQL will allow ‘0000-00-00’ in date and datetime fields. This also allows the silent conversion of invalid dates to this zero date as well.
You probably don’t want to find zero dates being allowed into your database if there is a problem with any of the data validation you are carrying out before inserting/updating data.

However you have to use something as a default value when creating a table that uses date and datetime fields. My recommendation is to go for the minimum allowed value: ‘1000-01-01’ for a date field and ‘1000-01-01 00:00:00’ for a datetime, if the field is a timestamp then this is ‘1970-01-01 00:00:01’.

Blog Tutorial with Fat-Free Framework v3

This article is adapted from my first one originally written 30/09/2011 for v2
This version was updated 18/02/13 for v3.05

Version 3 is better with even less typing required to access the power of this simple framework.

A while ago now I read an excellent tutorial on ‘Rapid Application Prototyping in PHP Using a Micro Framework‘, this used the Slim micro framework but one thing that bothered me was it required 5 packages before development could start.
These were: Slim (the micro framework), Slim Extras (additional resources for Slim), Twig (template engine), Idiorm (object-relational mapper) and Paris (Active Record implementation).
It struck me that if you need an extra 4 packages alongside Slim for a basic blog then maybe it is a little too skinny and I set out to find a micro framework that could do the same thing without these dependencies.

I found the Fat-Free Framework. It is condensed into a single 50KB file and has a host of features, you can find out about these on the web site so I won’t repeat it here. Instead I’ve reproduced the Slim tutorial to create a simple blog site, but using Fat-Free Framework instead.
You will need PHP 5.3 on your server, I used Ubuntu 12.04 for the tutorial as that version of PHP is easily installed.

Step 1: Setup

Download Fat-Free Framework (F3) from the website.
F3 works just as happily in the site root as from a subdirectory – I’ll assume you’re using an empty website site to do this tutorial.
Unzip the contents of the F3 download and copy the contents into your website root.
The folder contents should look like this:
folder contents

Notice how much simpler the starting site is compared with Slim + extra packages.

Move up one level in the directory heirarchy and set the permissions:

sudo chgrp -R www-data blog
sudo chmod -R 775 blog
cd blog
sudo chmod -R 777 tmp

If using Apache, mod_rewrite will need to be running.

You can browse this site right away and get the F3 start page.
Fat-Free Framework start page

(Once the site has been visited, F3 will create additional folders cache and temp – you don’t need to worry about these).

Step 2: Bootstrapping

As everything we need is already part of F3 you don’t need to do anything here!

You can however tidy up the site to remove the current home page and add a database connection.
Edit index.php and remove the two route functions – these are just used to display the welcome page and the documentation. It should look like this once they have been removed:

$f3=require('lib/base.php');
 
$f3->set('DEBUG',3);
$f3->set('UI','ui/');
 
$f3->run();

To setup a database connection add the following between the set and run commands:

$f3->set('DB',
  new DB\SQL(
    'mysql:host=localhost;port=3306;dbname=YourDatabaseName',
    'YourUserName',
    'YourPassword'
  )
);

All the User Interface files will go in the ui directory, you can delete welcome.htm however the contents of the css folder may be useful as they provide some basic styling to make your pages look a little nicer.

Step 3: Routing

Similar to Slim you need to tell F3 the route request method (GET, POST, PUT etc), the URI to respond to and how to respond.
Example home page route:

$f3->route('GET /',
  function ($f3) {
  //do something
  }
);

And if you want to use a route with parameters you would use something like this:

$f3->route('GET /view/@id',
  function ($f3) {
    $id = $f3->get('PARAMS.id');
  }
);

This tells F3 to expect a URI parameter and assigns it to a PHP variable in the anonymous function.

Now for our blog we need basic administration routes like this (the code will come later):

// Admin Home
$f3->route('GET /admin',
  function ($f3) {
  }
);
 
//Admin Add
$f3->route('GET /admin/add',
  function($f3) {
  }
);
 
//Admin Edit 
$f3->route('GET /admin/edit/@id',
  function($f3) {
  }
);
 
//Admin Add and Edit both deal with Form Posts
//don't use a lambda function here
$f3->route('POST /admin/edit/@id','edit');
$f3->route('POST /admin/add','edit');
function edit($f3) {
}
 
//Admin Delete
$f3->route('GET /admin/delete/@id',
  function($f3) {
  }
);

Notice that we’re using the same function to process Add and Edit form posts so it isn’t anonymous but the function name is passed as a parameter to the route command.

Step 4: Models

The ORMs in Fat-Free Framework do all the hard work for you – no directories, files or code required here.
Are you beginning to see how much simpler this is compared with Slim?

Here’s the SQL that will set you up with the 2 tables necessary for this tutorial:

CREATE DATABASE `blog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
USE `blog`;
 
CREATE TABLE IF NOT EXISTS `article` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `title` VARCHAR(128) NOT NULL,
  `summary` VARCHAR(128) NOT NULL,
  `content` text NOT NULL,
  `author` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
INSERT INTO `article` (`id`, `timestamp`, `title`, `summary`, `content`, `author`) VALUES
(1, '2011-07-28 02:03:14', 'Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr White'),
(2, '2011-07-28 02:03:14', 'More Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr Green');
 
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO `user` (`id`, `name`, `password`) VALUES
  ('1', 'admin', 'password');

Step 5: Application Front End

Like the Slim tutorial we’re going to keep this simple.
F3 has a set of object-relational mappers (ORMs) to make it easy and fast to work with data.
Instantiate a data mapper object that interacts with the users table, call the find method to return a simple array of results, finally the set command is used which will pass the variable between MVC components. F3 calls this a framework variable.

$article=new DB\SQL\Mapper($f3->get('DB'),'article');
$articles=$article->find();
$f3->set('articles',$articles);

You could condense the final two lines together like $f3->set('articles',$article->find()); but I’m keeping this verbose to aid readability and help you figure out how it is all working.

To use templating you need a base layout file in the ui folder called layout.html, feel free to link in the contents of /ui/css if you want it to look nicer

<!DOCTYPE html>
<html>
  <head>
    <title>{{ @html_title }}</title>
      <meta charset='utf8' />
  </head>
  <body>
    <include href="{{ @content }}" />
  </body>
</html>

The F3 template engine uses {{ @name }} to write out the value of a framework variable.
The include tag will embed the contents of a file at the position where the directive is stated – in the example above we’re using a framework variable as the URL so that the content is dynamic.

Now lets create the first of those content files with the view for the homepage, called blog_home.html

<h1>Blog Titles</h1>
<repeat group="{{ @articles }}" value="{{ @item }}">
  <p><a href="view/{{ @item['id'] }}">{{ trim(@item['title']) }}</a> by {{ @item['author'] }}</p>
  <p>{{ @item['summary'] }}</p>
</repeat>

The repeat tag will loop through an array, setting item to the current array element. Within the loop item contains the array of data retrived from the database table and this is accessed using the column name as the array key.

Now that the view is in place we can complete the code in index.php to display it. Set the framework variable to tell the template which view to include, then tell F3 to serve the template.

  $f3->set('content','blog_home.html');
  echo Template::instance()->render('layout.html');

Serving the template also converts it to PHP code the first time it’s used and this optimised version is used thereafter which is great for performance.
The full code for the home page function is:

//home page
$f3->route('GET /',
  function ($f3) {
    $f3->set('html_title','Home Page');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $f3->set('list',$article->find());
    $f3->set('content','blog_home.html');
    echo Template::instance()->render('layout.htm');
  }
);

Now for the detail view, in index.php we need to get an Mapper object, search for the id then send the data to the view/template. In this case we could have assigned each value individually e.g. $f3->set('title',$article->title); but it is quicker to put all the values in the POST framework variable with the copyTo command.

$f3->route('GET /view/@id',
    function ($f3) {
    $id = $f3->get('PARAMS.id');
    //create Mapper object and search for id
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?', $id));
    //set framework variables
    $f3->set('html_title',$article->title);
    $article->copyTo('POST');
    //serve up the view
    $f3->set('content','blog_detail.html');
    echo Template::instance()->render('layout.html');
  }
);

And the view file itself called blog_detail.html accesses the individual data items from the POST framework variable:

<h1>{{ @POST.title }}</h1>
Published: {{ @POST.timestamp }} by {{ @POST.author }}
{{@POST.content}}
<a href="/">Back to Homepage</a>

Step 6: Application Back End

The admin home page just needs to list the blog articles and provide links, the code is similar to that for the homepage.

$f3->route('GET /admin',
  function ($f3) {
    $f3->set('html_title','My Blog Administration');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $list=$article->find();
    $f3->set('list',$list);
    $f3->set('content','admin_home.html');
    echo template::instance()->render('layout.html'); 
  }
);

The view called admin_home.html and contains a table of the results. It looks like this:

<h1>My Blog Administration</h1>
<p><a href='admin/add'>Add Article</a></p>
<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Date</th>
      <th>Author</th>
      <th colspan='2'>Actions</th>
    </tr>
  </thead>
  <tbody>
  <repeat group="{{ @list }}" value="{{ @item }}">
    <tr>
      <td>{{ @item.title }}</td>
      <td>{{ @item.timestamp }}</td>
      <td>{{ @item.author }}</td>
      <td><a href="admin/edit/{{ @item.id }}">Edit</a></td>
      <td><a href="admin/delete/{{ @item.id }}">Delete</a></td>
    </tr>
  </repeat>
  </tbody>
</table>

The output of this look like:
Admin Home Page
Now a form to add and edit articles, called admin_edit.html

<h1>Edit</h1>
<form name="blog" method="post" action="{{ @BASE }}{{ @PARAMS.0 }}" >
  <label for='title'>Title: </label><br /><input type="text" name="title" id="title" value="{{ isset(@POST.title)?htmlspecialchars(@POST.title):'' }}" size="60"/><br />
  <label for='author'>Author: </label><br /><input type="text" name="author" id="author" value="{{ isset(@POST.author)?htmlspecialchars(@POST.author):'' }}" size="60"/><br />
  <label for='summary'>Summary: </label><br /><textarea name="summary" id="summary" cols="60" rows="10">{{ isset(@POST.summary)?htmlspecialchars(@POST.summary):'' }}</textarea><br />
  <label for='content'>Content: </label><br /><textarea name="content" id="content" cols="60" rows="10">{{ isset(@POST.content)?htmlspecialchars(@POST.content):'' }}</textarea><br />
  <input type="submit" value="Submit"/>
</form>

I’ve kept this basic, apologies for the lack of styling but that’s not what this tutorial is about.

The lines containing isset are using the ternary operator to check that the template variable exists before trying to access it.

Now for the logic with the routes, add & edit both use the same view remember.

$f3->route('GET /admin/add',
  function($f3) {
    $f3->set('html_title','My Blog Create');
    $f3->set('content','admin_edit.html');
    echo template::instance()->render('layout.html');
  }
);
 
$f3->route('GET /admin/edit/@id',
  function($f3) {
    $f3->set('html_title','My Blog Edit');
    $id = $f3->get('PARAMS.id');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?',$id));
    $article->copyTo('POST');
    $f3->set('content','admin_edit.html');
    echo template::instance()->render('layout.html');
  }
);

Now we assigned a function for the POST routes and here’s the content:

$f3->route('POST /admin/edit/@id','edit');
$f3->route('POST /admin/add','edit');
function edit($f3) {
  $id = $f3->get('PARAMS.id');
  //create an article object
  $article=new DB\SQL\Mapper($f3->get('DB'),'article');
  //if we don't load it first Mapper will do an insert instead of update when we use save command
  if ($id) $article->load(array('id=?',$id));
  //overwrite with values just submitted
  $article->copyFrom('POST');
  //create a timestamp in MySQL format
  $article->timestamp=date("Y-m-d H:i:s");
  $article->save();
  // Return to admin home page, new blog entry should now be there
  $f3->reroute('/admin');
}
);

And to delete a record

//Admin Delete
$f3->route('GET /admin/delete/@id',
  function($f3) {
    $id = $f3->get('PARAMS.id');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?',$id));
    $article->erase();
    $f3->reroute('/admin');
  }
);

There’s a lot less coding required here than with Slim + extras.

Step 7: Using Middleware

This isn’t relevant to using the Fat-Free Framework.
Basic authentication using a database is built in.
The following lines are added to the admin homepage route, they will cause a username/password prompt to appear. If the details are in the user table of the database a session variable is set which allows access to the other admin screens. If unsuccessful they get an error page.

//assign a mapper to the user table
$user=new DB\SQL\Mapper($f3->get('DB'),'user');
//tell Auth what database fields to use
$auth=new \Auth($user,
  array('id'=>'name','pw'=>'password'));
//the following will display an HTTP 401 Unauthorized error page if unsuccessful
$auth->basic();

In the other admin routes, add this line at the start of the function to deny access if they haven’t authenticated

if (!$f3->get('SESSION.user')) $f3->error(401);

That’s it (if you didn’t spot it in the SQL, my example uses name: admin and password: password for the login credentials).

You could instead choose to redirect back to the homepage with:

if (!$f3->get('SESSION.user')) $f3->reroute('/');

Step 8: Summary

So here is another example of how to quickly get a prototype running using a PHP micro framework.
Is it any faster than with Slim? I’d like to think that it is, there’s less code, less complexity and you aren’t dependent on other packages which may end up changing in some way or not being maintained.
I’ve used F3 to create a times tables application for my son and found it was fun to build and made me more productive – I’ve now put it online at times-tables.willis-owen.co.uk for anyone to use.

Licencing
Slim is released under the MIT Public License and you are free to use, modify and even sell it.

F3 is released on the GNU Public License (GPL v3). You are allowed to use it your business or for commercial gain – but if you make any money you should consider making a donation to the project.

You can download the files used from here Fat-Free-Blog.zip.
And access the files online via my Bitbucket repository.

I’ve done another tutorial on creating a near identical blog application to this but using CakePHP: Blog Tutorial with CakePHP Framework which may be interesting to compare with differences with using a micro-framework.

CakePHP 2.0 AJAX sortable list that updates to databases

It is easy to create an AJAX sortable list with CakePHP 2.0, using jQuery that automatically updates the database, however it is hard to find documentation explaining how to do it.

Here is my explanation – it assumes a basic knowledge of CakePHP.

Step 1: Data

Lets use a simple categories table which includes a column for sort_order. This will be used to define the order the records are displayed on the page.

CREATE TABLE `categories` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) DEFAULT NULL,
  `sort_order` INT(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Step 2: CakePHP

Bake a model, controller and views for this table so that we have some files to work with.
You should be able to go onto your website at http://yoururl/categories and see the default index view.

Layout

In order to use the sortable feature, you will need to edit the default layout. If you don’t already have your own layout you can copy the CakePHP default one (which is used if your own one isn’t found) from /lib/Cake/View/Layouts/default.ctp and place it in app/View/Layouts/default.ctp.
Edit this so that the writeBuffer method is one of the final lines, just before the close body tag:

<?php echo $this->Js->writeBuffer();?>
</body>
</html>

This will create the HTML and JavaScript generated by the Js helper in the view.

Controller

At the top of the CategoriesController make the Js helper available to the controller.

public $helpers = array('Js');

I am going to add 2 actions, one to display the sorted categories and one to handle the AJAX requests to update the database.
The first action is called sort, all this does is retrieve all the categories sorted by the sort_order column and send them off to the view.

public function sort() {
	$this->set('categories', $this->Category->find('all',array(
		'order' => 'sort_order ASC',
		)
	));
}

The second action is called reorder.
This will receive the AJAX post and update the categories table. I’ve commented out a line that can be used for debugging. If uncommented this will write to app/tmp/logs/error.log and is a really good way to see what is happening when you are dealing with AJAX requests.

public function reorder() {
	foreach ($this->data['Category'] as $key => $value) {
		$this->Category->id = $value;
		$this->Category->saveField("sort_order",$key + 1);
	}
	//$this->log(print_r($this->data,true));
	exit();
}

The code here is just looping through the posted data which will look like this:

Array
(
    [Category] => Array
        (
            [0] => 4
            [1] => 5
            [2] => 3
            [3] => 6
            [4] => 2
            [5] => 1
        )
)

The index is the order of elements after they have been sorted and the value is the id of each element. When saving the data I am adding 1 to the index as the one coming from jQuery is zero based.

View

For the sake of simplicity I’ve included the links to the jQuery and jQuery UI libraries here but in practice you would probably want to put them in the head of your layout.
In the view I have a simple unordered list tag containing the data.
The id of each element is important as this is used by jQuery UI to format the data sent back to the server.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<ul id="my-list">
	<?php foreach ($categories as $category): ?>
	<li id='Category_<?php echo $category['Category']['id']?>'><?php echo $category['Category']['name']; ?></li>
	<?php endforeach; ?>
</ul>
<?php
$this->Js->get('#my-list');
$this->Js->sortable(array(
	'complete' => '$.post("/categories/reorder", $("#my-list").sortable("serialize"))',
	));
?>

And that is all there is to it.
You can simply drag and drop any item in the list and behind the scenes the database will be updated with the new sort order.

Blog Tutorial with CakePHP Framework

In a follow up to my previous article: blog tutorial with Fat-Free Framework I have tried to make exactly the same application but using the CakePHP Framework – to contrast using a micro-framework with a fully featured one.
CakePHP works with PHP 4 or 5 so this should work on most servers.

Step 1: Setup

Download CakePHP from the website. This tutorial uses version 1.3.11.
It runs happily from its own folder in a web site (so you don’t need to set up a separate virtual site) – I just used a folder called cake1, with the contents looking like this
CakePHP root directory

Step 2: Bootstrapping

We’ll use this section to configure the database connection.
Copy /app/config/database.php.default to database.php in the same folder.
Edit the values in the $default array to match your database.
You should also edit /app/config/core.php and change the values for Security.salt and Security.cipherSeed.
Make the app/tmp folder writable for the webserver by chown -R www-data app/tmp.
As we’re developing it may be worth disabling caching so in /app/config/core.php so uncomment

Configure::write('Cache.disable', true);

Step 3: Routing

With CakePHP a lot of routing happens automagically by putting correctly names files in the right places.
However we need to tell it a route for the homepage of the site.
Edit /app/config/routes.php and comment out all the default route(s), replacing with one to the articles controller.

//Router::connect('/', array('controller' => 'pages', 'action' => 'index'));
Router::connect('/', array('controller' => 'articles', 'action' => 'index'));

Step 4: Models

CakePHP needs the database to follow a naming convention. The table names should be plural so we’ll use articles and users.
CakePHP will dynamically creates model objects for you if it cannot find corresponding files in /app/models, as this is a tutorial about getting a prototype running quickly we’ll take advantage of this here.
Here’s the SQL that will set you up with the 2 tables necessary for this tutorial, it is exactly the same as for my Fat-Free Framework tutorial:

CREATE DATABASE `blog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
USE `blog`;
 
CREATE TABLE IF NOT EXISTS `articles` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `title` VARCHAR(128) NOT NULL,
  `summary` VARCHAR(128) NOT NULL,
  `content` text NOT NULL,
  `author` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
INSERT INTO `articles` (`id`, `timestamp`, `title`, `summary`, `content`, `author`) VALUES
(1, '2011-07-28 02:03:14', 'Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr White'),
(2, '2011-07-28 02:03:14', 'More Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr Green');
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO `users` (`id`, `name`, `password`) VALUES
  ('1', 'admin', 'password');

Step 5: Application Front End

First create a layout that will be used as a template for the individual views /app/views/layouts/default.ctp:

<!DOCTYPE html>
<html>
  <head>
  <title><?php echo $title_for_layout?></title>
</head>
<body>
  <?php echo $content_for_layout ?>
</body>
</html>

Doing this will override the default CakePHP layout which means you will loose the nice diagnostic section that tells you what queries are being sent to the database. You can get this back whenever you want by temporarily renaming the layout default.ctp to something like default.html

Now the layout is in place, create a file called articles_controller.php inside /app/controllers with the code below. The index function provides the logic for the homepage and tells it to retrieve all articles and the set command passes the data to the view.

<?php
class ArticlesController extends AppController {
	var $helpers = array ('Html','Form');
	var $name = 'Articles';
	function index() {
		$this->set('articles', $this->Article->find('all'));
	}
}
?>

Add in another function to deal with the detail view which gets all the data for an individual record.

function view($id = null) {
	$this->Article->id = $id;
	$this->set('article', $this->Article->read());
}

Now the logic is in place lets deal with the views. Create a file for the home page /app/views/articles/index.ctp.

<p>Blog Titles</p>
<?php foreach ($articles as $article): ?>
	<p><?php echo $this->Html->link($article['Article']['title'], array('controller' => 'articles', 'action' => 'view', $article['Article']['id'])); ?> by <?php echo $article['Article']['author']; ?></p>
	<p><?php echo $article['Article']['summary']; ?></p>
<?php endforeach; ?>

This is more complicated than the Fat-Free Framework version and begins to show where using a micro framework may save you a lot of typing.
The file for the details view should be saved as /app/views/articles/view.ctp.

<?php echo $article['Article']['title']?>
<p>Published: <?php echo $article['Article']['timestamp']?> by <?php echo $article['Article']['author']?></p>
<?php echo $article['Article']['content']?>
<p><a href='../'>Back to Homepage</a></p>

You can now check this all works by visiting /articles/index with a browser.

Step 6: Application Back End

To mimic the previous tutorial I did, the whole admin section needs to accessed under a /admin URL.
This is built into CakePHP and you can achieve it by editing /app/config/core.php and uncommenting

Configure::write('Routing.prefixes', array('admin'));

You can now add functions like admin_index() to a controller and these are only available when using an admin prefix in the URL like /admin/articles.
The admin home page is very similar to the normal home page with some extra links to add/edit/delete blog articles, so the controller method looks like this:

function admin_index() {
	$this->set('articles', $this->Article->find('all'));
}

And the view, saved as /app/views/articles/admin_index.ctp looks like this:

<h1>My Blog Administration</h1>
<p><?php echo $this->Html->link('Add Article', array('controller' => 'articles', 'action' => 'admin_add')); ?></p>
<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Date</th>
      <th>Author</th>
      <th colspan='2'>Actions</th>
    </tr>
  </thead>
  <tbody>
  <?php foreach ($articles as $article): ?>
    <tr>
      <td><?php echo $article['Article']['title']; ?></td>
      <td><?php echo $article['Article']['timestamp']; ?></td>
      <td><?php echo $article['Article']['author']; ?></td>
      <td><?php echo $this->Html->link('Edit', array('controller' => 'articles', 'action' => 'admin_edit', $article['Article']['id'])); ?></td>
      <td><?php echo $this->Html->link('Delete', array('controller' => 'articles', 'action' => 'admin_delete', $article['Article']['id'])); ?></td>
    </tr>
  <?php endforeach; ?>
  </tbody>
</table>

You can now access this via /admin/articles/, we’ll add the authentication in the next step.

CakePHP uses something called the Flash to communicate temporary messages to the user (don’t confuse with Adobe Flash).
The following code in a view will display the message if present:

<?php echo $this->Session->flash(); ?>

We will add this to the admin_index view under the H1 tag so that messages from the add/edit/delete actions can be displayed.
Also the following code is required to make the component available in the controller:

var $components = array('Session');

Note that built into CakePHP is a mechanism for confirming the delete of these records:

<?php echo $this->Html->link('Delete', array('action' => 'admin_delete', $article['Article']['id']), null, sprintf('Are you sure you want to delete # %s?', $article['Article']['id'])); ?>

This is starting to get complicated by the number of parameters you have to pass this method. I you use this every day then maybe it will become second nature, however for a beginner it can be a little daunting.

Add/Edit/Delete
To keep things simple we will create separate actions and views for the add/edit functionality – even though there is a lot of similarity between the two.

function admin_add() {
	if (!empty($this->data)) {
		if ($this->Article->save($this->data)) {
			$this->Session->setFlash('Your article has been saved.');
			$this->redirect(array('action' => 'index'));
		}
	}	
}
 
function admin_edit($id = null) {
	$this->Article->id = $id;
	if (empty($this->data)) {
		$this->data = $this->Article->read();
	} else {
		if ($this->Article->save($this->data)) {
			$this->Session->setFlash('Your article has been saved.');
			$this->redirect(array('action' => 'index'));
		}
	}
}
 
function admin_delete($id) {
	if ($this->Article->delete($id)) {
		$this->Session->setFlash('The article has been deleted.');
		$this->redirect(array('action' => 'index'));
	}
}

Delete doesn’t require a view as the action is confirmed by use of the flash. Edit is nearly the same as Add except for a few extra lines to retrieve the record first.
CakePHP uses a method called ‘save’ to save changes to the database, but behind the scenes this is going to be an insert or update. If Cake gets passed an id field via the submitted form it assumes you are going to be updating a record.

Thoughts at this stage – can clearly see the difference between a micro framework and a full size one now, Cake is more complex, there’s more to learn (much more documentation) and it takes longer.
Now the views of Add and Edit, these are nearly identical except that Edit includes the id field in a hidden variable.
views/articles/admin_add.ctp

<h1>Add</h1>
<?php echo $this->Form->create('Article');?>
	<?php
		echo $this->Form->input('title');
		echo $this->Form->input('summary');
		echo $this->Form->input('content');
		echo $this->Form->input('author');
	?>
<?php echo $this->Form->end('Submit');?>

views/articles/admin_edit.ctp

<h1>Edit</h1>
<?php echo $this->Form->create('Article');?>
	<?php
		echo $this->Form->input('title');
		echo $this->Form->input('summary');
		echo $this->Form->input('content');
		echo $this->Form->input('author');
		echo $this->Form->input('id', array('type' => 'hidden'));
	?>
<?php echo $this->Form->end('Submit');?>

You can now test this in a browser and should be able to add/edit and delete records.

Step 7: Using Middleware

To implement authentication from a database table we use CakePHPs Authentication Component.
This gets messy if you want to do it differently to the CakePHP way – that means we need to create a new database table with different column names and use that instead.

DROP TABLE users;
CREATE TABLE users (
    id INTEGER AUTO_INCREMENT,
    username CHAR(50),
    password CHAR(40),
    PRIMARY KEY (id)
);

This requires a users controller called users_controller.php

<?php
class UsersController extends AppController {
 
    var $name = 'Users';    
    var $components = array('Auth');
 
    function login() {
    }
 
    function logout() {
        $this->redirect($this->Auth->logout());
    }
}

And the following 2 changes to articles_controller.php, update the components variable to include the Auth component and add a new function.

var $components = array('Session','Auth');  //changed to add Auth
 
function beforeFilter() {
	$this->Auth->allow('index', 'view');
	$this->Auth->loginAction = array('admin' => false, 'controller' => 'users', 'action' => 'login');
}

The beforeFilter function changes some of the defaults for the Auth component, the first line allows the index and view actions to be executed without requiring authentication. Without the second line CakePHP will look for an action called admin_login so this tells it to simply use the login action.
Now we need a view to contain a login form.
views/users/login.ctp

<?php
    echo $this->Session->flash('auth');
    echo $this->Form->create('User');
    echo $this->Form->input('username');
    echo $this->Form->input('password');
    echo $this->Form->end('Login');
?>

As the passwords are stored hashed in the database (which is a good idea) we have a minor annoyance here – we can’t insert a row for the admin user into the database manually as we don’t know what the password field needs to hold.
We can get around this by returning to the CakePHP default layout which contains useful debug information and see what SQL it is using.
Rename app/views/layouts/default.ctp to default.htm
Visit /admin/articles and input your chosen username and password (I used ‘admin’ and ‘password’ for this tutorial) then click submit, in the diagnostic queries section you will see what SQL it tried to use, copy the hashed version of the password and manually insert this into the users database table with your username.
CakePHP default template showing SQL
Now you can rename default.htm back to default.ctp and get your own layout back.

Thoughts – if you know what you are doing it can be set up with very little coding and it works well.
However it took me about 45 mins to figure it out from the documentation on their website.

Step 8: Summary

I guess it should have been obvious from the start but using a bigger framework will lead to more complexity and a steeper learning curve. In exchange for that you gain greater flexibility which aren’t being taken advantage of with such a simple system developed here. A some really important ones for me are the form helper, validation, built in pagination (with sorting) and custom DataSources. You may be interested in my article on creating a DataSource to read from a RESTful API which lets you page through and sort the data just as if it were from MySQL.

Rather than putting the whole framework into a zip file, I’ve just included the files that I created with this tutorial, they all exist within the app folder of the CakePHP installation.

controllers
	articles_controller.php
	users_controller.php
views
	articles
		admin_add.ctp
		admin_edit.ctp
		admin_index.ctp
		index.ctp
		view.ctp
	layouts
		default.ctp
	users
		login.ctp

Download CakePHP app folder.

I’m going to try this very same blog with CodeIgniter next.