Thoughts on Database Schema for Validation?
Posted: Tue Jul 28, 2009 3:17 pm
Ok, inspired in part by CakePHP's Model Validation I created a project which uses a separate database schema file. I didn't like that in CakePHP the model file and the db-schema are combined in the same php file. I like having them separate.
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:
Let me try to explain how the database schema is used in my project. My project uses CodeIgniter and ZendFramework. All my Model methods call either $this->select(), $this->update(), $this->insert() or $this->delete(). Simple enough.
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.
Any thoughts on using DB validation schemas for webapps?
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?