community.egroupware.org: Community wiki

  
Community wiki
EliminateSqlInjectionHowTo
The eGroupWare-API provides a mechanism, wich correctly escapes values of any datatype for use in the SQL commands INSERT or UPDATE.

To use this mechanism, the API has to know about the datatypes of the columns, wich are accessed by the query. This can be achieved with the following functions, usually called in the constructor of your so-class:

/* first get the table-definitions out of our own setup/tables_current.inc.php
(if we only want to get the definitions of a single table, we can pass
the tablename as a second parameter */
$table_definitions = $GLOBALS['phpgw']->db->get_table_definitions('appname');

/* secondly, pass the column-definitions of the table to be accessed back to
the db-class */
$GLOBALS['phpgw']->db->set_column_definitions($table_definitions['tablename']['fd']);

Now, if we want to query the database, instead of using addslashes or db_addslashes to escape the values, we use the special function column_data_implode, wich takes an array as a parameter:

// prepare the array
$qry_data = array(
'column1' => 'value1'
'column2' => 'value2'
'column3' => 'value3'
);

/* let the api escape and quote the values. For all possible parameters
of column_data_implode, refer to the api-documentation */
$keys = implode(',',array_keys($qry_data));
$values = $GLOBALS['phpgw']->db->column_data_implode(',',$qry_data,False);

// query the database with correctly prepared data
$GLOBALS['phpgw']->db->query("INSERT INTO tablename (" . $keys . ") "
. "VALUES (" . $values . ")",__LINE__,__FILE__);

And that's our injection-safe query.
You are here