Perl to protect database field name info

Melodious Monks,

A small meditation on a current project.

You have an sql db and are going to dynamically generate a record-input screen with Perl script. First temptation is do something like this (pseudo-code):

$sql = "show columns from my_table";
execute
while (there are $field_names) {
     print "<input type='text' name='".$field_name."'>"
}
[download]

and then the reverse when the form is submitted. BUT, I've now given away the actual column names of my table - one small piece of real info for hackers to exploit.

I could, instead, have a hash of "public" names:

$public_names{$field_1} = "foo";
$public_names{$field_2} = "bar";
foreach $key (keys %public_names) {
     print "<input type='text' name='".$public_names{$key}."'>"
}
[download]

But now I have this intermediate association table to maintain. Sooooo, I thought a nifty alternative would be a home-made field name encoder/decoder:


$sql = "show columns from my_table";
execute
while (there are $field_names) {
     $public_name = &encode($field_name);
     print "<input type='text' name='".$public_name."'>"
}

sub encode() {
     $scrambled =~ tr/[a-z]/[w,t,5,s,c,....,7,a]/;
     return $scrambled;
}
[download]

with a corresponding decode sub for the returning values in the submitted form. No association tables to maintain - and my field name info is secure.

Wadda y'all think?

Forget that fear of gravity,
Get a little savagery in your life.

Comment on Perl to protect database field name info Select or Download Code

Replies are listed 'Best First'.
Re: Perl to protect database field name info by dragonchild (Archbishop) on Feb 13, 2007 at 00:46 UTC
I don't think I'd ever want to jump off a bridge into a valley of cactuses. So, discussing the best technique for doing so isn't very relevant to me. Your database and your users should NEVER NEVER NEVER interface. Your application should provide a useful set of abstractions mapped to your users' domain. Your application should provide ANOTHER useful set of abstractions mapped to your datastore's needs. In my experience, and the experience of several people I trust, there has never been a case where the two domains were identical. Ever. Instead, you should think about what you want your users to be able to do. Present that to them. Then, you should think about how your data wants to be stored. Then store it like that. Your application exists, in part, to help map the two domains together. Don't abrogate your responsibility. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re^2: Perl to protect database field name info by ptum (Priest) on Feb 13, 2007 at 14:22 UTC
I agree with this. I'm in the middle of writing an internal web application using CGI::Application and I succumbed to the temptation of mapping my database tables directly to user functions 'to save time'. It worked OK at first, while I was still in the simple prototype stage, but I am starting to feel the pain now, as my customers happily suggest changes. Because I don't have a very thick layer of abstraction between my user's domain and the database domain, I am finding it very painful to make changes that affect the structure of my database tables, and those modifications create ripples which threaten to destroy my application. Besides, you pretty much have to agree with someone who can correctly spell 'abrogate' and use it effectively in a sentence.	[reply]
Re^3: Perl to protect database field name info by punch_card_don (Curate) on Feb 13, 2007 at 14:45 UTC
OK, then could someone please write something more concrete on this infamous abstraction?!?! I mean, just how abstract can you get in receiving "first_name = bob", cleaning it, and writing it to users.fname? Thanks. Forget that fear of gravity, Get a little savagery in your life.	[reply]
Re^4: Perl to protect database field name info by dragonchild (Archbishop) on Feb 13, 2007 at 19:02 UTC
Re^5: Perl to protect database field name info by punch_card_don (Curate) on Feb 13, 2007 at 23:11 UTC
Some notes below your chosen depth have not been shown here
Re^4: Perl to protect database field name info by ptum (Priest) on Feb 13, 2007 at 16:07 UTC
Re^2: Perl to protect database field name info by punch_card_don (Curate) on Feb 13, 2007 at 14:05 UTC
On further thought, I'm unclear on how what you describe is not the same thing as what I suggested. OK, so , in concrete terms, I believe that what you're saying is that on the user side you name an input form element "xyz". In your application, you're going to run the input value through some regex's to strip dangerous characters. And then on the database side, you're going to write the cleaned value to my_table.column_foo. Yes? Then how is that different (other than leaving out the regex cleaner step for simplicity) from scrambling column_foo into 'xyz' and outputting an html form element of that name, then unscrambling 'xyz' into "column_foo" and writing the cleaned value to it? Forget that fear of gravity, Get a little savagery in your life.	[reply]
Re: Perl to protect database field name info by shmem (Chancellor) on Feb 12, 2007 at 23:29 UTC
No association tables to maintain - and my field name info is secure. Betraying false sense of security. Your database is never being at risk because you reveal that your columns are named "Mom" and "Dad", but because it's environment isn't secure. While your technique is a nice idea, I'd label it "security by obscurity." --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply]
Re^2: Perl to protect database field name info by Joost (Canon) on Feb 12, 2007 at 23:43 UTC
agreed. using the correct DBI constructs and SQL (or better yet, use a wrapper like Class::DBI) you should never have any problem with this. and if you do have sql problems the OP's solution isn't really going to help. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re^3: Perl to protect database field name info by Mutant (Priest) on Feb 13, 2007 at 10:01 UTC
or better yet, use a wrapper like Class::DBI Don't forget DBIx::Class, which IMHO surpassed Class::DBI in just about every way about a year ago. In response to the OP, you may also want to look at Catalyst. From your posts so far, it appears as if you're building web applications. Using a framework like Catalyst (along with Template Toolkit and DBIx::Class) will encourage you to keep your application, database and presentation logic separate (which will save you a lot of pain in the long run).	[reply]
Re: Perl to protect database field name info by Anno (Deacon) on Feb 13, 2007 at 00:28 UTC
I think in `encode()`, the statement `$scrambled =~ tr/[a-z]/[w,t,5,s,c,....,7,a]/;` [download] won't do what you expect it to do. Anno	[reply] [d/l] [select]
Re: Perl to protect database field name info by punch_card_don (Curate) on Feb 13, 2007 at 02:43 UTC
OK, all fair enough. And thanks for the insights. Forget that fear of gravity, Get a little savagery in your life.	[reply]
Re: Perl to protect database field name info by tweetiepooh (Hermit) on Feb 13, 2007 at 15:09 UTC
There are products out there that hide the database schema behind coded attributes but it makes things a real pain. It may be OK in the provided tools but what if you now need to interface something else directly into the database? You now need to now and remember that t55995483 is Surname and t95854778 is Age and so on. Much better to keep attribute and table names nice and clear. It may not be you who has to debug it in the future.	[reply]