LxCenter Forum: API and Integration » Adding hacklist to API

Not sure if it already exists in the API or not.. I wasn't able to find a way to run a custom SQL via API and get the results so I wrote a tiny hack hopping it will help others too.

Use this hack on your own risk, I'll not be responsible if things go south.

Background:
In my case I want to search if an email address exists in client table. On the forums I saw an example of using:

-> action=simplelist & resource=all-client
-> then parse through the list to find a match

This is not going to work for me. I need a simple SQL lookup

Solution:
Files to be modified (on master):
1. /usr/local/lxlabs/kloxo/httpdocs/htmllib/lib/objectactionlib .php
2. /usr/local/lxlabs/kloxo/httpdocs/htmllib/lib/commandlinelib. php

snippet for #1 objectactionlib.php
Around line 51 modify the code so it looks like:

    
    if ($opt['action'] === 'hacklist') {
        $must = array('class', 'arrHackParams');
    }
    elseif ($opt['action'] === 'simplelist') {
	$must = array('action', 'resource');
    .....

Around line 80, modify the code so it looks like:

    if ($opt['action'] === 'hacklist') {
        json_print_result($opt, $list);
    }
    else if ($opt['action'] === 'simplelist') {
	json_print_result($opt, $list);
    ....

snippet for #1 commandlinelib.php
Add this new function (preferably towards the bottom)

/**
 * hacklist lets you run sql query
 *
 * @param array $param which has the following keys:
 *      - class: eg client, domain
 *      - arrHackParams: json encoded data which has:
 *          - arrWhere: where criteria array
 *          - arrFields: array of fields we want to return
 *          ex: to get a list of clients that match contactemail = 'email@domain.com' 
 *          (following array will be json_encoded())
 *
 *          'arrHackParams' => array(
 *              'arrWhere' => array(
 *                    'contactemail' => array('type' => 'string', 'value' => 'email@domain.com')
 *               ),
 *              'arrFields' => array('nname', 'parent_cmlist', 'used_q_backup_flag')
 *          )
 * @return array of results
 */
function __cmd_desc_hacklist($param)
{
    $ipdb = new Sqlite(null, $param['class']);
    $arrHackParams = json_decode($param['arrHackParams'], true); 
    foreach($arrHackParams['arrWhere'] as $f => $v)
    {
        if ($queryWhere) $queryWhere .= ' AND ';
        $queryWhere .= '`'. $f .'` = "'. $v['value'] .'"'; //@todo escape for SQL injection
    }
    return $ipdb->getRowsWhere($queryWhere, $arrHackParams['arrFields']);
}

Usage:

Suppose you want to search for client whose email address is email@domain.com and you want the API to return nname, parent_cmlist, used_q_backup_flag

Start by building arrHackParams array like so:

$arrHackParams = array(
    'arrWhere' => array(
        'contactemail' => array(
                                'type' => 'string', // for future
                                'value' => 'email@domain.com')
    ),
    'arrFields' => array('nname', 'parent_cmlist', 'used_q_backup_flag')
)

The above code will translate in SQL as:

SELECT nname, parent_cmlist, used_q_backup_flag FROM CLASS WHERE contactemail = "email@domain.com"

Remember that you need to pass json encoded value of $arrHackParams to the API

$arrHackParams = json_encode($arrHackParams) and set this to API..

The POST (or GET depending upon your implementation) it would look like

login-class=client&
login-name=API_USER&
login-password=API_PASSWORD&
output-type=json&
action=hacklist&
class=client&
arrHackParams=%7B%22arrWhere%22%3A%7B%22contactemail%22%3A%7B%22type%22%3A%22string%22%2C%22value%22%3A%22email%40domain.com%22%7D%7D%2C%22arrFields%22%3A%5B%22nname%22%2C%22parent_cmlist%22%2C%22used_q_backup_flag%22%5D%7D&

I have attached the two files, in most cases if you are using the latest version 6.1.10 you should be able to copy and replace.

Hope it helps.