User Name
Password
AppleNova Forums » Programmer's Nook »

Updating table with PL/SQL


Register Members List Calendar Search FAQ Posting Guidelines
Updating table with PL/SQL
Thread Tools
BenP
Senior Member
 
Join Date: Jun 2005
 
2010-08-23, 13:01

I'm supposed to write a stored procedure that will update a production database given a table name, column name, and value as inputs. This seems like a bad idea to me because of security, performance, and debugging issues, but my boss is pretty insistent.

Is it actually a bad idea? If so, is there somewhere authoritative I could go to prove it? I've searched the Oracle documentation but I can't find something that explicitly says to avoid this.
  quote
Banana
is the next Chiquita
 
Join Date: Feb 2005
 
2010-08-23, 13:13

Well, considering that you probably can code the validation/security check into your stored procedure, it may actually make more sense than giving your users direct access to the tables. In general, I'd prefer using stored procedures over triggers mainly to minimize contention. Furthermore, it's not unheard that DBA would grant access only to stored procedures and deny any access to tables for average users so they have control over what data is going in and who can see what above and beyond the built-in security mechanism for a given RDBMS.

But then again, I'm aware of cases where shops will actively avoid any stored procedures... not because of any inherent flaws, but rather to avoid getting locked into single database product. That is generally true for web applications, I think.

Finally, since your boss is basically asking for a dynamic SQL, I'm 99% sure that cannot be meaningfully parameterized away short of using, well, dynamic SQL. I would be *extremely* uncomfortable allowing my users to execute a dynamic SQL directly or indirectly, and at least with stored procedure, one can encapsulate the logic and protect them from inputting in system tables or anything that they shouldn't be doing. Note, that also can be done in the application level but that has its own set of issues, and again is generally unrelated to security/technical merits but rather the business needs as is the case with the web application mentioned above.

HTH.
  quote
BenP
Senior Member
 
Join Date: Jun 2005
 
2010-08-23, 13:19

Quote:
Originally Posted by Banana View Post
Well, considering that you probably can code the validation/security check into your stored procedure, it may actually make more sense than giving your users direct access to the tables. In general, I'd prefer using stored procedures over triggers mainly to minimize contention. Furthermore, it's not unheard that DBA would grant access only to stored procedures and deny any access to tables for average users so they have control over what data is going in and who can see what above and beyond the built-in security mechanism for a given RDBMS.

But then again, I'm aware of cases where shops will actively avoid any stored procedures... not because of any inherent flaws, but rather to avoid getting locked into single database product. That is generally true for web applications, I think.

Finally, since your boss is basically asking for a dynamic SQL, I'm 99% sure that cannot be meaningfully parameterized away short of using, well, dynamic SQL. I would be *extremely* uncomfortable allowing my users to execute a dynamic SQL directly or indirectly, and at least with stored procedure, one can encapsulate the logic and protect them from inputting in system tables or anything that they shouldn't be doing. Note, that also can be done in the application level but that has its own set of issues, and again is generally unrelated to security/technical merits but rather the business needs as is the case with the web application mentioned above.

HTH.
Thanks, Banana! The thing is, the procedure will be called from a web application written in Perl so I'd validate the inputs in Perl anyways. I don't see any advantages in the stored procedure. Also, everyone in my group is a business user and we're doing this ourselves so that if our needs change we don't have to go to IT for support. (That's why I never know what I'm doing.)
  quote
Banana
is the next Chiquita
 
Join Date: Feb 2005
 
2010-08-23, 13:26

Understandable. I can only say that this is basically what you'd tell your boss for why we shouldn't use stored procedures. I'd be surprised if stored procedures were actually less secure than an external script and wouldn't try to argue that way anyway, regardless of the RDBMS product we're talking about.
  quote
chucker
 
Join Date: May 2004
Location: near Bremen, Germany
Send a message via ICQ to chucker Send a message via AIM to chucker Send a message via MSN to chucker Send a message via Yahoo to chucker Send a message via Skype™ to chucker 
2010-08-23, 13:41

Quote:
Originally Posted by BenP View Post
This seems like a bad idea to me because of security, performance, and debugging issues, but my boss is pretty insistent.

Is it actually a bad idea?
Yes. You can't do parametrization that way, which means, among other things:
  1. you're opening the flood gates for SQL injection potential
  2. the RDBMS can't optimize your queries should they repeat again, because the objects (e.g. columns) are entirely arbitrary, so no indexing and statistics are possible

Needless to say, you're throwing most benefits of a relational database out the window. Might consider a dictionary-based alternative, in that case.

There are reasons to do this, but a catch-all "update x set y=z" procedure sounds absolutely horrid.
  quote
Posting Rules Navigation
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Post Reply

Forum Jump
Thread Tools
Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a GUID Partition Table to install Leopard? turtle Genius Bar 17 2007-11-23 17:08
How to Create Excel Table in Word 2004? gbh Genius Bar 7 2007-06-21 10:39
CSS noob need schooling (Using CSS to replace a table) ThunderPoit Programmer's Nook 24 2006-08-28 20:16
Way to Insert Table in vBulletin? JK47 Genius Bar 15 2006-06-04 00:51
Futuristic Furniture: Tasting Table curiousuburb AppleOutsider 2 2006-01-19 13:30


« Previous Thread | Next Thread »

All times are GMT -5. The time now is 12:51.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004 - 2018, AppleNova