Looking at the code in the previous entry wasn’t exactly a pleasant aesthetic experience (sorry for that, bit of a botched job) so for my new project, an implementation of the NIST RBAC model in PHP, I decided to code a nice generic PHP query engine. The Query Engine takes a number of arguments like the SQL query, the arguments for the query (to be passed into prepared statements), the types of the arguments and whether the query is part of an overall transaction. The nice thing is that the QueryEngine function returns the results as an associative array using the database column names as the key value.

The database connection is set up through the usage of constants that are defined below.

/**
* Database Server IP address
*/
define ("DATABASE_SERVER", "<insert database server>");
/**
* Database username
*/
define ("DATABASE_USER", "<insert database user>");
/**
* Database password
*/
define ("DATABASE_PASSWORD", "<insert database password>");
/**
* Database name
*/
define ("DATABASE_NAME", "<insert database name>");

The function below is the QueryEngine itself.

/**
* Generic query execution engine. This function uses the mysqli
* interface and makes use of mysqli bound parameters and bound results to
* lower the risk of SQL injection attacks.
*
* @param string $sql
* @param array $param
* @param string $type
* @param integer $transaction
* @return array
*/
function QueryEngine($sql='', $param=array(), $type='', $transaction_flag=0) {
    /* Initialize variables */
    $field = $meta =  $params = $key = $val = $c = $results = '';
    /* The database connection is cast as a static variable to ensure that the
    database connection will be initialized only once during script execution.
    This saves on valuable time otherwise spent on setting up the database
    connection over and over again in the same script execution. */
    static $database_connection = NULL;
    if (is_null($database_connection)) {
        /* CONSTANTS for database connection are defined in the top of this script */
        $database_connection = mysqli_connect(DATABASE_SERVER, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);
        if (!$database_connection) {
            trigger_error('Connect Error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error(), E_USER_ERROR);
        }
        /* Force the UTF-8 character set */
        if (!mysqli_set_charset($database_connection, "utf8")) {
            trigger_error('Error loading character set utf8: ' . mysqli_error($database_connection), E_USER_ERROR);
        }
    } 

    /* Check whether the transaction flag has been set, the transaction needs
    to be comitted or rolled back */
    switch ($transaction_flag) {
        case 0:
            break;
        case 1:
            /* Set autocommit to off */
            mysqli_autocommit($database_connection, FALSE);
            break;
        case 2:
            /* Commit transaction */
            mysqli_autocommit($database_connection, TRUE);
            break;
        case 3:
    	      /* Rollback transaction */
            mysqli_rollback($database_connection);
            break;
        default:
            break;
    }

    if (!empty($sql)) {
        /* Prepare SQL statement */
        $stmt = mysqli_prepare($database_connection, $sql);
        /* Dynamically bind arguments passed in through the $param array */
        if ($stmt) {
            if (($param) && ($type)) {
                /* A custom function is constructed that calls
                mysqli_stmt_bind_param because the regular function can't
                handle arrays as an input source */
                call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $type), $param));
            }
            mysqli_stmt_execute($stmt);
            /* Get the column names of the retrieved rows by querying the schema
            meta-data. The column names are returned as the keys of the
            multidimensional result array and the rows are returned as the
            values of the array. */
            $meta = mysqli_stmt_result_metadata($stmt);
            if (!empty($meta)) {
                while ($field = mysqli_fetch_field($meta)) {
                    $params[] = &$row[$field->name];
                }
                /* A custom function is constructed that uses the retrieved
                column names as bound result parameters */
                call_user_func_array(array($stmt, 'bind_result'), $params);
                while (mysqli_stmt_fetch($stmt)) {
                    /* The results are put into an associative array */
                    foreach($row as $key => $val) {
                        $c[$key] = $val;
                    }
                    $results[] = $c;
                }
            }
        } else {
            trigger_error('query failed: ' . $sql . ' ' . mysqli_error($database_connection), E_USER_ERROR);
        }
        mysqli_stmt_close($stmt);
    }

    return $results;
}

Subsequently you use the code as follows:

$sql = 'INSERT INTO role_permission (role_id, permission_id) VALUES (?, ?)';
$results = QueryEngine($sql, array(&$role_id, &$permission_id), 'ii', 0);
if (!empty($results)) {
    $all_query_ok = FALSE;
}

The parameters are passed by reference (&$role_id and &$permission_id), this is required for PHP 5.3 and upwards.

As indicated above the database column names are used as the key values of the associative database, they can be used as follows (the $results array contains the return result of the QueryEngine function):

<table>
<caption>Test</caption>
<thead
<tr>
<?php
$column_headers = array_keys($results[0]);
$number_of_columns = count($column_headers);
for ($counter = 0; $counter < $number_of_columns; $counter++) {
    print '<th scope="col">' . $column_headers[$counter] . "</th>\n";
}
?>
</tr>
</thead>
<tbody>
<?php
foreach ($results as $key => $val) {
    print "<tr>\n";
    for ($counter = 0; $counter < $number_of_columns; $counter++) {
        print '<td class="usertable">' . $val[$column_headers[$counter]] . "</td>\n";
    }
    print "</tr>\n";
}
?>
</tbody>
</table>

And lastly using transactions:

/* Start transaction */
$all_query_ok = TRUE;
QueryEngine('', '', '', 1);

/* Get user id and role ids from the user table*/
$sql = 'SELECT user.user_id, role.role_id
        FROM user
        INNER JOIN user_role USING (user_id)
        INNER JOIN role USING (role_id)
        WHERE user.username = ?';
$results = QueryEngine($sql, array($username), 's', 0);

if (is_array($results)) {
    foreach ($results as $key => $val) {
        $user_id = (int) $val['user_id'];
        $role_id_collection[] = (int) $val['role_id'];
    }
} else {
    $all_query_ok = FALSE;
}

/* Insert the session based on the unique identifier */
$sql = 'INSERT INTO session (identifier, created) VALUES (?, NOW())';
$results = QueryEngine($sql, array($identifier), 's', 0);
if (!empty($results)) {
    $all_query_ok = FALSE;
}

/* Create the link between the session and the active user */
$sql = 'INSERT INTO user_session (user_id, session_id) VALUES (?, LAST_INSERT_ID())';
$results = QueryEngine($sql, array($user_id), 'i', 0);
if (!empty($results)) {
    $all_query_ok = FALSE;
}

/* Assign the users' roles to the session */
$sql = 'INSERT INTO session_role (role_id, session_id) VALUES (?, LAST_INSERT_ID())';
foreach($role_id_collection as $role_id) {
    $results = QueryEngine($sql, array($role_id), 'i', 0);
    if (!empty($results)) {
        $all_query_ok = FALSE;
    }
} 

/* Commit or rollback transaction based on the value of $all_query_ok */
$all_query_ok ? QueryEngine('', '', '', 2) : QueryEngine('', '', '', 3);

That’s it, I hope it’s of use to you. Any questions please drop me a line at meint squiggly bit meint dot net.

No related posts.