HttpSqlConn module

Module name: httpsqlconn
You have to enable this module in site/_modules.php file to use it.

This module gives you the possibility to receive and handle json encapsulated sql commands through HTTP/POST requests. There is a C++ json builder in gSAFE package which works together this module.

The C++ query builder works similar to CodKep sql query interface, so you can use same methods and options to build queries in C++ as CodKep in php. By configuring this module you can "execute" the built queries in server side safe way.

C++ sample code (in client program) 

#include <builder.h> //From gSAFE

...

HttpSqlConnection::configure("http://myserver.local","mysampleresource","secretreshash");

HttpSqlConnection conn;
conn.sendReqAll(db_query("usertable")
                   .get("name")
                   .get("birthdate")
                   .get("comm")
                   .join_ffe("account","","usertable","uid","account","connuid")
                   .get("account","balance")
                   .cond_fv("age",Unquoted,"30",">")
                );

Steps of remote sql processing

Resource names

The HttpSqlConn receive the requests within resource names. You can associate the command executing permissions and the availability of the sql tables to this resource names.

The HttpSqlConn module has a global $httpsqlconn object which configurable in in site settings and describe the resource names and the options/working modes of this resources.

Section if _settings.php which enable the "mysampleresource" resource name. 

global $httpsqlconn;
$httpsqlconn->define_routes = true;
$httpsqlconn->resources = [
    'mysampleresource' => [
        'fastid' => 'secretreshash',
        'sqlreconnect' => false,
       // 'sql_user' => 'myuser',
       // 'sql_password' => 'secretpassword',
    ],
];

In the default handling url (When $httpsqlconn->define_routes is true) the client connect to an url which contains the resource name and a fast-id which does a first line authentication and check of the client.

By set of $httpsqlconn->input_encoder = CALLBACKNAME and $httpsqlconn->output_encoder = CALLBACKNAME you can set functions which can achieve additional encoding/encryption/authentication. In default settings this values contains NULL which means that no further encoding used to receive and send data. The gSAFE sample HttpSqlConnection class works this way. If you will set some encoder functions in settings above, you will have to modify the HttpSqlConnection class to use same encoding methods.

Access control

Set the available commands in resources

You can control the availability of the commands within a resource name by implementing HOOK_httpsqlconn_command_enabled hook.

The hook receives two parameters:

  1. $resource The resource name within the request is received.
  2. $command The requested command name.

Note: You can also reach te remote address of the client by get_remote_address() within this hook

The hook have to return one of the following values:

Note1: The NODE_ACCESS_DENY is always stronger than NODE_ACCESS_ALLOW, if both received the result will NODE_ACCESS_DENY

Sample hook which enables all commands for the "mysampleresource" resource: 

function hook_mymodule_httpsqlconn_command_enabled($resource,$command)
{
    if($resource == "mysampleresource")
        return NODE_ACCESS_ALLOW;

    return NODE_ACCESS_IGNORE;
}

Set the SQL table permissions in resources

You can control the SQL table availability within a resource name by implementing HOOK_httpsqlconn_operation_enabled hook.

The hook receives three parameters:

  1. $resource The resource name within the request is received.
  2. $operation can be one of the following
    • "select" - Select/Query data from the table
    • "update" - Update the data in the table
    • "insert" - Insert data to the table
    • "delete" - Delete data from the table
  3. $tablename The related sql table name.

Note: You can also reach te remote address of the client by get_remote_address() within this hook

The hook have to return one of the following values:

Note1: The NODE_ACCESS_DENY is always stronger than NODE_ACCESS_ALLOW, if both received the result will NODE_ACCESS_DENY

Sample hook which enables all operation on the "sampletable" in "mysampleresource" resource: 

function hook_mymodule_httpsqlconn_operation_enabled($resource,$operation,$tablename)
{
    if($resource == "mysampleresource" && $tablename = "sampletable")
        return NODE_ACCESS_ALLOW;

    return NODE_ACCESS_IGNORE;
}