Tag: Programming

php-PDO-MySQL versus php-mysql

JonTheNiceGuy Leave a comment

PHP and MySQL was the duo that took dynamic web development from /cgi/formmail.cgi to… well, where we are today for me. My first proper experience of working with a web application, as opposed to a static web site with a few perl scripts bolted on was PHPNuke, and I’ve not looked back since.

Almost the first lines of PHP you learn to code are

<?php
mysql_connect("localhost", "root", "");
mysql_use_db("mysite");

Followed closely by mysql_query(), then mysql_fetch_array(), mysl_num_rows(), and if the rest of us are lucky, mysql_real_escape_string().

So you see, for me, it’s really weird to find that even though this is what we’re teaching everyone to use, this weird incantation where we have to wrap every query up in a cotton wool ball of SQL injection protection… there’s an easier way where you don’t have to wrapper everything, which, even better, is more efficient at your SQL server and, if you’ve made the transition to using Classes in your code, will give you an object per-result rather than an array.

Let’s look at some examples of code, and see how we would transition things.

Here’s some rather unoriginal code to get the contents of a user table.

<?php
$db = mysql_connect("localhost", "root", "");
mysql_use_db("mysite");
$sql = "select userid, realname, lastloggedin from users "
     . "where username = '" . mysql_real_escape_string($_POST['username'])
     . "' and password = '" . mysql_real_escape_string($_POST['password']) . "'";
$query = mysql_query($sql);
$data = false;
if (mysql_num_rows($query) == 1) {
  $data = mysql_fetch_array($query);
  echo "Hello {$data['realname']}, your userid is {$data['userid']} and "
     . "you last logged in at " . date("H:i:s", strtotime($data['lastloggedin']))
     . " on " . date("d-M-Y" , strtotime($data['lastloggedin']));
}

There, now there’s not much wrong with that (ignoring the security hole of having your passwords in cleartext) is there? All pretty readable, right? Well… what happens if your database is down, how do you handle that in some kind of sensible way? You need to wrap some code around the mysql_connect to show it actually connected. What about if someone dropped the database by mistake, or you connected to the wrong database server and that table wasn’t there, now you’re wrappering the use_db line too. Did you make a typo in the SQL? Are you sure you didn’t miss a mysql_real_escape_string in there somewhere…

Here’s the same thing in PDO, protected against all (again, except the cleartext password) of those things.

<?php
try {
  $db = new PDO("mysql:host=localhost;dbname=mysite", "root", "");
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "select userid, realname, lastloggedin from users where username = ? and password = ?";
  $query = $db->prepare($sql);
  $query->execute(array($_POST['username'], $_POST['password']));
  $data = $query->fetch();
  if ($data != false) {
    echo "Hello {$data['realname']}, your userid is {$data['userid']} and "
       . "you last logged in at " . date("H:i:s", strtotime($data['lastloggedin']))
       . " on " . date("d-M-Y" , strtotime($data['lastloggedin']));
  }
} catch (PDOException $e) {
  error_log("User unable to login: " . $e->getMessage());
}

So, let’s look at what transforms this into something much more interesting: $query->fetchObject();

<?php
try {
  $db = new PDO("mysql:host=localhost;dbname=mysite", "root", "");
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "select userid, realname, lastloggedin, username, password " 
       . "from users where username = ? and password = ?";
  $query = $db->prepare($sql);
  $query->execute(array($_POST['username'], $_POST['password']));
  $data = $query->fetchObject('user');
  if ($data != false) {
    echo $data;
  }
} catch (PDOException $e) {
  error_log("User unable to login: " . $e->getMessage());
}

class user
{
  // Columns from the database
  protected $userid = null;
  protected $realname = null;
  protected $lastloggedin = null;
  protected $username = null;
  protected $password = null;
  // Processed Data
  protected $transformed_lastloggedin = null

  public function __construct()
  {
    if ($this->lastloggedin != null) {
      $this->transformed_lastloggedin = date("H:i:s", strtotime($this->lastloggedin)) 
                             . " on " . date("d-M-Y" , strtotime($this->lastloggedin);
    }
  }

  public function toString()
  {
    echo "Hello {$this->realname}, your userid is {$this->userid} and " 
       . "you last logged in at {$this->transformed_lastloggedin}";
  }
}

I’m starting to stretch the analogy here a little, but the important things here are:

  1. The __construct() function of the class is run after the data is inserted into it, meaning you can post-process any data you receive from the database without needing any new functions. Arguably, you could make the construct function accept the same values you’d receive from the database, and process those in the same way.
  2. The toString() function means you can mangle the content you want to output, without having to apparently run another function on the class you’ve just created.
  3. By using classes you get to do wonderful things like Unit Testing (this is a topic to follow), have automatic code documentation (err… to follow), reduce code duplication by extending classes and check on that, using “Copy and Paste Detection”.

Dependency Hell – and it’s not my package manager to blame

JonTheNiceGuy 1 Comment

I’m writing a web service for conferences. I’ve been writing it, on and off for 3 years, and I think it would be fair to say the coding reflects my learning over those three years. The script is written in PHP. It has a MySQL backend. It has undergone a lot of changes.

In the beginning, was a concept. The concept was to have a digital timetable. One where you could register your interest in a talk, and the busiest talks got the biggest rooms. It should use any tech the user had to use the system including SMS. It would not expect you to have a user name and password combo (heaven forbid!), but would use OpenID.

The concept was implemented and demo’d at an event 2 years ago, and a friend [1] asked where the API was. “API?” I replied, feeling somewhat foolish, “It doesn’t have an API“.

[1] Lorna Jane Bowman (neé Mitchell aka @lornajane)

I realised the foolishness of my coding when discussing this issue [2]. “It’s dead simple, just create another view which returns just JSON” said someone. “View? I don’t know what you mean“… “Model, View, Controller? MVC – it’s a pretty common pattern”. “Oh no” I replied, “my project has inline PHP. It just seemed simpler that way.” “Well, how about you add a toString() function to your classes, and use that to render the output?” “Classes. Another thing my project doesn’t have. Sorry. Can’t do that either.”

[2] With Lorna again, and adding Katherine Reeve (aka @BinaryKitten)

Did you ever get that slightly sinking feeling that maybe you’re making a fool of yourself?

“Well, there are lots of benefits to Classes in PHP, not least of which that you can use PHP CodeSniffer to enforce your coding standards” I start to laugh a little at this point “and you can use PHP Documenter to create all your developer documentation” which shut me right back up again “you can use PDO to create your objects from your database results” wow… mind blown – I’ve jumped on this little trick of a pony… “and of course, you can’t really do unit testing without classes”. Uh oh. “What’s unit testing?” “It’s a set of automated tests you can run against your code every time you commit to your version control software” whew! I’m using that at least! “to make sure that the code you’re committing hasn’t broken anything”.

Fast forward to this week, and I asked on Facebook whether anyone could teach me how to do Unit Testing. See, I’ve managed to cobble together my own MVC – I have classes called “Object_” which are the Models, the controller is my routing script – otherwise known as index.php, and then the Views are templates in Smarty, or just a json_encode((array) $object) [3] for my API. I have my own set of tests – not unit tests, it’s a script called “Test.sh” which runs PHP against the file (to make sure that the pages don’t have brackets missing or similar), then runs PHP Code Sniffer against it, and finally, if all the files are OK, it then runs PHPDoc against the whole mass of it.

[3] As the Apple iPhone/iPad adverts say – some sequences shortened

So, one of the books suggested to me, again by the lovely Lorna Jane, was The Grumpy Programmer’s Guide To Building Testable PHP Applications which mentioned that it’s much easier to do unit testing if you set up dependency injection. Note, I’m still not yet doing Unit testing here.

Woah. What the hell is Dependency Injection? Well, fortunately, there were code examples in that book. Oh boy, were there code examples. So let’s look through this idea. Instead of writing

$stuff = new MyStuff();

class MyStuff()
{
    function construct()
    {
        $this->db = mysql_connect('localhost', 'username', 'password');
        mysql_use_database('production', $this->db);
    }
}

You could instead write this:

$db = mysql_connect('localhost', 'username', 'password');
mysql_use_database('production', $this->db);
$stuff = new MyStuff($db);

class MyStuff()
{
    function construct($db = null)
    {
        $this->db = $db;
    }
}

So, this now means that in your testing framework, you could pass it a non-production database, or a testing database, or, well, anything that relies on something outside your script.

I did a bit of digging around to find some other examples of dependency injection code that might be a bit easier to use, which is to say, something so I don’t need to amend all my constructor functions.

I found this slideshare from a talk at PHP Barcelona about dependency injection which says you can do dependency injection like this:

$thing = new MyClass($dependency);

OR

$thing = new MyClass();
 $thing->setDependency($dependency);

OR

$thing = new MyClass();
 $thing->dependency = $dependency;

but somewhat weirdly, it also says that you can create a container class which holds your dependencies, and refer to that later – and that this isn’t a singleton. Sadly, I didn’t understand all that code fully (and have gone on to file a bug for the PHP documentation for the functions I didn’t understand to help people who follow behind with it!), but (and I’ve copied this verbatim from the slideshare) essentially, it looks like this:

class Container { protected $values = array(); function __set($id,$value) {  $this->values[$id] = $value;  }  function __get($id) {  if (!isset($this->values[$id])) {  throw new InvalidArgumentException(sprintf('Value "%s" is not defined.', $id));  }  if (is_callable($this->values[$id])) {  return $this->values[$id]($this);  } else { return $this->values[$id];  }  }  function asShared ($callable) {  return function($c) use ($callable) {  static $object; if (is_null($object)) {  $object=$callable($c);  }  return $object; }; } } $container = new Container(); $container->session_name='SESSION_ID'; $container->storage_class='SessionStorage'; $container->user = $container->asShared( function($c) {  return new User($c->storage);  } ); $container->storage = $container->asShared(  function($c) {  return new $c->storage_class($c->session_name); } );

Now, I have to be honest, this confuses the hell out of me. How on earth do I use this in my code? I’ve been doing this in my code thus far:

class Object_User{
  protected $intUserID    = null; // Obtained by the getCurrentUser() function
  protected $strUsername  = null;
  protected $hashPassword = null; // More of these, depending on the SQL

  function getCurrentUser() { // Called as $user = Base_User::getCurrentUser();
 $objCache  = Base_Cache::getHandler(); // A singleton to "cache" any data we've pulled to save getting it repeatedly
    if (
          isset($objCache->arrCache['Object_User']['current'])
          && $objCache->arrCache['Object_User']['current'] != false
        ) {
      return $objCache->arrCache['Object_User']['current'];
    }
    $arrRequest  = Base_Request::getRequest(); // Returns array of parsed request data
    $objDatabase = Base_Database::getConnection(); // Returns a PDO object

    $sql = "SELECT * FROM users WHERE strUsername = ? and hashPassword = ?";
    $query = $db->prepare($sql);
    $query->execute(array($request['username'], $request['password'])); $result = $query->fetchObject('Object_User');
    if ($result != false) {
      $objCache->arrCache['Object_User']['id'][$result->getVal('intUserID')] = $result;
      $objCache->arrCache['Object_User']['current'] = $result;
    }
    return $result;
  }

  function getVal($key) { // Return protected variables
    if (!isset($this->$key)) {
      return false;
    }
    return $this->$key;
  }
}

I know that singleton methods are considered “Bad” because they’re (apparently) difficult to unit test, but I would have thought that it would have been pretty straightforward to create a singleton class which holds all the dependencies (see following)

class Base_Dependencies
{
  protected static $handler = null; protected $arrDependencies = array();   protected function GetHandler() {
    if (self::$handler == null) { self::$handler = new self(); }
    return self::$handler;
  }

  function set($key, $dependency) {
    $handler = self::GetHandler();
    $handler->arrDependencies[$key] = $dependency;
  }

  function get($key) {
    $handler = self::GetHandler();
    if (isset($handler->arrDependencies[$key])) {
      return $handler->arrDependencies[$key];
    } else {
      return false;
    }
  }

  function unset($key) { // Only used for Unit Testing, I would imagine
    $handler = self::GetHandler();
    if (isset($handler->arrDependencies[$key])) {
      unset($handler->arrDependencies[$key]);
    }
  }
}

Doing it this way means I can, from, for example, my database class, which is currently a singleton, say instead:

function GetConnection() {
  $db = Base_Dependencies::get("Database");
  if ($db != false) {
    return $db;
  }
  $config = Base_Config::getAllConfig();
  $db = new PDO($config['DSN'], $config['DB_User'], $config['DB_Pass']);
  Base_Dependencies::set("Database", $db);
  return $db;
}

Is this wrong? Is this just not best practice? Given the above, how can I fix my dependencies in such a way that the poor schmuck who wants to commit a patch can figure out what they’re sending? Or do I just need to fix how my head works with this stuff? If it’s the latter, can someone provide some samples of what I’m doing wrong?

Thanks for reading this mammoth post!

Using Amazon an EC2 instance as an off-site CronJob

JonTheNiceGuy Leave a comment

I run the CCHits.net website, and part of the day-to-day tasks that running the site entailed was the daily show creation which involved creating some text-to-speech audio for insertion into the podcasts. As I run the website on shared hosting, to which I didn’t have full access to the OS, I couldn’t just install Festival on the platform, and for whatever reason (I never did figure out what went wrong), I couldn’t build Festival to run on the shared host either.

Until “The Big Rewrite” (the capitals are totally worth it), I’d been doing the text-to-speech on my home server, but frankly, I’m on DSL, which meant I needed to set up Dynamic DNS, I had to be sure the server was always up (and it wasn’t!), etc, etc, etc. While I was looking into why I couldn’t get Festival to build, someone said “Well, why not just use EC2 to do it”.

After nearly a year of faffing about trying to make the …….. stupid thing work (as is testified by the draft in this very blog called “How I built my Audio Stack for CCHits”), I finally decided to spin up an EC2 instance for just this one task.

Now, I’m not the greenest guy on the block – hell, I drive 45 minutes into work each day, but I figured, why keep an EC2 instance running all the time, when I only need it for less than 20 minutes each day, so I did some reading, and found a post on making EC2 do the hard work for you, using the scalable computing APIs, but frankly, all I actually needed was the code to make it spin up, run the task and shut down again, especially as with using his methods, I’d have needed to either create an AMI image, or download the festival voice files each time… at around 100Mb. Not good. I ended up using the stuff I did know (bash scripting, cron tasks) and ditching the stuff I didn’t (AMI files, scalable computing API). I may revisit this later to do it the way he said instead. We’ll see :)

So, here’s the crack.

Create your EC2 image. It doesn’t need to do anything fancy yet – just boot up and keep running. You’ll do some tweaks later. Make a note of the instance number – it’ll probably start i- and then 8 or so hexedecimal digits, like this: i-12345678.

On your shared web host, download the EC2 API tools. According to this page, the API tools are available from here. The first link of those two is unlikely to change for a while, the second, maybe more so. You’ll need to make sure you have Java installed on that web host.

Once you’ve got the tools, you’ll need to create an X.509 certificate and key pair. See this page to find instructions. It was pretty straightforward.

So, you now have in, let’s say for sake of argument, your home directory:

  • /home/user/ec2-api-tools-x.x.x.x # The directory containing all the EC2 API tools
  • /home/user/ec2-keys/pk-{32[A-Za-z1-0]}.pem
  • /home/user/ec2-keys/cert-{32[A-Za-z1-0]}.pem

Also, you have java in /usr/bin/java.

Create the following script also in /home/user/ec2-api-tools-x.x.x.x – I called it ec2-wrapper.

#!/bin/bash
export EC2_HOME=/home/user/ec2-api-tools-x.x.x.x
export JAVA_HOME=/usr
export EC2_KEY=/home/user/ec2-keys/pk-{32[A-Za-z1-0]}.pem
export EC2_CERT=/home/user/ec2-keys/cert-{32[A-Za-z1-0]}.pem
${EC2_HOME}/bin/$* -K ${EC2_KEY} -C ${EC2_CERT

Obviously, you should change your paths to match what you have. What this script does is to add the X.509 certs to every EC2 request, plus adds the appropriate java and EC2_HOME paths to the script before running it.

I set up a CRON job (using crontab -e) to schedule the regular startup of the instance. Here’s the entry from my crontab:

#M   H  DoM Mth DoW  Command (Regular Crontab columns)
30   1   *   *   *   /home/user/ec2-api-tools-x.x.x.x/ec2-wrapper ec2-start-instances i-12345678
30   2   *   *   *   /home/user/ec2-api-tools-x.x.x.x/ec2-wrapper ec2-stop-instances i-12345678

So, this runs the start task at 30 minutes past 1am, local server time, and the stop task at 30 minutes past 2am. The second one there is just to be on the safe side, as we’ll try to shut down the box once it’s finished processing anyway. This way, the maximum time you’ll be billed for is 1 hour of time each day.

I then logged into my EC2 machine, and created, then tweaked the script from the earlier blog post (the scalable computing one).

#!/bin/bash -x
exec > >(tee /var/log/user-data.log|logger -t user-data -s 2>/dev/console) 2>&1
#
# This code is licensed under an Apache License - for the original 
# code and license, please see the footer of this script.
#
# !!!IMPORTANT!!!
# Edit this file and change this next line to your own email address:
#

EMAIL=user@example.com

# Get some information about the running instance
instance_id=$(wget -qO- instance-data/latest/meta-data/instance-id)
public_hostname=$(wget -qO- instance-data/latest/meta-data/public-hostname)

# Wait 5 minutes in case we want to get in to stop this from doing stuff
Sleep 300

if [ -f /home/ubuntu/donotrun ]
then
  exit 0
fi

# Send status email
/usr/sbin/sendmail -oi -t -f $EMAIL <<EOM
From: $EMAIL
To: $EMAIL
Subject: Running EC2 Scripts

== Making sure everything is up to date ==

`cd /home/ubuntu/website-rewrite && su -c "git pull" ubuntu 2>&1`

== Running the scheduled task ==

`php /home/ubuntu/website-rewrite/scheduled_task.php`

== Notes ==

This email was generated on the EC2 instance: $instance_id

If the instance is still running, you can monitor the output of this
job using a command like:

  ssh ubuntu@$public_hostname tail -1000f /var/log/user-data.log

EOM

# Give the email some time to be queued and delivered
sleep 300 # 5 minutes

if [ -f /home/ubuntu/shutdownwhendone ]
then
  shutdown -h now
fi

exit 0

########################################################################
# For more information about the original version of this code see:
#   http://alestic.com/2011/11/ec2-schedule-instance
# The original code and its license are available on github:
#   https://github.com/alestic/demo-ec2-schedule-instance
########################################################################

So, with that, I get a script which runs on schedule, on an EC2 platform, I get a confirmation e-mail it’s running. It shuts itself down, and hopefully, keeps on trucking :)

Transfer my files using SFTP and SCP only?

JonTheNiceGuy Leave a comment

A colleague today asked for some guidance around setting up an SFTP and SCP only account on a RedHat based Linux machine.

I sent him a collection of links, including one to the CopSSH project, and he implemented the code on that link, but then struggled when it didn’t work.

Aside from the fact the shell wasn’t copied into /etc/shells (which wasn’t disastrous, but did mean we couldn’t reuse it again later), it was still returning an error on each load.

Doing some digging into it, and running some debugging, I noticed that pscp (the PuTTY SCP) tool uses the SFTP subsystem rather than the SCP command to upload files, so we need to also check that the SFTP server hasn’t been called, instead of the SCP command, and also the SCP command needs to be corrected.

Here follows a script, complete with comments. Personally, I’d save this in /bin/sftponly, created and owned by root, and set to permissions 755 (rwxr-xr-x). Then, set the shell to this for each user which needs to do SFTP or SCP only.

#!/bin/bash
# Based on code from http://www.itefix.no/i2/node/12366
# Amended by Jon Spriggs (jon@sprig.gs)
# Last update at 2011-09-16

# Push the whole received command into a variable
tests=`echo $*`

# Set up a state handler as false
isvalid=0

# Test for the SFTP handler.
# The 0:36 values are the start character and length of the handler string.
if [ "${tests:0:36}" == "-c /usr/libexec/openssh/sftp-server" ]; then
  # Set the state handler to true
  isvalid=1
  # Configure the handling service
  use=/usr/libexec/openssh/sftp-server
fi

# Test for the SCP handler.
if [ "${tests:0:6}" == "-c scp" ]; then
  # Set the state handler to true
  isvalid=1
  # Configure the handling service
  use=/usr/bin/scp
fi

# If the state handler is set to false (0), exit with an error message.
if [ "$isvalid" == "0" ]; then
  echo "SCP only!"
  exit 1
fi

# Run the handler
exec $use $*

Getting my head around coding Object Orientated PHP

JonTheNiceGuy Leave a comment

I’ve been writing two open source projects over the last couple of years. My code has never really been particularly great, but I’ve been trying to learn how to improve my code and over the last few months, I’ve been really trying to polish up my coding skills.

A few months back, I attended a series of fantastic sessions at PHPNW about using Unit Testing, PHP CodeSniffer and phpDocumentor, and how these can be incorporated into Object Orientated code (or in fact, requiring Object Orientated code to implement them).

So, I went back into my main projects and started to look at how I could fix the code to start adopting these tools.

So, the first thing I needed to do was to start thinking about the structure. CCHits.net (which is the “big project” I’ve been working on recently) has several chunks of data, and these are:

User
Track
Artist
Show
ShowTrack
Vote
Chart

Each of these have been broken down into three “things” – The Object itself, a “Broker” which finds all the relevant objects, and a class to create new items, so let’s start with a user class. We’ll define a few properties in the initial class creation.

class UserObject
{
    protected $intUserID = 0;
    protected $strOpenID = "";
    protected $strCookieID = "";
    protected $sha1Pass = "";
    protected $isAuthorized = 0;
    protected $isUploader = 0;
    protected $isAdmin = 0;
    protected $datLastSeen = "";
}

By setting these as protected, it stops me from directly setting or accessing these variables from outside of the class – instead I want to do it from a function, so let’s add those in (I’ll just do one – assume these will be copied on to the rest of the values).

class UserObject
{
    protected $strOpenID = "";

    function set_strOpenID($strOpenID = "") {
        if ($strOpenID != $this->strOpenID) {
            $this->strOpenID = $strOpenID;
        }
    }

    function get_strOpenID() {
        return $this->strOpenID;
    }
}

In the set_ functions, we already do a little bit of error checking here (is the value already set to this?) but we could add other things like, on the integer items, is it actually an integer, with the boolean values ($is[SOMETHING]), make sure it’s set to 1 or 0 (or true/false).

Now, let’s add some documentation to this:

/**
 * CCHits.net is a website designed to promote Creative Commons Music,
 * the artists who produce it and anyone or anywhere that plays it.
 * These files are used to generate the site.
 *
 * PHP version 5
 *
 * @category Default
 * @package  CCHitsClass
 * @author   Jon Spriggs 
 * @license  http://www.gnu.org/licenses/agpl.html AGPLv3
 * @link     http://cchits.net Actual web service
 * @link     http://code.cchits.net Developers Web Site
 * @link     http://gitorious.net/cchits-net Version Control Service
 */
/**
 * This class deals with user objects
 *
 * @category Default
 * @package  Objects
 * @author   Jon Spriggs 
 * @license  http://www.gnu.org/licenses/agpl.html AGPLv3
 * @link     http://cchits.net Actual web service
 * @link     http://code.cchits.net Developers Web Site
 * @link     http://gitorious.net/cchits-net Version Control Service
 */
class UserObject
{
    /**
     * This is the Setter function for the strOpenID value
     *
     * @param string $strOpenID The value to set
     *
     * @return void There is no response from this function
     */
    function set_strOpenID($strOpenID = "") {
        // Do stuff
    }
}

So, let’s make it do stuff with the database. Firstly, we need to set up a connection to the database. I’ll be using PDO (PHP Database Object, I think) to set up the connection (I’ll show why in a minute).

So, here’s another class – Database, this time it’s using a singleton factory (which is to say, while it may exist many times in the code, it’ll only ever have one connection open at once) – note it’s got hard-coded authentication details here – this isn’t how it actually is in my code, but this way it’s a bit more understandable!

class Database
{
    protected static $handler = null;
    protected $db = null;

    /**
     * This function creates or returns an instance of this class.
     *
     * @return object $handler The Handler object
     */
    private static function getHandler()
    {
        if (self::$handler == null) {
            self::$handler = new self();
        }
        return self::$handler;
    }

    /**
     * This creates or returns the database object - depending on RO/RW requirements.
     *
     * @return object A PDO instance for the query.
     */
    public function getConnection()
    {
        $self = self::getHandler();
        try {
            $self->rw_db = new PDO('mysql:host=localhost;dbname=cchits', 'cchits', 'cchits', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
            return $self->rw_db;
        } catch (Exception $e) {
            echo "Error connecting: " . $e->getMessage();
            die();
        }
    }
}

So, now let’s create yet another class, called GenericObject. This will be re-used in all of the “Object” classes to perform our main database calls. Try to move as much code up to your highest level objects – so you could have a “validateBoolean($value)” function that is used any time you set or get a boolean value here… we even mentioned something like this above!

class GenericObject
{
    protected $arrDBItems = array();
    protected $strDBTable = "";
    protected $strDBKeyCol = "";
    protected $arrChanges = array();

    /**
     * Commit any changes to the database
     *
     * @return boolean Status of the write action
     */
    function write()
    {
        if (count($this->arrChanges) > 0) {
            $sql = '';
            $strDBKeyCol = $this->strDBKeyCol;
            $values[$strDBKeyCol] = $this->$strDBKeyCol;
            $values = array();
            foreach ($this->arrChanges as $change) {
                if ($sql != '') {
                    $sql .= ", ";
                }
                if (isset($this->arrDBItems[$change])) {
                    $sql .= "$change = :$change";
                    $values[$change] = $this->$change;
                }
            }
            $full_sql = "UPDATE {$this->strDBTable} SET $sql WHERE {$this->strDBKeyCol} = :{$this->strDBKeyCol}";
            try {
                $db = Database::getConnection();
                $query = $db->prepare($full_sql);
                $query->execute($values);
                return true;
            } catch(Exception $e) {
                return false;
            }
        }
    }

    /**
     * Create the object
     *
     * @return boolean status of the create operation
     */
    protected function create()
    {
        $keys = '';
        $key_place = '';
        foreach ($this->arrDBItems as $field_name=>$dummy) {
            if ($keys != '') {
                $keys .= ', ';
                $key_place .= ', ';
            }
            $keys .= $field_name;
            $key_place .= ":$field_name";
            $values[$field_name] = $this->$field_name;
        }
        $full_sql = "INSERT INTO {$this->strDBTable} ($keys) VALUES ($key_place)";
        try {
            $db = Database::getConnection();
            $query = $db->prepare($full_sql);
            $query->execute($values);
            if ($this->strDBKeyCol != '') {
                $key = $this->strDBKeyCol;
                $this->$key = $query->lastInsertId();
            }
            return true;
        } catch(Exception $e) {
            return false;
        }
    }

    /**
     * Return an array of the collected or created data.
     *
     * @return array A mixed array of these items
     */
    function getSelf()
    {
        if ($this->strDBKeyCol != '') {
            $key = $this->strDBKeyCol;
            $return[$key] = $this->$key;
        }
        foreach ($this->arrDBItems as $key=>$dummy) {
            $return[$key] = $this->$key;
        }
        return $return;
    }
}

Any protected functions or variables are only accessible to the class itself or classes which have been extended from it (referred to as a child class). We need to make some changes to our UserObject to make use of these new functions:

class UserObject extends GenericObject
{
    // Inherited Properties
    protected $arrDBItems = array(
        'strOpenID'=>true,
        'strCookieID'=>true,
        'sha1Pass'=>true,
        'isAuthorized'=>true,
        'isUploader'=>true,
        'isAdmin'=>true,
        'datLastSeen'=>true
    );
    protected $strDBTable = "users";
    protected $strDBKeyCol = "intUserID";
    // Local Properties
    protected $intUserID = 0;
    protected $strOpenID = "";
    protected $strCookieID = "";
    protected $sha1Pass = "";
    protected $isAuthorized = 0;
    protected $isUploader = 0;
    protected $isAdmin = 0;
    protected $datLastSeen = "";

    function set_strOpenID($strOpenID = "") {
        if ($this->strOpenID != $strOpenID) {
            $this->strOpenID = $strOpenID;
            $this->arrChanges[] = 'strOpenID';
        }
    }
}

Notice I’ve stripped the “phpDoc” style comments from this re-iteration to save some space! In the real code, they still exist.

Now we have an object we can work with, let’s extend it further. It’s probably not best practice, but I find it much more convenient to create new objects by extending the UserObject into a new class called NewUserObject. Notice once we’ve set our database items, we run the create(); function, which was previously defined in the GenericObject class.

class NewUserObject extends UserObject
{
    public function __construct($data = "")
    {
        if (strpos($data, "http://") !== false or strpos($data, "https://") !== false) {
            $this->set_strOpenID($data);
        } elseif ($data != "") {
            $this->set_sha1Pass($data);
        } else {
            if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
                $cookie_string = $_SERVER['HTTP_X_FORWARDED_FOR'];
            } else {
                $cookie_string = $_SERVER['REMOTE_ADDR'];
            }
            $cookie_string .= $_SERVER['HTTP_USER_AGENT'];
            $cookie_string .= $_SERVER['HTTP_ACCEPT_LANGUAGE'];
            $cookie_string .= $_SERVER['HTTP_ACCEPT_ENCODING'];
            $cookie_string .= $_SERVER['HTTP_ACCEPT_CHARSET'];
            $this->set_strCookieID(sha1sum($cookie_string));
        }
        $this->datLastSeen = date("Y-m-d H:i:s");
        $_SESSION['cookie'] = sha1($cookie_string);
        return $this->create();
    }
}

Using the code $user = new NewUserObject($login_string); we can create a new user, but how about retrieving it.

This is where the reason I’m loving PDO comes into play. See, before PDO, when you did a database request, you might have had something like this:

$db = mysql_connect("localhost", "root", "");
if ($db == false) {
    die ("Failed to connect to the Database Server");
}
if (! mysql_select_db("database")) {
    die ("Failed to select the database");
}
$intUserID = mysql_real_escape_string($_GET['intUserID']);
$sql = "SELECT * FROM users WHERE intUserID = '$intUserID' LIMIT 1";
$qry = mysql_query($sql);
if (mysql_errno() > 0) {
    echo "Failed to make Database Call: " . mysql_error();
} else {
    if (mysql_num_rows($qry) == 0) {
        echo "Failed to retrieve record 1";
    } else {
        $row = mysql_fetch_array($qry);
    }
}

Now, let’s assume you’re looking for a few users? You need to do more of those mysql_real_escape_strings and mysql_num_rows() and mysql_fetch_array()s to get your data out – it’s far from clean and clear code.

How about this instead?

try {
    $db = Database::getConnection();
    $sql = "SELECT * FROM users WHERE intUserID = ? LIMIT 1";
    $query = $db->prepare($sql);
    $query->execute(array($_GET['intUserID']));
    $row = $query->fetch();
} catch(Exception $e) {
    echo $e;
    $row = false;
}

The most complicated bit here is that you’re having to prepare your SQL query and then tell it what to get. The reason we do that is that PDO will automatically ensure that anything being passed to it using the ? is sanitized before passing it into the query. If you look back at the GenericObject class we created earlier, it uses something like this there too, except there (if you work out what it’s doing) it prepares something like INSERT INTO users (intUserID) VALUES (:intUserID); and then executes it with the values like this: array(‘:intUserID’=>1) and with this you can have some very complex statements. Other code you might spot while mooching through CCHits (if you decide to) looks like this:

$sql = "UPDATE IGNORE votes SET intTrackID = ? WHERE intTrackID = ?; DELETE FROM votes WHERE intTrackID = ?";
$query = $db->prepare($sql);
$query->execute(array($intNewTrackID, $intOldTrackID, $intOldTrackID));

By wrapping it all up in a try/catch block, you can get your error dumped in one place, including a stack trace (showing where the issue turned up). You don’t need to check for mysql_num_rows – if the row doesn’t exist, it’ll just return a false. Sweet.

Where it gets REALLY nice, is that if you swap $row = $query->fetch() with $object = $query->fetchObject(‘UserObject’); and it’ll create the object for you!

So, now, with the function getUser (visible at https://gitorious.org/cchits-net/website-rewrite/blobs/master/CLASSES/class_UserBroker.php) it’ll try to return a UserObject based on whether they’re using OpenID, Username/Password or just browsing with a cookie… and if it can’t, it’ll create you a new user (based on the above criteria) and then return you that UserObject.

The last thing to add, is that I wrapped up all the nice phpDocumentor, PHP CodeSniffer functions, plus wrote a script to check for missing or incorrectly labelled functions across a suite of classes. These sit in https://gitorious.org/cchits-net/website-rewrite/trees/master/TESTS if you want to take a look around :)

EDIT 2011-08-25: Correcting some errors in the code, and to adjust formatting slightly.
EDIT 2012-05-05: Changed category, removed the duplicated title in the top line, removed some whitespace.

Experimenting with Tiny Core Linux on QEMU

JonTheNiceGuy 3 Comments

In response to a post on the Ubuntu UK Loco mailing list today, I thought the perfect way to produce a cross-platform, stable web server… would be to create a QEMU bootable image of Tiny Core.

So, the first thing I did was to download a Tiny Core image. This I obtained from the Tiny Core Download Page. I then created a 512MB disk image to store my packages on.

qemu-img create tinycore-tce.img 512M

After a bit of experimenting, I ended up with this command to boot TinyCore. At the moment, it’s relatively cross-platform, but will need some tweaking to get to the point where I can do anything with it…

qemu -hda tinycore-tce.img -m 512 -cdrom tinycore-current.iso -boot d -net nic -net user,hostfwd=tcp:127.0.0.1:8008-:80 -vnc 127.0.0.1:0 -daemonize

So, let’s explain some of those options.

-hda tinycore-tce.img

This means, use the image we created before, and install it in /dev/hda on the visualised machine.

-cdrom tinycore-current.iso -boot d

Create a virtual CD using the ISO file we downloaded. Boot from the CD rather than any other media.

-m 512

Allocate the virtual machine 512Mb RAM.

-net nic -net user,hostfwd=tcp:127.0.0.1:8008-:80

Create a virtual network interface in “UserMode”, and port forward from port 80 on the dynamically allocated IP address on the virtual machine to port 127.0.0.1:8008 (which means it’s only accessible from the host machine, not from any other machine on the network)

-vnc 127.0.0.1:0 -daemonize

This makes the service “headless” – basically meaning it won’t show itself, or need a terminal window open to keep it running. If you want to interact with the system, you need to VNC to localhost. If you’ve already got a VNC service running on the machine (for example, if you’re using Vino under Ubuntu), increment the :0 to something else – I used :2, but you could use anything.

At the moment, because I’ve not had much opportunity to tweak TinyCore’s boot process, it won’t start running automatically (you have to tell it what to start when it boots), nor will it start any of the services I want from it, I’ve had to use VNC to connect to it. I’ll be trying out more things with this over the next few days, and I’ll update this as I go.

Also, I’ve not tried using the Windows qemu packages to make sure the same options all work with that system, and I’ll probably be looking into using the smb switch for the -net user option, so that as much of the data is clearly accessible without needing to drop in to the qemu session just to upload a few photos into the system. I guess we’ll see :)

Watching for file changes on a shared linux web server

JonTheNiceGuy Leave a comment

$NEWPROJECT has a script which runs daily to produce a file which will be available for download, but aside from that one expected daily task, there shouldn’t be any unexpected changes to the content on the website.

As I’m hosting this on a shared webhost, I can’t install Tripwire or anything like that, and to be honest, for what I’m using it for, I probably don’t need it. So, instead, I wrote my own really simple file change monitor which runs as a CronJob.

Here’s the code:

#! /bin/bash
# This file is called scan.sh
function sha512sum_files() {
find $HOME/$DIR/* -type f -exec sha512sum '{}' \; >> $SCAN_ROOT/current_status
}
SCAN_ROOT=$HOME/scan
mv $SCAN_ROOT/current_status $SCAN_ROOT/old_status
for DIR in site_root media/[A-Za-z]*
do
sha512sum_files
done
diff -U 0 $SCAN_ROOT/old_status $SCAN_ROOT/current_status

And here’s my crontab:


MAILTO="my.email@add.ress"
# Minute Hour Day of Month Month Day of Week Command
# (0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)
0,15,30,45 * * * * /home/siteuser/scan/scan.sh

And lastly, a sample of the output

--- /home/siteuser/scan/old_status 2010-10-25 14:30:03.000000000 -0700
+++ /home/siteuser/scan/current_status 2010-10-25 14:45:06.000000000 -0700
@@ -4 +4 @@
-baeb2692403619398b44a510e8ca0d49db717d1ff7e08bf1e210c260e04630606e9be2a3aa80f7db3d451e754e189d4578ec7b87db65e6729697c735713ee5ed /home/siteuser/site_root/LIBRARIES/library.php
+c4d739b3e0a778009e0d53315085d75cf8380ac431667c31b23e4b24d4db273dfc98ffad6842a1e5f59d6ea84c33ecc73bed1437e6105475fefd3f3a966de118 /home/siteuser/site_root/LIBRARIES/library.php
@@ -71 +71 @@
-88ddd746d70073183c291fa7da747d7318caa697ace37911db55afce707cd1634f213f340bb4870f1194c48292f846adaf006ad61b4ff1cb245972c26962b42d /home/siteuser/site_root/api.php
+d79e8a6e6c3db39e07c22e7b7485050007fd265ad7e9bdda728866f65638a8aa534f8cb51121a68e9287f384e8694a968b48d840d37bcd805c117ff871e7c618 /home/siteuser/site_root/api.php

While this isn’t the most technically sound way (I’m sure) of checking for file changes, at least it gives me some idea (to within 15 minutes or so) of what files have been changed, so gives me a time to start hunting.

Weirdness with Bash functions and Curl

JonTheNiceGuy Leave a comment

I’m writing a script (for $NEW_PROJECT) which, due to my inability to figure out how to compile a certain key library on Dreamhost, runs SSH to a remote box (with public/private keys and a limitation on what that key can *actually* achieve) to perform an off-box process of some data.

After it’s all done, I am using curl to call the API of the project like this:

curl --fail -F "file=@`pwd`/file" -F "other=form" -F "options=are_set" http://user:password@server/api/function

Because I’m making a few calls against the API, I wrote a function like this:

function callAPI() {
API=$1
if [ "$2" != "" ]
then
API=$API/$2
fi
if [ "$3" != "" ]
then
API=$API/$3
fi
if [ "${OPTION}" != "" ]
then
FORM="${OPTION}"
else
FORM=""
fi
if [ $DEBUG == "1" ]
then
echo "curl --fail ${FORM} http://${USER}:**********@${SITE}/api/${API}"
fi
eval `curl --fail ${FORM} http://${USER}:${PASS}@${SITE}/api/${API} 2>/dev/null`
}

and then call it like this:

OPTION="-F \"file=@filename\" -F \"value=one\" -F \"value=two\""
callAPI function

For all the rest of my API calls (those which ask for data, rather than supply it, these calls work *fine*, but as soon as I tell it to post a form containing a file, it throws this error:

curl: (26) failed creating form post data

I did some digging around, and found that this means that the script can’t read from the file. The debug line, when run outside of the script processed the command perfectly, so what’s going on?

To be honest, in the end, I just copied the command into the body of the code, and I’m praying that I can figure out why I can’t compile this library on Dreamhost, before I need to work out why running that curl line doesn’t work from inside a function.

A summary of my ongoing Open Source projects

JonTheNiceGuy Leave a comment

I’m a pretty frequent contributor to various Open Source projects, either when I’m starting them myself, or getting involved in someone else’s project. I thought, as I’m probably stretching myself a bit thin with these projects right now, I’d list off what I’m doing, so I can find out whether anyone’s interested in getting involved in any of them. Read More

Need to quickly integrate some IRC into your app? Running Linux? Try ii

JonTheNiceGuy Leave a comment

I know, it looks like a typo, but the script ii makes IRC all better for small applications which don’t need their own re-implementation of an IRC client.

I know it’s available under Ubuntu and Debian (apt-get install ii), but I don’t know what other platforms it’s available for.

It’s not much use as a user-focused IRC client (although it would vaguely work like that with a little scripting!), but for scripts it works like a charm.

Read More