Life of a web developer

November 22, 2010

Tutorial: Handy mysql database object

Filed under: Database, PHP, Tutorials — Tags: , , , , — Ole Aass @ 10:45 pm

I’m tired of typing the same stuff over and over. So I spend alot of time writing reusable code.

No matter if it’s just simple methods or abit more complex objects. Here’s the database object I just wrote. It’s versatile and it really doesnt restrict you to follow any special rules except what objects already restricts you to. You can decide for yourself if you want to complete the query statement or make prepared statement in a valid vsprintf() format.

The following code is examples of usage:

$db = new Class_Database;
$db->connect();

$sql = "SELECT * FROM users WHERE username='%s' AND password='%s'";
$inputs = array('username', 'password');

$result = $db->execute($sql, $inputs);
if (!$result)
{
	return false;
}
else
{
	return true;
}

or you can use:

$db = new Class_Database;
$db->connect();

$username = $db->secureInputs('username');
$password = $db->secureInputs('password');
$sql = "SELECT * FROM users WHERE username='".$username."' AND password='".$password."'";

$result = $db->execute($sql);
if (!$result)
{
	return false;
}
else
{
	return true;
}

This is the main object:

class Class_Database
{
	/**
	 * These are the variable that will hold 
	 * the database settings
	 */
	private $host;
	private $user;
	private $pass;
	private $base;
	private $prefix;
	
	// This will hold the connection link after a
	// valid connection has been established
	private $link;

	/**
	 * The constructor will trigger when the object
	 * is initialized, assigning the correct settings
	 * to the correct variable
	 *
	 * @param bool $autoConnect If this is true you do not need to manually trigger the connect function after initializing the object
	 */
	final function __construct($autoConnect = false)
	{
		$this->host		= 'localhost';
		$this->user		= 'username';
		$this->pass		= 'password';
		$this->base		= 'mydatabase';
		$this->prefix	= 'mydb_';
		
		// Try to establish a connection when initialized
		if ($autoConnect)
		{
			$this->connect();
		}
	}
	
	/**
	 * Connect to the database
	 */
	public function connect()
	{
		// Try to establish a valid connection to the database
		$this->link = mysql_connect($this->host, $this->user, $this->pass);
		
		// If connection attempt failed, return false
		if (!$this->isConnected())
		{
			return false;
		}
		else
		{
			// If not able to select the database, return false
			if (!mysql_select_db($this->base, $this->link))
			{
				return false;
			}
		}
		
		// If nothing fails, return true
		return true;
	}
	
	
	/**
	 * Disconnect from the database
	 */
	public function close()
	{
		// Close connection
		mysql_close($this->link);
		
		// Empty the link variable
		unset($this->link);
	}
	
	/**
	 * Check if a valid connection has been established or not
	 */
	public function isConnected()
	{
		// If the link is not set or not a valid resource,
		// try to connect. If this fails, return false
		if ((!isset($this->link)) || (!is_resource($this->link)))
		{
			if (!$this->connect())
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		
		// If everything validates, return true
		return true;
	}
	
	/**
	 * Execute a direct or prepared sql statement.
	 * All prepared statement must come in a valid vsprintf() format
	 * Example:
	 * $sql = "SELECT * FROM users WHERE username='%s' AND password='%s'";
	 * $inputs = array('username','password');
	 * Database::execute($sql, $inputs);
	 * 
	 * @param string $sql This holds the main sql statement. Either fully working or a prepared one
	 * @param array $inputs If you've given a prepared statement this must hold an array of input values
	 */
	public function execute($sql, $inputs = array())
	{
		// First we make sure there's a valid connection established
		if (!$this->isConnected())
		{
			return false;
		}
		
		// If there's been specified input values
		if (!empty($inputs))
		{
			// Secure the inputs
			$inputs = $this->secureInputs($inputs);
			
			// Merge the array of secured inputs with 
			// the vsprintf() prepared SQL statement
			$sql = vsprintf($sql, $inputs);
		}
		
		// Return the raw result returned by MySQL
		return mysql_query($sql);
	}

	/**
	 * Secure inputs from different typs of attack forms.
	 * I know this one is not that safe, but it gives you 
	 * the basic idea of how it works. I also need to learn
	 * more about this issue, so any ideas on the topic are 
	 * very much appreciated.
	 * 
	 * @param array $inputs This should ideally be an array, but can also be a string
	 */
	public function secureInputs($inputs)
	{
		// If the inputs isn't an array turn it in to one
		if (!is_array($inputs))
		{
			$inputs = (array) $inputs;
		}
		
		// Loop through the array and secure the values
		foreach ($inputs as $key => $value)
		{
			$value = mysql_real_escape_string($value);
			$inputs[$key] = $value;
		}
		
		// Return the array holding the secured values
		return $inputs;
	}
}
Advertisements

2 Comments »

  1. Really use full reusable class man… good work… keep it up πŸ™‚

    Comment by Riyaz — November 23, 2010 @ 10:25 am

    • Thanks for the comment Riyaz πŸ™‚ glad you liked it

      Comment by develophp — November 23, 2010 @ 12:00 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: