All of my sql updates, inserts, and deletes are first passed by this schema. I wrote update() insert() delete() functions to do this validation automatically.
Why? Because I can accept a post from a visitor and send the un-checked data to my database without having to check it. My schema filters for innocent and not so innocent violations.
Here is an example of a database schema:
Code: Select all
$schemas = array('database_name_hidden'=>array(
'assigned_weeks'=>array(
'id'=>array('id'),
'user_id'=>array('foreign_id','users'),
'week_number'=>array('posint'),
'year'=>array('posint'),
'unit_id'=>array('foreign_id','units'),
'claim_listing'=>array('posint'),
'created'=>array('created'),
'modified'=>array('modified'),
'resort_id'=>array('foreign_id','resorts','required'),
),
'trade_listings'=>array(
'id'=>array('id'),
'assigned_week_id'=>array('foreign_id', 'assigned_weeks','required'),
'listing_assigned_week_id'=>array('foreign_id', 'assigned_weeks'),
'opposite_id'=>array('numeric'),
'listed'=>array('bool'),
'prev_id'=>array('foreign_id','trade_listings'),
'next_id'=>array('foreign_id','trade_listings'),
'listing_email'=>array('email'),
'listing_description'=>array('text'),
'trade_confirmation_number'=>array('text'),
'external_resort_id'=>array('foreign_id','resorts'),
'external_unit_number'=>array('text','size'=>array(1,6)),
'external_start_time'=>array('time_future',),
'external_end_time'=>array('time_future'),
'admin_comment'=>array('text'),
'comment'=>array('text'),
'created'=>array('created'),
'creator'=>array('creator'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
'resort_id'=>array('foreign_id','resorts','required'),
),
'realtor_listings'=>array(
'id'=>array('id'),
'photo1'=>array('filename'),
'photo2'=>array('filename'),
'photo3'=>array('filename'),
'photo4'=>array('filename'),
'photo5'=>array('filename'),
'photo6'=>array('filename'),
'price'=>array('posint','required'),
'description'=>array('text','required'),
'unit_id'=>array('foreign_id','units'),
'realtor_id'=>array('foreign_id','users'),
'created'=>array('created'),
'modified'=>array('modified'),
'resort_id'=>array('foreign_id','resorts','required'),
),
'resorts'=>array(
'id'=>array('id'),
'name'=>array('company','required','unique'),
'start_days'=>array('text'),
'created'=>array('created'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
'street'=>array('street'),
'city'=>array('city'),
'state'=>array('state_code'),
'country'=>array('country_code'),
'postal_code'=>array('text'),
'telephone_us'=>array('phone'),
'telephone_foreign'=>array('phone'),
'fax'=>array('phone'),
'email'=>array('email'),
'homepage'=>array('url'),
'contactpage'=>array('url'),
'enable_real_estate'=>array('bool'),
),
'connected_resorts'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts'),
'connected_resort_id'=>array('foreign_id','resorts'),
'created'=>array('created'),
'creator'=>array('creator'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
),
'unit_descriptors'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts'),
'descriptor'=>array('text','required','size'=>array(6,100)),
'created'=>array('created'),
'creator'=>array('creator'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
),
'units'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts','required'),
'bathrooms'=>array('posint','required'),
'bedrooms'=>array('posint','required'),
'floors'=>array('posint','required'),
'unit_descriptor_id'=>array('foreign_id','unit_descriptors'),
'number'=>array('text','required'),
'description'=>array('text'),
'created'=>array('created'),
'modified'=>array('modified'),
),
'users'=>array(
'id'=>array('id'),
'username'=>array('text','size'=>array(4,32),'required'),
'password'=>array('hex','size'=>array(32),'required'),
'logins'=>array('posint'),
'last_login'=>array('time'),
'first_login'=>array('time'),
'salt'=>array('text','size'=>array(32),'required'),
'first_name'=>array('name'),
'last_name'=>array('name'),
'super_admin'=>array('bool'),
'admin'=>array('bool'),
'email'=>array('email'),
'unit_id'=>array('foreign_id','units'),
'resort_id'=>array('foreign_id','resorts','required'),
'realtor'=>array('bool'),
'company'=>array('company'),
'phone'=>array('phone'),
'homepage'=>array('url'),
'thumb'=>array('url'),
'created'=>array('created'),
'modified'=>array('modified'),
),
));
Inside select(), update(), insert() and delete() is where the data passes through validation. Here is my function update. Please excuse my terrible code. I hope you can overlook sloppy code to understand the idea that I can trying to accomplish.
Please notice line #20! The function validate_record() is used to validate with the database schema automatically for every database update.
Code: Select all
function update($table_name='', $id=0, $update_data=array(), $params=array(), $db_name='')
{
$result = false;
if (@READ_ONLY_MAINTENANCE===true)
{
add_flash(i18n('read_only_maintenance_error'),'error');
}
elseif ($id and !is_numeric($id))
{
add_flash("Incorrect data request for `$id`. Please go to previous page and try again.",'error');
}
elseif ($id && $table_name && count($update_data))
{
$this->burn_cache();
if ($this->has_permission(__function__,$table_name,$id))
{
$valid_data = $this->validate_record(__function__, $table_name, $id, $update_data,$params);
if ($valid_data)
{
//-- build `sets` --//
$temp = array();
foreach ($valid_data as $field=>$value)
{
$temp[] = " $field=? ";
$statements[] = $value;
}
//-- assign `set` --//
if (count($temp))
{
$set = implode(', ', $temp);
}
//-- build `wheres` --//
$temp = array();
if (@is_array($params['filters']))
{
foreach ($params['filters'] as $field=>$value)
{
$temp[] = " $field=? ";
$statements[] = $value;
}
}
if ($id)
{
$temp[] = " id=? ";
$statements[] = $id;
}
//-- assign `where` --//
if (count($temp))
{
$where = implode('AND', $temp);
}
// generate sql
$sql = "UPDATE $table_name SET $set WHERE $where";
// fire off query
$result = $this->query($sql, $statements);
if ($result)
{
if (!@in_array('flash',$params['no']))
{
$table_schema = $this->get_table_schema($table_name, $db_name);
$record_description = (@$table_schema[0]['record_description'])? $table_schema[0]['record_description']: $table_name ;
if (!@in_array('flash',$params['no'])) add_flash("$record_description updated.",'success');
}
}
else
{
if (!@in_array('flash',$params['no'])) add_flash('Update failed.','error');
}
}
else
{
if (!@in_array('flash',$params['no'])) add_flash('Record not updated.','warning');
}
}
else
{
add_flash("No permission to update: table:[$table_name] , id:[$id]",'debug');
}
}
else
{
internal_error();
}
return $result;
}
Any thoughts on using DB validation schemas for webapps?