ADODB Abstraction in Sahana

Sahana uses the ADODB abstraction library for PHP as its core database abstraction layer. This allows Sahana to be deployed over several database management systems, including MySQL, PostgreSQL, MS SQL, Oracle, etc. You talk to ADODB using PHP functions, it talks to the database with SQL specific to the database that the user has selected.

The Sahana framework instantiates the ADODB connection object and stores a reference to it in the $global['db'] global variable. This object is of type ADOConnection.

function my_function(){
 global $global;
 $db = $global['db'];
 $q = "SELECT * FROM some_table WHERE somefield1=? AND somefield2=?";
 $res = $db->Execute($q,array('parameter1','parameter2');
 while($res && $row = $res->FetchRow()){
   echo $row['column1'];
   echo $row['columnn'];
 }
}

The following code snippet shows a SQL query vulnerable to an SQL injection attack. These types of queries should be avoided to ensure the security of the application.

$db = $global['db'];
$param1 = $_POST['param1'];
$param2 = $_POST['param2'];
$param3 = $_POST['param3'];
 
$query = "INSERT INTO table1(field1,field2,field3) VALUES ('".$param1."','".$param2."','".$param3."')";
$db->Execute($query);

Currently Sahana has a lot of modules with vulnerable SQL queries of the above type. The vulnerability can be overcome by adding the extra overhead of escaping special characters in the $_GET and $_POST variables. But better is to avoid such overhead by writing queries in the following manner:

$db = $global['db'];
$param1 = $_POST['param1'];
$param2 = $_POST['param2'];
$param3 = $_POST['param3'];
 
$query = "INSERT INTO table1(field1,field2,field3) VALUES (?,?,?)";
$db->Execute($query,array($param1,$param2,$param3));

Refer to the Execute documentation for more details

While ADODB supports pagination through its PageExecute() function, doing so has the drawback of sending an additional query internally. The following method is recommended instead:

function shn_skel_pagination(){
    global $global;
    $db = $global['db'];
    $page = $_REQUEST['page_no']; // assign the page number parameter
    $rows_per_page = 10; // set to whatever value you wish
 
    $count_query = "SELECT COUNT(*) FROM table1 WHERE field1=?";
    $res_count = $db->Execute($count_query,array($_POST['param1']));
    $count = $res_count->fields[0]; // obtain count
 
    $page_count = ceil($count/$rows_per_page);
 
    $query = "SELECT * FROM table1 WHERE field1=?";
    $res = $db->SelectLimit($query,$rows,($page-1)*$rows_per_page,,array($_POST['param1']));
 
    while($res && !$res->EOF){
        echo $res->fields[0]." ".$res->fields[1]." ".$res->fields[2];
    }
 
    // print the pagination links
    for($i=1;$i<=$page_count;$i++){
        echo "<a href='index.php?mod=skel&act=pagination&page_no=".$i."'>".$i."</a>&nbsp;&nbsp;";
    }
}

ADODB supports basic transactions without the support of the underlying database. But more advanced uses of transactions depends on the capabilities of the underlying database. For example, MySql requires the php_mysqli extension to be loaded for transaction support.

$db = $global['db'];
 
$db->StartTrans();//start the transaction.
$db->Execute('UPDATE table1 SET field1=?, field2=? WHERE field3=?',array($param1,$param2,$param3));
$db->Execute('UPDATE table2 SET field1=?, field2=? WHERE field3=?',array($param4,$param5,$param6));
$db->Execute('UPDATE table3 SET field1=?, field2=? WHERE field3=?',array($param7,$param8,$param9));
/*
 * if param 10 is null then invalid values were entered by user. 
 */
if($param10==null){
    $db->FailTrans(); // mark the transaction as failed.
}
$failed = $db->HasFailedTrans();// the flag to check whether the transaction failed
$db->CompleteTrans(); // end the transaction

The ADODB system will automatically handle commit on transaction success and rollback on transaction failure. If you need to rollback, mark the transaction as failed by calling $db→FailTrans();

Refer to Transactions for more details

ADODB internaly has a debug flag which when enabled dumps SQL statements to the standard output (web page). This dump includes the parameter values at the time of execution. Use the following code to set the debuging flag

    $db = $global['db'];
    $db->debug = true;
 
    // now any statements executed will be dumped on the web page. Use this option with care when using AJAX, as it corrupts the XML headers.

You can improve the readability of your queries by breaking them down into separate lines as in this example:

$q = "SELECT p.plg_uuid,ct.name,pi.quantity,p.plg_date,pi.can_send_by_date,fr.req_id,fr.req_item_id,fr.fwd_qty,pi.inventory,rf.plg_uuid AS has_plg,cu.name,cu2.name,o.name,c.name FROM rms_pledge p
	       JOIN rms_plg_item pi ON p.plg_uuid=pi.plg_uuid 
	       JOIN ct_catalogue ct ON ct.ct_uuid = pi.item_uuid 
	       JOIN rms_fw_req fr ON fr.plg_id=p.plg_uuid 
	       JOIN rms_req_item ri ON ri.req_uuid = fr.req_id AND ri.item_uuid = fr.req_item_id 
	       JOIN rms_request rq ON rq.req_uuid = fr.req_id 
	       JOIN camp_camp_to_user cpu ON cpu.user_id = rq.reqstr_uuid 
	       JOIN camp_general c ON c.c_uuid = cpu.c_uuid
	       JOIN ims_inv_to_org io ON io.inv_id = pi.inventory 
	       JOIN org_main o ON o_uuid = io.org_id 
	       LEFT OUTER JOIN ct_unit cu ON cu.unit_uuid = pi.unit 
	       LEFT OUTER JOIN ct_unit cu2 ON cu2.unit_uuid = ri.unit 
	       LEFT OUTER JOIN rms_fulfil rf ON rf.plg_uuid=p.plg_uuid AND rf.req_uuid=fr.req_id AND rf.item_uuid=fr.req_item_id 
	       ORDER BY p.plg_date DESC,p.plg_uuid";

• Writing insert queries without specifying the columns.

$query = "INSERT INTO table1 VALUES (?,?,?)"; // <--- WRONG !!!!
$db->Execute($query,array('a','b','c');
 
$right_query = "INSERT INTO table1 (column1,column2,column3) VALUES (?,?,?)"; // <--- CORRECT
$db->Execute($query,array('a','b','c');

• Writing database specific queries. Since ADODB is an abstraction library, you have to write queries in generic SQL, NOT in Database-specific form.

// the following query is mysql specific
$query = "SELECT * FROM table1 LIMIT 0,10"; <--- WRONG  !!!
$res = $db->Execute($query);
 
// it should be written and executed as follows
$queyr = "SELECT * FROM table1";
$res = $db->SelectLimit($query,10,0); <--- CORRECT

• Using unnamed tables in queries and subqueries.

// the following is not recommended
$query = "SELECT * FROM table1 JOIN table2 ON field1=field2 JOIN table3 ON field3=field4 WHERE field5=?";
$res = $db->Execute($query,array('param1');
 
// instead it should be written as follows.
$query = "SELECT * FROM table1 t1 JOIN table2 t2 ON t1.field1=t2.field2 JOIN table3 t3 ON t3.field3=t2.field4 WHERE t3.field5=?";
$res = $db->Execute($query,array('param1');