A PHP back end to YUI

This is my package of PHP functions for the server side.

I have my own personal standard on how to carry communications back and forth in between client and server. All transactions follow this standard even when they might use very little of it, but it helps make things very predictable, which helps mantainability.

From the client to the server, all requests will go in a POST message in URL-encoded format. There will always be at least two arguments, ajaxObj and ajaxAction. The first is associated with a certain object, for example, a DataTable or a form. The second, ajaxAction, indicates what is it that I am requesting about that object. The rest of the arguments depend on the particular operation.

On the reply from the server to the client, the format is as follows

{
    replyCode:200, replyText:"Ok", 
    data:[{ ... } , ... ]
    , .... 
}

The replyCode and replyText are mandatory and will always be there. The first, replyCode I set to 200 for Ok (much like in HTTP), 201 for Data Follows or 500 and above for errors. Though I adopted the numerical conventions of HTTP do not confuse them with those. These are application error codes, not communication codes. The browsers and caching proxies might take action on the HTTP codes and though I could use the header function to create any HTTP reply code I wanted, some of them might cause unexpected behavior, very difficult to trace. replyText hopefully will contain an 'Ok' for no error or an appropriate message.

This message format is usually called an envelope since the data goes inside or, as in this case, right afterward.  An arbitrary number of named properties may follow the envelope, in particular, if present, an object named data carries tabular information. Its value will be an array of object literals, each element of the array will be one record represented as an object literal which will have a property named after each column containing the field value. Further properties and their values may follow.  Certain transactions expect as a reply either an Ok or an error reply; those will carry no data.

After including the standard includes, all my AJAX server pages call ajaxReq (shown later) which branches to a function with the name pattern (in PHP syntax) "ajax_${ajaxObj}_${ajaxAction}" where $ajaxObj and $ajaxAction are PHP variable names representing arguments coming in the POST request.

After the call to ajaxReq there will be a series of functions responding to that name pattern. To allow the same page to both serve AJAX and normal page requests, ajaxReq will first look for the ajaxObj argument; if it doesn't find one, it returns, assuming that the request was a normal page request, not an AJAX one. If the page does only serve AJAX request, it is enough to place an appropriate error reply right after the call to ajaxReq. If the ajaxObj argument is there it will search for an ajaxAction argument as well and it should find a function named as indicated or it will give an error.

When ajaxReq reads the basic arguments and finds the corresponding function, it calls it and upon return sends back an 200 'Ok' message back with no data, this is the hopeful default. At any time the called function can return with data or an error message. Returning some sort of data is actually the most frequent case. If so, the processing will be terminated and the default 200 'Ok' message will never be reached.

The next important function is ajaxReply, which takes a variable number of arguments. The first is what will become the replyCode and it defaults to 200 if none is given, the second is the replyText which defaults to 'Ok'. The third and remaining optional arguments can be one of two things, either an array or an string, anything else will produce an error. Any number of them can be given in any order.  

If the extra argument is an array, it should be in the name => value format (not just unnamed values) so that they will be turned into named properties in the JSON reply. Care should be taken not to repeat property names when there is more than one array and that none of those coincide with any of the names already in use by the function.  Though the argument itself, if it is an array, has to have named elements, the value of any of those named elements might be a simple array with no names.

If the extra argument is a string, it will assume it is an SQL query, which will be executed and the data returned in the data property of the reply. If there are more than one SQL query in the arguments, the first will be in the data array, the second in a data1 array, the third in a data2 and so on.  I usually don't have more than a single tabular element in the reply, and that is the one that goes into the DataTable.  Other tabular elements might contain name-value pairs for a dropdown list. 

ajaxReply does not return from the call, it always exits after the reply is sent to the client since the envelope ends up closed and further data would be ignored.

Let's see a typical server page:

<?php
    include 'includes/include.php';
    
    ajaxReq();
    
    function ajax_Places_select() {
        ajaxReply(201,'Data Follows','select IdPlace as value, Name as text from Places order by Name');
    }

Right after including my personal library of functions, I call ajaxReq then, any number of function definitions may follow. Here I show just one, ajax_Places_select which will be invoked every time a request comes with ajaxObj=Places&ajaxAction=select in the URL. This particular function, which I call a 'transaction' since that is what they usually turn into on the SQL side, does not need any further arguments so it does not read any more from the URL. It is simply a request to read a lookup table of places. These values will be used in an HTML select box and the DataTable formatter for dropdown boxes expects to receive its values as an array of value => text couples, so I use SQL to make the alias and, in order to have the select box sorted, I do the sorting at the SQL side, which is very fast. Since the third argument is a string, it will assume it is an SQL statement so ajaxReply will execute this statement and it will create an array of literal objects in the data element of the envelope, like:

{
    replyCode:201, replyText:"Data Follows", 
    data:[
        {"value":23,"text":"Alice's"},
        {"value":1,"text":"Home"},
         ... 
    ] 
}

Other functions might just return a status indication, for example:

function ajax_Places_delete() {
    $IdPlace = parse_post_int('IdPlace');
    mysql_query('delete from Places where IdPlace =' . $IdPlace)
        or ajaxReply(500,'SQL error '  . mysql_error());
    mysql_affected_rows() or ajaxReply(501,
        'The record does not exist, it might have been deleted by someone else');
}

The delete transaction calls parse_post_int to read argument IdPlace from the POST request, which it then checks with a regular expresion for valid digits and calls intVal to ensure it gets a number. Then it builds an SQL statement to delete a record. If the statement fails, it sends an error reply with a 500 replyCode and a corresponding message. It then checks whether it actually deleted any records at all and, if not, sends another error message. The numbers used for the error codes are whatever you want, I follow the convention of assuming 5xx are error codes, I have no standard on what each means, it is just a matter of documenting them for each transaction. If all goes according to plan, the function will finally return and ajaxReq which called it in the first place, will then send a 200 'Ok' message with no data. In neither success or failure replies I use the third argument to ajaxReply since there is no extra information to convey.

Actually, since the pattern of calling mysql_query and then checking for errors is so frequent, I use a function called ajaxSqlQuery:

function ajaxSqlQuery($sql) {
    $result = mysql_query($sql);
    if ($result) return $result;
    ajaxReply(620,'Sql error: ' . mysql_error(),array('sql' => $sql));
}

If there is an error in the execution of the SQL statement, mysql_query will return false so anything else is a valid result, which it then returns.  Otherwise, the reply will have 620 as a replyCode, the SQL error message as the replyText and an extra property named 'sql' containing the failed SQL statement as its value.  Not that the end user would care nor even should know what the structure of your database is but at development time this information is good.

Another posible transaction on the same table, an update:

function ajax_Places_update() {
    ajaxSqlQuery(
        BuildSql('update Places set ?1x = ?2s where IdPlace = ?3i and ?1x = ?4s'
            , $_POST['fieldName'], $_POST['newValue'], 
            parse_post_int('IdLugar'), $_POST['oldValue']
        )
    );
    mysql_affected_rows() or ajaxReply(501,
        'The record does not exist or it might have been modofied by someone else');
}

ajax_Places_update executes an action query which does not return a value, nevertheless, the query is made so that it checks that the record to be changed still exists with its original value (taken from 'oldValue'). I'll describe the BuildSql function later, right now it is enough to know that it is a sort of sprintf made for SQL where the ? starts the placeholder to the values, which follow the formatting list. BuildSql accepts positional arguments, indicated by the number after the ?, thus here we use the first argument, fieldName, in two places. It checks if there was any affected rows, if there weren't it means someone else changed the original record and it returns a 501 error. If none of the above happens, it will return and ajaxReq which called it, will return a 200,'Ok' message. This kind of update function I often use for inline edits on DataTable fields. In none of the replies I use the third argument since there is nothing to return but a status code.

The next function, ajax_Places_insert does a record insert and at the end it has a call to ajaxReply with the third argument set to an array which is the database record ID of the newly inserted record.

function ajax_Places_insert() {
    ajaxSqlQuery(
        BuildSql('insert into Places (Name,Address1, Address2, City,Zip) values (?s,?ns,?ns,?ns,?ns)'
            , $_POST['Name'], $_POST['Address1'], $_POST['Address2'], $_POST['City'], $_POST['Zip']
        )
    );
    ajaxReply(201,'Data Follows',array('IdPlace' => mysql_insert_id()));
}    

This is how this reply would look:

{"replyCode":201,"replyText":"Data Follows","IdPlace":26}

Function ajaxReq

The ajaxReq reads the ajaxObj argument from $_POST. If there is no ajaxObj argument, it assumes it was not an AJAX request so it returns. If it does find one, it does a little regexp check for valid characters then reads ajaxAction and validates it as well. It finally assembles the function name. If there is such a function, it will send the headers to prevent the reply from being cached and to insure the text is read in the proper charset (I don't expect to use anything but Roman characters, but UTF-8, Cyrillic or any other are reasonable alternatives). Then it calls the function and on return, if it does return (which it often does not) , it calls ajaxReply without arguments, which will produce a 200, 'Ok' reply.

Notice the call to the PHP function set_error_handler right before calling the variable function $func. This makes PHP send any error to the function named in the argument.  The purpose of this handler is to format any error message in a JSON compatible way. PHP error messages are formatted in HTML to show on a browser. The function, shown later, takes the component of the error message and packs them into a JavaScript object literal. Usually you would save the old value of the error handler to restore it after whatever you did, but in this case since we are in 'AJAX mode' and we won't return to 'HTML mode', we don't want the normal handler restored.

function ajaxReq() {
    $ajaxObj = trim($_POST['ajaxObj']);
    if (strlen($ajaxObj)) {
        if (preg_match('/^[a-zA-Z]+$/',$ajaxObj)) {
            $ajaxAction = trim($_POST['ajaxAction']);
            if (strlen($ajaxAction)) {
                if (preg_match('/^[a-zA-Z]+$/',$ajaxAction)) {
                    $func = "ajax_${ajaxObj}_${ajaxAction}";
                    if (function_exists($func)) {
                        header('Cache-Control: no-cache, must-revalidate'); // HTTP/1.1
                        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
                        header('Content-type: application/json; charset=utf-8');
                        set_error_handler('ajaxErrorHandler');
                        $func();
                        ajaxReply();
                    }
                    ajaxReply(601,'function not defined: ' . $func);
                }
                ajaxReply(602,'action contains invalid chars: ' . $ajaxAction);
            }
            ajaxReply(603,'missing action');
        } 
        ajaxReply (604,'object name has invalid characters: ' . $ajaxObj);
    }
}

Reading ajaxObj and ajaxAction from $_REQUEST instead of $_POST is a valid alternative.  This might allow you to order transactions from a GET request as well as from a POST one.


Function ajaxReply

ajaxReply uses two or more arguments.  The first two default to a 200, 'Ok' reply message. They are the basic envelope for any data which, if any, goes into the remaing arguments beyond the first two. 

First, it initializes variable $s where the content of the envelope will be assembled. It loops through the arguments starting with the third.  It uses the PHP functions func_num_args and func_get_arg to check the number of arguments and fetch each one, which will then be stored in $arg.  

If $arg is an array, it will use json_encode (which in PHP 5 is already built in) to encode it but, since this will produce a full json-encoded string, with its enclosing curly braces and we might not be finished yet, we strip those off and concatenate it into $s after a comma . 

If $arg a string then it assumes it to be an SQL statement and it calls ajaxSqlQuery to execute it. Remember ajaxSqlQuery already checks for errors and if any is found, it will return the appropriate reply and die so, if a $result is ever returned, it has to be a valid result set. I set $s to start a JavaScript array literal named datann, where nn is a sequential number (though there is no data0 but simply data). Each element is made of a row returned from the query turned into a JavaScript object literal made from the field names and field values.

Since the optional arguments from the third on have to be either an array or a valid SQL statement, anything else will produce an error which, captured by the error handler set in ajaxReq, will produce a suitable error reply. 

Finally, the function assembles the envelope with replyCode, replyText and the data assembled in $s, if there is any.

function ajaxReply($replyCode = 200,$replyText = 'Ok') {
    $s = '';
    $nSql = '';
    for ($iArg = 2;$iArg < func_num_args();$iArg++) {
        $arg = func_get_arg($iArg);
        if (is_array($arg)) {
            $arg = json_encode($arg);
            $s .= ',' . substr($arg,1,strlen($arg)-2);
        } elseif (is_string($arg)) {
            $result = ajaxSqlQuery($arg);
            $s .= ',"data' . $nSql . '":[';
            $nextRow = false;
            while ($row = mysql_fetch_assoc($result)) {
                if ($nextRow) {
                    $s .= ',';
                } else {
                    $nextRow = true;
                }
                $s .= json_encode($row);
            }
            $s .= ']';
            mysql_free_result($result);
            $nSql++;
        } else {
            trigger_error("ajaxReply: optional argument at position $iArg value $arg is invalid, 
                only arrays or SQL statements allowed",E_USER_ERROR);
        }
    }
    echo '{"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '}';
    exit;
}

Replying to YUI Get requests

The new YUI Get Utility allows for cross-domain requests, nevertheless it imposses a restriction, it can only read scripts and stylesheets, it cannot read plain data as supplied by a plain JSON server. Though a JSON reply is plain JavaScript, it is simply a value and, unless assigned to something, it is evaluated and completely forgotten.The way to go around this limitation is to provide the server with the name of a callback function so that the reply is actually a function call with the JSON reply as its argument.

In order to do this, lets introduce another URL argument, ajaxCallback which will be the name of the function to call. Then, the function ajaxReply above would end with the following lines:

    $ajaxCallback = trim($_POST['ajaxCallback']);
    if (strlen($ajaxCallback)) {
        header('Content-type: application/javascript; charset=utf-8');
        echo $ajaxCallback, '({"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '});';
    } else {
        echo '{"replyCode":' , $replyCode , ',"replyText":"' , $replyText , '"' , $s, '}';
    }
    exit;
}

If there is an ajaxCallback argument in the request, these last few lines will, first, change the response header from JSON to JavaScript, then echo the very same response enclosed in parenthesis with the name of the function provided in front. This will make the onSuccess callback function of Get unnecesary since upon successful load and evaluation of the response, the callback function provided along the URL will be called and the reply will be handled by that function.


Function BuildSql

This is my BuildSql function which, as I said, it is a kind of sprintf for SQL. Placeholders start with ? not with % and they are composed of:

?: the start of the place holder

n: optional, a number (starting with 1) for the position of the argument in the argument list (position 0 is the format string itself). If not specified, they will be taken in the order in which they appear.

m: optional, either:

t: mandatory, the data type, which can be any of:

All the arguments can be either upper or lowercase. The first 6 are quite obvious. Dates are expected as regular PHP timestamp integer values so it is up to you to convert them from whatever the format of your date to a PHP timestamp. Strings are passed through mysql_real_escape_string to escape any might dislike and enclosed in quotes.

The P argument stands for Prefix and is meant to put before the names of tables so that in a situation where you have to share the same database with other applications or development team, the contents of the $table_prefix global variable, which you would have read from some configuration file, will be put in front of the table name. For example:

select * from ?ptable

If $table_prefix is empty, then no harm is done, if it is not, then your table names will share the same prefix.

Finally, the X argument is basically for field names, when the name of the field itself is a variable. You will have noticed it in the ajax_Places_update function in the example above, the posted $_POST['fieldName'] contains the name of the field to be table column to be modified. The field name will be quoted with backticks as per MySql syntax and escaped. With all this escaping and the intvals, floatvals and such in the other datatypes, there shouldn't be much chance for SQL injection.

function BuildSql($query) {
    global $table_prefix;
    
    $num_args = func_num_args();  // number of arguments available
    $args_used = (1 << $num_args) -2; // bit mask to check if arguments are used

    $offset = -1;
    $matches = array();
    while(true) {
        $offset++;
        $match = array('start' => $offset);
    
        $offset = strpos($query,'?',$offset);
        if ($offset === false) {
            $match['len'] = -1;
            $matches[] = $match;
            break;
        }
        $match['len'] = $offset - $match['start'];
        $state = 0;
        do {
            $offset++;
            $ch = strtolower($query[$offset]);
            switch($ch) {
                case 's':
                case 'i':
                case 'f':
                case 'd':
                case 't':
                case 'b': 
                case 'p':
                case 'x':
                    $match['type'] = $ch;
                    $state = 99;
                    break;
                case 'm':
                    if ($state < 10) {
                        $match['mandatory'] = true;
                        $state = 10;
                    } else {
                        trigger_error('BuildSql: Cannot have an "M" modifier at that position ' 
                            . substr($query,0,$offset+1),E_USER_ERROR);
                    }
                                break;
                case 'n':       // null if empty?
                    if ($state < 10) {
                        $match['null'] = true;
                        $state = 10;
                    } else {
                        trigger_error('BuildSql: Cannot have an "N" modifier at that position ' 
                            . substr($query,0,$offset+1),E_USER_ERROR);
                    }
                                break;
                case '0':
                case '1':
                case '2':
                case '3':
                case '4':
                case '5':
                case '6':
                case '7':
                case '8':
                case '9':
                    switch($state) {
                        case 0:
                            $match['pos'] = intval($ch,10);
                            $state = 3;
                            break;
                        case 3:
                            $match['pos'] = $match['pos'] * 10 + intval($ch,10);
                            $state = 5;
                            break;
                        default:
                            trigger_error('BuildSql: Cannot have a digit at that position ' 
                                . substr($query,0,$offset+1),E_USER_ERROR);
                            break;
                    }
                    break;
                default:
                    trigger_error('BuildSql: Unknown formatting character: ' 
                        . substr($query,0,$offset+1),E_USER_ERROR);
            }
        } while ($state < 99);
        $matches[] = $match;
    }

    $arg_pointer = 1; // sequential pointer to arguments
    $s = '';  // output SQL statement
    foreach($matches as $match) {
        if ($match['len'] == -1) {
            $s .= substr($query,$match['start']);
            break;
        }
        $s .= substr($query,$match['start'],$match['len']);
        // read the value of the argument
        if ($match['type'] == 'p') {
            $value = $table_prefix;     // t is a special case, it takes no argument from the list
            $n = 0; // to avoid marking the argument as used.
        } else {
            if ($match['pos']) {
                $n = $match['pos'];
            } else {                        
                // else, read the next sequential argument an increment the argument pointer
                $n = $arg_pointer++;
            }
            // some bitwise magic to unset the bit position representing the argument
            $args_used &= ~ (1 << $n);   
            if ($n >= $num_args) {
                trigger_error("BuildSql: Missing argument $n after: $s",E_USER_ERROR);
            } else {
                $value = func_get_arg($n);      // otherwise, read it
            }
        }
        if ($match['mandatory'] && is_null($value)) {
            trigger_error("BuildSql: Missing value for argument $n after: $s",E_USER_ERROR);
        }
        $NullIfEmpty = $match['null'];
        switch($match['type']) { // now we process $value according to datatype and $NullIfEmpty flag
            case 's':
                if ($NullIfEmpty and strlen($value) == 0) {
                    $s .='null';
                } else {
                    $s .= "'" . mysql_real_escape_string($value) . "'";
                }
                break;
            case 'i':
                if ($NullIfEmpty and $value == 0) {
                    $s .='null';
                } else {
                    $s .= intval($value);
                }
                break;
            case 'f':
                if ($NullIfEmpty and $value == 0) {
                    $s .='null';
                } else {
                    $s .= floatval($value);
                }
                break;
            case 'd':
                if ($NullIfEmpty and empty($value)) {
                    $s .='null';
                } else {
                    $s .= "'" . date('Y-m-d H:i:s', $value) . "'";
                }
                break;
            case 't':
                if ($NullIfEmpty and empty($value)) {
                    $s .='null';
                } else {
                    $s .= "'" . date('z H:i:s', $value) . "'";
                }
                break;
            case 'b':  // booleans cannot be null
                $s .= intval($value!=false);
                break;
            case 'p':
                $s .=  mysql_real_escape_string($value);
                break;
            case 'x':
                $s .= '`' . mysql_real_escape_string($value) . '`';
                break;
        }
    }
    //
    // if all arguments used, $args_used will be zero
    if ($args_used) {
        for($i=1; $i < $num_args; $i++) {
            $args_used >>=1; // bitmask is shifted right
            // if right most bit still 1, it means it has not been used.
            if ($args_used & 1) trigger_error("BuildSql: Argument $i not used",E_USER_ERROR);
        }
    }
    //
    return $s;
}

BuildSql is of general use within or without AJAX, actually, I've been using it in several incarnations (including Visual Basic and VBScript versions) for years now.  Notice that I use function trigger_error to signal errors which, with PHP error handler set to ajaxErrorHandler (shown below) will be reported in true JSON fassion.


Function ajaxErrorHandler

This is the error handler set by ajaxReq to handle error messages in JSON style. I took it from the example in the PHP manual which discriminates between the several errors, though it actually doesn't matter much, except to separate notices from the rest. You have to ignore notices since this function is called with all errors, warnings and notices, regardless of the error level you set. Besides setting replyCode and replyText, which are the least the JSON client expects, it sets other properties from values passed as arguments to this function.

function ajaxErrorHandler($errno, $errstr, $errfile, $errline)    {
    switch ($errno) {
        case E_USER_ERROR:
            echo '{"replyCode":611,"replyText":"User Error: ' 
                , addslashes($errstr) . '","errno":', $errno;
            break;
        case E_USER_WARNING:
            echo '{"replyCode":612,"replyText":"User Warning: ' 
                , addslashes($errstr) . '","errno":', $errno;
            break;
        case E_USER_NOTICE:
        case E_NOTICE:
            return false;
        default:
            echo '{"replyCode":610,"replyText":"' 
                , addslashes($errstr) . '","errno":', $errno;
            break;
    }
    if ($errfile) {
        echo ',"errfile":"' , addslashes($errfile) ,'"';
    }
    if ($errline) {
        echo ',"errline":"', $errline ,'"';
    }
    echo '}';
    die();
}

Licensing: I know company auditors require this so, for their benefit, let me state that all the code above can be freely used, modified, distributed or simply ignored, whichever pleases you most.  Just be nice and don't take undue credit for it, if credit is due.  Thanks.