Updating table with PL/SQL

[BenP]

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.

[Banana]

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.

[BenP]

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.)

[Banana]

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.

[chucker]

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:

you're opening the flood gates for SQL injection potential
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.