NYCPHP Meetup

[Paul Houle]

Randal Rust wrote:
>
> The correct process would be to:
>
> 1. Run fix_magic_quotes() to ensure that all of the backslashes are
> removed (if magic_quotes_gpc is on)
> 2. Run each piece of data through the appropriate function for validation
> 3. Re-insert the slashes using the database specific function
>
    A lot depends on the database you use.  If you use mysql,  you can 
write something like

update x set y='5' where z='50';

where y and z are integer columns of x.

    Postgresql,  MS SQL and many other databases won't accept that.  If 
you're writing your own SQL,  you'll need to quote strings and validate 
numbers.

    Many people claim it's a 'best practice' to use a database API that 
supports place holders...  For instance,  in ADODB,  you can write

$db->Execute("
    Update x set
         y=?
    where
        z=?
",array($y,$z));

    This seems to get good results with MySQL and MS SQL,  but doesn't 
work with access if the fields are numeric.

    Using placeholders is certainly better than not quoting,  but my 
experience is that placeholders + changing code = defects.  Imagine you
write something like

$db->Execute("
    Update x set
       a=?,
       ...24 columns ..
       z=?
    where
       aa=?
       AND ab=?
       AND ac=?
",array($a,$b,$c,$d,...$ac));

    Once you're writing them by the hundreds,  you'll find that it's 
tiresome and error-prone to write your own UPDATE and INSERT 
statements...  Lately I've been using a homebrew active record class 
that lets me write code like

$table=$ar_db->get("characters");
$row=$table->new_record();
$row->firstname="Genma";
$row->lastname="Saotome";
$row->age=45;
$row->insert();

    If I'm using auto-increment columns, with mysql,  I can pick up the 
id  with

$last_insert_id=$row->id;

    In this approach,  strings get quoted and numbers get validated --

$row->age="Forty-Five";

    causes an exception to be thrown.  The real advantage that comes out 
of the active record is that you can build your update/inserts in an 
additive way

$row=$table->new_record();
assign_columns_from_form_elements($row);
assign_complicated_multipart_key($row);
assign_timestamp($row);
$row->insert();

------------

    All that said,  it's a good thing to validate your data;  MySQL, for 
instance,  doesn't throw an error when it gets a bad date,  but it 
stores '0000-00-00',  which probably isn't the behavior you want.  If 
you want your application to behave predictably,  it's good to keep bad 
data out.