Re: Data validation

[prev] [thread] [next] [Date index for 2004/11/10]

On Wed, 10 Nov 2004 09:38:44 -0500, Kingsley Kerce
<kingsley@xxxxxxxxxxxxxxxx.xxx> wrote:

> Why not perform all -- or most -- data validation at the RDBMS level?
> For example, as most of you are aware, PostgreSQL provides a rich set
> of features for programming at the database server level, including a
> Perl interpreter.  You can create custom datatypes, constraints,
> triggers, rules, etc.

Not everyone runs Postgres - there are many CDBI users out there who
use mysql. Myself for one. So doing it all at the database level is
not an option for everyone, choices of database and the ensuing
flamewars aside. ;-)

> For web-based applications, there are many levels at which data
> validation can occur, from high to low:
> * the browser;

Possible, but you can never trust the data coming from a browser. 

> * the portion of the web application that's running on the web server,
> either in the Model (e.g., CDBI constraints and/or triggers) or the
> Controller (Data::FormValidator, CGI::FormBuilder, etc.); or

This is where I do my validation. Currently it's all on the Controller
side (CGI::Application), with some on the CDBI side (column level
constraints, etc).

> * the RDBMS.

Unfortunately not an option for me. :-( Although I would love to try a
real application with Postgres, my hosting provider doesn't offer it
and I haven't gotten around to getting a virtual server and setting it
up myself.

> Pros and cons of data validation at the RDBMS level:
> 
> Pros:
> + Model can be implemented entirely in the lowest level, and
> Controller code is less cluttered by validation logic.

True, but see my previous post about a way to cleanly separate it at
the Controller level. It's possible at this level, but verifying at
the database level is also A Good Thing.

> + Prevents invalid data more closely to the level at which data is
> actually stored.  Suppose you or a colleague modify data in the
> database via a method that bypasses your Controller, e.g., an INSERT
> or UPDATE is issued via the RDBMS's interactive terminal ('psql' for
> PostgreSQL).  If your data validation does not occur at the database
> level, you may (and likely will) end up with invalid data in the
> database.  A constraint in the database would have prevented this.

In my experience, the invalid data problem is more commonly caused by
problems with the business logic. I've just finished analysing a
reporting prolem that was caused by a column having valid data but it
shouldn't have been populated. This condition could have been
prevented by a DB constraint, but I don't think we would have thought
to add it before finding out this problem.

> Cons:
> - Makes porting to another database platform more difficult.
> Counter: This con can be applied to any level, which seems to cheapen
> the argument.  E.g.: Data validation at the Model (CDBI) level makes
> porting to another database abstraction platform more difficult.  Data
> validation at the Controller level makes porting to another language
> platform more difficult.  Data validation at the browser level makes
> porting to another browser more difficult.

People always mention this, but IMHO applications are rarely ported to
a different database/Model because it's usually not worth the effort.
If you want to make an application cross-database independent then
you've got my attention.

> - Untainting, SQL injection attack prevention, etc. still must be
> handled prior to sending data to the RDBMS.

Definitely!

> - Places more load on the database server, as it's doing more than
> just read/writes.
> Counter: This con can be argued at any level.  E.g.: Data validation
> at the Model (CDBI) or Controller level places more load on the web
> server.  Data validation at the browser level places more load on the
> client box.

I'm quite happy with CDBI and DFV, and don't plan to change unless
something _much_ better comes along. So I'm more interested in finding
ways for these two modules to work together better. How can I define
table constraints in such a way that I can pull that metainfo from
CDBI and put it into CGI::App/DFV?

Drew
        -- 
        ----------------------------------------------------------------
 Drew Taylor                 *  Web development & consulting
 Email: drew@xxxxxxxxxx.xxx  *  Site implementation & hosting
 Web  : www.drewtaylor.com   *  perl/mod_perl/DBI/mysql/postgres
 ----------------------------------------------------------------