Data validation - was Re: OT: Canadian Tax Database - Mailing list pgsql-general

----- Original Message -----
From: "omar" <omar2@omnicode.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 09, 2007 10:40 PM
Subject: Re: [GENERAL] OT: Canadian Tax Database


> Tom, I promise this isn't a political statement, even though it's on the
> same thread.
> I'm curious what people think about the following statement considering
> the database typing talk being brought up here.  My experience is that
> more times than not I have to put data validation in my client code even
> when it's available on the server, if for no other reason that users don't
> understand what foreign key violation, etc messages mean.  It begs the
> question of whether it's really necessary on the server or not.  SQLite
> seems to take the position that it isn't since there is no referential
> integrity and the following.  To be honest, there's a lot of power in the
> ability to view everything as a string, with of course proper data
> validation.

This risk of this is far too high.  Treating everything as a string is,
IMHO, a very bad idea.

There are, especially for a web application, numerous forms of attack, so I
routinely provide code for client side validation, server side validation
(in a web app or in filters that process the data before providing it to
whatever is going to do something useful with the data.  this includes
designing stored procedures to receive, and validate, data before the data
is stored in the database.  On the client side, the main benefit is to
ensure the user doesn't miss anything that is necessary and that he enters
valid data.   If the user is malicious, and wants to try a SQL injection
attack, nothing you do on the client side can prevent him from creating his
own version of your page bypassing all of your client side validation code.
And it is possible for a scoundrel to try a man in the middle attack
(intercepting a transaction mid stream and trying, e.g., a SQL injection
attack).  So even with client side validation, server side validation is
absolutely essential.  I like Perl for that, but it can be done in your
favourite programming language.  And it can be done in .NET also, if you
prefer.

Maybe I am paranoid, but whether I am writing code to be run at the very
back end, or the very front end, or anywhere between the two, my preference
is to validate the data that specific object has received before I do
anything with it.  That is key in secure application development.  You
generally assume that your system, and any component therein, can been
compromised so you program on the assumption that it can be compromised
somewhere and write code that minimizes or eliminates the damage that can be
done if some component anywhere else in the system has been compromised.
Just 'coz I'm paranoid doesn't mean they're not out to get me.  ;-)   I
value really good system administrators who go the extra mile to make
intranets and systems as secure as humanly possible, but as an application
developer, I never assume they have not overlooked something.  Instead, I
assume the opposite and that therefore, they got everything wrong and that
the intranet and every server in it either has been compromised or will soon
be compromised, and I therefore try to minimize the risk of damage or
violation of data confidentiality or security in a network or on a system
that has been compromised.  I know perfection is not possible, but I hope we
can make it too expensive for a cyber criminal to get what he wants
illegally.  If we make his cost greater than his potential return, he should
rationally move on to easier targets.

Cheers

Ted