Tired with adding quotes around vars for SQL query? Do you want to make sure supplied values are valid for fields? Do you want to use database default value, if user send no values and default value is defined for the field? Then this function might be useful.

By : yohgaki

<?php
/*
* Description:
* array get_db_values(resource db_link_id, string table_name, array fields)
*
* Parameters:
* db_linkid PostgreSQL link resource
* table_name table name - needed to get metadata.
* fields array(<field name> => <user input>) All field name should much database field name.
*
* Return Value:
* array(<field name> => <value for INSERT/UPDATE>) Return NULL if error.
*
* It converts assoc array data so that values are sutable for INSERT/UPDATE
* SQL statements. (WHERE clause also)
* All array key should match database field names. Return NULL if error.
*
* It checks if there is a default field value in the table definition. If there
* is, it does not return those fields.
* If there is no input (null string for field), it set NULL for the field.
* If quote is required, add slashes and put quotes.
*
* Format checks are limited. Complex format checks should be done
* by other functions/class other than this function.
* (For example, HTML FORM class)
*
* Not all PostgreSQL data types/formats are supported.
* OID is treated as invalid. You need to modify to allow if you want to
* update/insert OID type.
*
* Function name sounds odd to me. Anyone have better name for it?
* email: yohgaki@hotmail.com
*
*/

function Get_DB_Values($db, $table, $fields) {

// Get Metadata
$meta = get_Metadata($db, $table);
if (!$meta) {
trigger_error('Get_DB_Values(): Failed to getting metada for '. $table, E_USER_WARNING);
return NULL;
}

// Check values
foreach($fields as $k => $v) {
if (!isset($meta[$k])) {
trigger_error('Get_DB_Values(): Non-existent field. Table: '. $table .'. Field: '.$k, E_USER_WARNING);
return NULL;
}
$v = trim($v);
// Value is NULL and default is defined, use DB default.
if ($v !== '' || !$meta[$k]['hasdefault']) {
switch ($meta[$k]['type']) {
case 'bool':
// Does not support numeric type. Must be string.
$v = strtolower($v);
if ($v === '') {
$ret[$k] = 'NULL';
}
elseif ($v == 't' || $v == 'true' || $v == 'y' || $v == 'yes' || $v == 'on') {
$ret[$k] = 'TRUE';
}
elseif ($v == 'f' || $v == 'false' || $v == 'n' || $v == 'no' || $v == 'off') {
$ret[$k] = 'FALSE';
}
else {
trigger_error('Get_DB_Values(): BOOLEAN type has invalid value. ', E_USER_WARNING);
return NULL;
}
break;

case 'cidr':
case 'inet':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format - 10.10.10.10 or 10.10.10.10/32
elseif (!ereg('^([0-9]{1,3}\.){3}[0-9]{1,3}(/[0-9]{1,2}){0,1}$',$v)) {
trigger_error('Get_DB_Values(): INET/CIDR type has invalid format. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = "'$v'";
}
break;

case 'float':
case 'float8':
case 'numeric':
case 'money':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format - Does not support '1.111E+10' notation.
elseif (!ereg('^[0-9]*\.[0-9]*$|^[0-9]*$', $v)) {
trigger_error('Get_DB_Values(): FLOAT/NUMERIC/MONEY type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = $v;
}
break;

case 'interval': // Only allow INTEGER for interval
case 'int':
case 'int2':
case 'int4':
case 'int8':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format
elseif (!ereg('^[0-9]*$',$v)) {
trigger_error('Get_DB_Values(): INTEGER/INTERVAL type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = $v;
}
break;

case 'bpchar': // char type
case 'char':
case 'text':
case 'varchar':
// NO length check - char/varchar will be truncated by DB if it's too long.
$ret[$k] = ($v !== '') ? "'".addslashes($v)."'" : 'NULL';
break;

case 'timestamp':
case 'datetime':
case 'date':
// Check format - "2001/02/11 14:59:01", "2001/4/1 15:10" or "2001/11/15".
if ($v === '') {
$ret[$k] = 'NULL';
}
elseif (!ereg('^([0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2})([ \t]+(([0-9]{1,2}:[0-9]{1,2}){1}(:[0-9]{1,2}){0,1})){0,1}$',$v, $regs)) {
trigger_error('Get_DB_Values(): DATE/TIMESTAMP type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = "'{$regs[1]} {$regs[3]}'";
}
break;

case 'oid': // OID type should not be INSERTed nor UPDATEd. (Add oid type if you use oid for BLOD)
case 'serial': // Serial type should not be INSERTed nor UPDATEd. So treat as invalid type.
default:
trigger_error('Get_DB_Values(): INVALID datatype ', E_USER_WARNING);
return NULL;
break;
}
}
}
return $ret;
}


// Return matadata
// return NULL if error
// This is the same function
// http://www.zend.com/codex.php?id=481&single=1
function Get_MetaData($db, $table) {
$rows = 0; // Number of rows
$qid = NULL; // Query result resource
$meta = array(); // Metadata array - return value

// See PostgreSQL developer manual (www.postgresql.org) for system table spec.
// Get catalog data from system tables.
$query=<<<EOQ

SELECT
a.attname,
a.attnum,
t.typname,
a.attlen,
a.atttypmod,
a.attnotNULL,
a.atthasdef
FROM
pg_class as c,
pg_attribute a,
pg_type t
WHERE
a.attnum > 0 AND
a.attrelid = c.oid AND
c.relname = '$table' AND
a.atttypid = t.oid
ORDER BY
a.attnum;

EOQ;

$qid = pg_Exec($db, $query);

// Check error
if (!is_resource($qid)) {
trigger_error('Get_Metadata(): metadata query fialed ', E_USER_WARNING);
return NULL;
}

$rows = pg_NumRows($qid);
// Store meta data
for ($i = 0; $i < $rows; $i++) {
$field_name = pg_Result($qid,$i,0); // Field Name
$meta[$field_name]['id'] = pg_Result($qid,$i,1); // Attrbute ID
$meta[$field_name]['type'] = pg_Result($qid,$i,2); // Data type name
$meta[$field_name]['len'] = pg_Result($qid,$i,3); // Length: -1 for variable length
$meta[$field_name]['modifier'] = pg_Result($qid,$i,4); // Modifier
$meta[$field_name]['notnull'] = (pg_Result($qid,$i,5) === 't' ? TRUE : FALSE); // Not NULL?
$meta[$field_name]['hasdefault'] = (pg_Result($qid,$i,6) === 't' ? TRUE : FALSE); // Has default value?
}

pg_FreeResult($qid);

return $meta;
}


?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

blog comments powered by Disqus