Page 1 of 1

Thoughts on Database Schema for Validation?

Posted: Tue Jul 28, 2009 3:17 pm
by anarkhos
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:

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'),
    ),
));
 
 
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.

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?

Re: Thoughts on Database Schema for Validation?

Posted: Tue Jul 28, 2009 3:19 pm
by anarkhos
Also, fyi. In my schema I break the attributes for a field into two groups: types (the first attribute), and rules. here are the types I have defined (I won't bore you with the regex if/else i wrote for each type).

email
domain
url
ip_address
phone
id
bool
time
time_future
time_past
datetime
enum
text
alpha
alpha_numeric
alpha_dash
numeric
created
creator
zipcode
modified
modifier
foreign_id
country_code
state_code
serialized
currency
hex
price
a_n_d
cc_number
name
address
city
company
password6
hex_color
tags
xml
json
percent
integer
posint
filename

Also, here are the rules which can be applied to any of the types:

required
size
unique
modifiers
default
confirm
assigned_to
tag_name