Using information_schema as a method of Form Validation??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Using information_schema as a method of Form Validation??

Post by GM »

Hi all,

My first new topic, please don't bite...

I've done some fairly extensive searches, and can't seem to find anyone who suggests doing this, so I thought I'd sign up and ask for opinions.

I'm running PHP over Apache and mySQL, and I recently had an idea to use the information_schema in my form validation. Within the information_schema are stored all the details of the columns, lengths, data types etc., and so I decided to use it to (partially) validate user input from forms, for example,

all I've got to do is a:

Code: Select all

SELECT COLUMN_NAME, DATA_TYPE, MAXIMUM_CHARACTER_LENGTH, IS_NULLABLE FROM COLUMNS WHERE TABLE_NAME='table_name' AND TABLE_SCHEMA='database_name';
and this gives me the information (example):

"Username", type varchar, length 30, not null
"Password", type varchar, length 32, not null
"Age", type int, length 3, not null

etc.

With this information, and suitable naming conventions on my forms, I can automatically validate a form containing these fields.

Obviously, all I'm validating here is that the data entered by the user will not cause errors at the database level - clearly, I'm not validating actual content - for instance, if a field needs to be within a certain range of values, this would require further more specific validation.

All I'm doing here is asking for feedback - is this a good idea? Are there any security issues? Can you suggest better methods of achieving this? Do you use something similar?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I don't see why you would simulate database validation rules if your database is already capable of applying them.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

timvw wrote:I don't see why you would simulate database validation rules if your database is already capable of applying them.
I understand that the database would throw an error which could then be handled, but I'd prefer to have data arrive to the database that will not cause errors. This method seems to allow a basic form validation which is extendable to all fields and forms in a database with minimal effort.

It just seems neat to me, that using this information you get client-side and server-side validation almost "free of charge", as it were, without manually writing javascripts and field-specific validations.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I used to work for a company that thought this was a great idea. It may, likely was, their implementation of the handling for this, but it ran horrificaly slow. So I'd tiptoe into trying this, but make sure you can easily reverse it if and when you find it to be the same. ;)
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Re: Using information_schema as a method of Form Validation?

Post by Roja »

GM wrote:All I'm doing here is asking for feedback - is this a good idea? Are there any security issues? Can you suggest better methods of achieving this? Do you use something similar?
I don't see anything wrong with it. All you are doing is using the database schema as the definitions for your form validation criteria. Makes sense, seems reasonable. The only concerns I would have would be around multibyte/utf-8 issues, but thats fairly rare and might not apply.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

GM wrote:
timvw wrote:I don't see why you would simulate database validation rules if your database is already capable of applying them.
I understand that the database would throw an error which could then be handled, but I'd prefer to have data arrive to the database that will not cause errors. This method seems to allow a basic form validation which is extendable to all fields and forms in a database with minimal effort.
The problem is that in every application/language/environment where you use that database, you have to replicate the validation stuff... And eventually they will grow out of sync. When you store the rules at the dbms itself the are centralized and up to date.

GM wrote: It just seems neat to me, that using this information you get client-side and server-side validation almost "free of charge", as it were, without manually writing javascripts and field-specific validations.
I see what you're aiming at. It's something i always aks myself: "How much validation do i want to replicate in the GUI?" I agree that it's nice to generate those validation rules, but in that case i'd generate them based on the actual constraints (and not those stored separately)

Eg: Use mysql DESCRIBE function to figure out the constraints and generate JavaScript based on this information...
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

feyd wrote:I used to work for a company that thought this was a great idea. It may, likely was, their implementation of the handling for this, but it ran horrificaly slow. So I'd tiptoe into trying this, but make sure you can easily reverse it if and when you find it to be the same. ;)
I can see that this has potential to be slow, in cases of multi-table updates, and more rigorous checking (dependencies, child/parent records etc). Backing out should be reasonably easy, as the validation would be done via a single function call, which would simply need to be removed or rendered useless.
Roja wrote:I don't see anything wrong with it. All you are doing is using the database schema as the definitions for your form validation criteria. Makes sense, seems reasonable. The only concerns I would have would be around multibyte/utf-8 issues, but thats fairly rare and might not apply.
Thanks - a basic sanity check is just what I needed. To me it seemed fine, but I just wanted to make sure I wasn't doing something completely ridiculous/dangerous/not-recomended. For me multibyte/utf-8 are not a problem as I'm only writing in English and Italian.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

timvw wrote:The problem is that in every application/language/environment where you use that database, you have to replicate the validation stuff... And eventually they will grow out of sync. When you store the rules at the dbms itself the are centralized and up to date.
Maybe I'm not understanding something here... the information_schema is part of the dbms, is it not? When I alter a table, the information_schema is altered to reflect that change automatically, and so my validation should never be out of sync.
timvw wrote: I see what you're aiming at. It's something i always aks myself: "How much validation do i want to replicate in the GUI?" I agree that it's nice to generate those validation rules, but in that case i'd generate them based on the actual constraints (and not those stored separately)

Eg: Use mysql DESCRIBE function to figure out the constraints and generate JavaScript based on this information...
Again, the validation rules would be generated according to the information held in the information_schema. When you do a mySQL DESCRIBE tableName, it pulls the information from information_schema.columns, using DATABASE_SCHEMA = currentDatabase and TABLE_NAME=tableName as keys. In this way, it can present you all the information about the table.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I was under the impression that you where considering another table where you would maintain a schema yourself instead of the one your sql dbms maintains.. Anyway, it seems that i agree with you ;)
Post Reply