www.zeger.nl

When using JSON as a data transport encoding schema for MySQL the database server should always return a single row; it can be useful (even better: you should) to make use of stored functions for the generation of the JSON data on the database side. A simple database object is shown and discussed.

Note:
Article is still wip.

Firstly note that we should use constants wherever possible; in this example constants are set in the constants.php file.

The object has some specific properties:

1. public function __construct()

   
   Upon construction the database object "auto-connects" to the database; usually the database object is instantiated in order to communicate with the database hence we auto connect.

2. public function connect()

   
   We use the new mysqli object to connect to the database in some occasions a stored procedure is implemented which can only be used when the mysqli object is used to connect. For example: Stored procedures are used when recursively constructing a menu tree.

   Note we only use constants to connect to the database: this is good practice and improves the development process of database driven applications.

3. public function escapeString( $value )

   
   A function to escape the SQL string is needed, we can write it ourselfs or use the mysqli_real_escape_string function which should solve all our escaping problems. Note the function needs a database link object.

4. public function callFunction( $function_name )

   
   This object can be used for stored functions, in order to improve the security we always escape the function arguments. By using array_walk we can escape all function arguments without any hassle by using the previously described escapeString function.

The complete object is shown below.

require_once( $_SERVER['DOCUMENT_ROOT'] . '/constants.php' );
 
class Database
{
	private $link;
 
	public function __construct()
	{
		$this->connect();
	}
 
	public function connect()
	{
		/**
		 * We could use some round robin here for load balancing
		 */
		$link = mysqli_connect( 
			DATABASE_HOST, 
			DATABASE_USER, 
			DATABASE_PASSWORD, 
			DATABASE_SCHEMA, 
			DATABASE_PORT
		);
 
		if ( !$link ) 
    		die( 'Could not connect: ' . $link->error );
 
		$this->link = $link;		
	}
 
	public function reconnect()
	{
		$this->cleanUp();
		$this->connect();
	}
 
	public function getLink()
	{
		return $this->link;
	}
 
	public function cleanUp()
	{
		mysqli_close( $this->link );
	}
 
	public function getError()
	{
		return $this->link->error;
	}
 
	public function query( $sql )
	{
		$call_result = $this->link->query( $sql );
 
		if ( $call_result !== false && $this->link->affected_rows )
		{
			/**
			 * For stored procedures the result can be empty
			 * hence the call result is 'true' and no rows can
			 * be fetched
			 */
			if ( $call_result === true )
				return true;
 
			$row = $call_result->fetch_row();
			return $row;
		}			
		return false;
	}
 
	public function escapeString( $value )
	{
		/**
		 * mysqli_real_escape_string should protect for
		 * SQL injection
		 */
		return mysqli_real_escape_string( $this->link, $value );
	}
 
	/**
	 * Secure function call
	 * 
	 * Call SELECT function_name( arg_1 ... arg_n ) and
	 * return the result
	 *
	 * @param String $function_name
	 * @return array
	 */
	public function callFunction( $function_name )
	{
		$parameters = func_get_args();
 
		array_shift( $parameters );
		array_walk( $parameters, array( $this, 'escapeString' ) );
 
		$sql = 'SELECT ' . $function_name . "( '"  . implode( "','" , $parameters ) . "' );";
 
		return $this->query( $sql );
	}
}