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.
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
