Does anyone do database tier field validation?
-
A looong time ago, as part of a middleware builder I wrote, it would generate SQL-DDL scripts to create tables and the stored procedures necessary to access them. One of the things it did was use text cursors to run regular expressions over fields to validate them. Basically, it generated the SQL code to use a table based state machine to walk the fields and validate a regular expression so that if you passed a phone number or an email address in as a string it would reject invalid candidates. The same thing happened at the middle tier, and at the front end web page, so no matter how you hit it, it would *not* let you put invalid data in the database. I've almost never seen this done in practice elsewhere, but it has been a long time since I've done this kind of development professionally, and I was wondering how common a practice it is (maybe using some of the newfangled SQL Server features for example) to do granular field validation like that? One reason I ask is because I have a tool that can potentially generate the SQL-DDL scripts to do this, but I don't know how useful it would be to people in practice.
Real programmers use butterflies
-
A looong time ago, as part of a middleware builder I wrote, it would generate SQL-DDL scripts to create tables and the stored procedures necessary to access them. One of the things it did was use text cursors to run regular expressions over fields to validate them. Basically, it generated the SQL code to use a table based state machine to walk the fields and validate a regular expression so that if you passed a phone number or an email address in as a string it would reject invalid candidates. The same thing happened at the middle tier, and at the front end web page, so no matter how you hit it, it would *not* let you put invalid data in the database. I've almost never seen this done in practice elsewhere, but it has been a long time since I've done this kind of development professionally, and I was wondering how common a practice it is (maybe using some of the newfangled SQL Server features for example) to do granular field validation like that? One reason I ask is because I have a tool that can potentially generate the SQL-DDL scripts to do this, but I don't know how useful it would be to people in practice.
Real programmers use butterflies
Not really, but the applications I work on don't take user input. My responsibility is pretty much just ETLing data from other places into a staging database (mostly with SSIS). There are some places where we check for IP addresses (in particular), but that's about it. And to do IP addresses, I wrote a CLR Function which uses .net's
System.Net.IPAddressclass to convert to VARBINARY(16) or return null. -
Not really, but the applications I work on don't take user input. My responsibility is pretty much just ETLing data from other places into a staging database (mostly with SSIS). There are some places where we check for IP addresses (in particular), but that's about it. And to do IP addresses, I wrote a CLR Function which uses .net's
System.Net.IPAddressclass to convert to VARBINARY(16) or return null.Spinning up the CLR/CLI inside an RDBMS makes me feel dirty.
Real programmers use butterflies
-
Spinning up the CLR/CLI inside an RDBMS makes me feel dirty.
Real programmers use butterflies
Yeah, baby! :cool: It's there anyway. And much faster than so many SQL-implemented chores -- text handling in particular.
-
A looong time ago, as part of a middleware builder I wrote, it would generate SQL-DDL scripts to create tables and the stored procedures necessary to access them. One of the things it did was use text cursors to run regular expressions over fields to validate them. Basically, it generated the SQL code to use a table based state machine to walk the fields and validate a regular expression so that if you passed a phone number or an email address in as a string it would reject invalid candidates. The same thing happened at the middle tier, and at the front end web page, so no matter how you hit it, it would *not* let you put invalid data in the database. I've almost never seen this done in practice elsewhere, but it has been a long time since I've done this kind of development professionally, and I was wondering how common a practice it is (maybe using some of the newfangled SQL Server features for example) to do granular field validation like that? One reason I ask is because I have a tool that can potentially generate the SQL-DDL scripts to do this, but I don't know how useful it would be to people in practice.
Real programmers use butterflies
A rather similar question came up hear maybe half a year ago. That poster got "corrected" for insisting that browser input validation was enuff. I would say: that DB-level validation is absolutely mandatory to protect the DB from data corruption. E.g. somebody my try to enter data with their own client (out of malice or just curiosity). Having data validation-as-you-type is matter of user friendliness. Eg I hate sites that tell you that the password I just entered twice needs more stuff, after having chewed on the entire form for bit.
"If we don't change direction, we'll end up where we're going"
-
A rather similar question came up hear maybe half a year ago. That poster got "corrected" for insisting that browser input validation was enuff. I would say: that DB-level validation is absolutely mandatory to protect the DB from data corruption. E.g. somebody my try to enter data with their own client (out of malice or just curiosity). Having data validation-as-you-type is matter of user friendliness. Eg I hate sites that tell you that the password I just entered twice needs more stuff, after having chewed on the entire form for bit.
"If we don't change direction, we'll end up where we're going"
megaadam wrote:
I would say: that DB-level validation is absolutely mandatory to protect the DB from data corruption. E.g. somebody my try to enter data with their own client (out of malice or just curiosity).
Unless you're in a situation where arbitrary applications can write to your database, serverside validation is sufficient. If you have a big-One company database that dozens of different applications use, well Codethulu help you (because no lesser gods can); and yeah then you probably do need to do validation in the DB.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
A rather similar question came up hear maybe half a year ago. That poster got "corrected" for insisting that browser input validation was enuff. I would say: that DB-level validation is absolutely mandatory to protect the DB from data corruption. E.g. somebody my try to enter data with their own client (out of malice or just curiosity). Having data validation-as-you-type is matter of user friendliness. Eg I hate sites that tell you that the password I just entered twice needs more stuff, after having chewed on the entire form for bit.
"If we don't change direction, we'll end up where we're going"
One problem is that SQL-implemented routines often perform poorly, so "doing it in code" is preferred. So what about when using a Web Service as the front door to the database -- and not allowing direct access to the database? All clients have to go through the front door.
-
One problem is that SQL-implemented routines often perform poorly, so "doing it in code" is preferred. So what about when using a Web Service as the front door to the database -- and not allowing direct access to the database? All clients have to go through the front door.
That works depending on the situation, and depending on how much confidence you have in your DMZ being adequately hardened. When the database is on a LAN, things become a bit different, but even if it's hooked to web facing internet servers behind a DMZ there is always the chance your webserver gets hacked, and when that happens, they at least have the same access to the database the webserver does. If the database is hardened with routines like this it can at least limit some of the damage.
Real programmers use butterflies
-
That works depending on the situation, and depending on how much confidence you have in your DMZ being adequately hardened. When the database is on a LAN, things become a bit different, but even if it's hooked to web facing internet servers behind a DMZ there is always the chance your webserver gets hacked, and when that happens, they at least have the same access to the database the webserver does. If the database is hardened with routines like this it can at least limit some of the damage.
Real programmers use butterflies
Yeah, see? I don't do that Web stuff. :| I stay in the back end and keep my head down.
-
Yeah, see? I don't do that Web stuff. :| I stay in the back end and keep my head down.
I *am* talking about the backend. This is a statement about infrastructure, not front end coding. If your DB is on a LAN, there's a good chance there's no DMZ which means a disgruntled employee could poison the database, probably pretty easily, unless there's security on the tables and validation.
Real programmers use butterflies