storing perl code in database

sflitman has asked for the wisdom of the Perl Monks concerning the following question:

I have a complex PostgreSQL database application which requires extensibility using a scripting language, so naturally I thought of Perl. Does it make more sense to:

Store function bodies in a table and eval them:

# SQL
create table _perlsub (name text,code text);
insert into _perlsub (name,code) values ('get_time',
'my $x=shift; scalar localtime $x');

# PERL
# invoke code for any function, assume strict, $dbh is active, error c
+hecking omitted for brevity
my $func='get_time';  my $arg=1226940421;  my %perlsubs;
unless (exists $perlsubs{$func}) {  # cache compiled subs
   my $sth=$dbh->prepare("select code from _perlsub where name='$func'
+");
   $sth->execute();
   my $code=$sth->fetchrow;
   $sth->finish;
   eval "$perlsubs{$func}=sub $func { $code }";  
}
print $perlsubs{$func}->{$arg};  # prints Mon Nov 17 16:47:01 2008
[download]

OR, would it be better to use something like Module::Loader, and store the individual routines (there are about 18 of them in the production environment) in individual .pm or .pmc files?

The application is running Apache with mod_perl/DBI/DBD::Pg on RedHat 9, if that helps to know. The extensible subs all do things like data validation or generating pieces of the interface like findboxes and alphalinks.

Appreciate all commentary, critical and otherwise!

SSF

Comment on storing perl code in database Download Code

Replies are listed 'Best First'.
Re: storing perl code in database by graff (Chancellor) on Nov 18, 2008 at 02:05 UTC
Personally, I'd feel better with the code base in a set of files (e.g. where normal version control and debugging tools normally work). If you want to make sure you have proper "referential integrity" between names of procedures in your database and the names of module files in your code base, I expect that would be fairly easy to handle in any of at least a few different ways. Using a "require" statement with a properly untainted/validated module name would seem like the safest, most accountable way to execute code in a data-dependent run-time environment. Another issue to ponder (which might favor storing code as files rather than table fields): compare how write-access is controlled in the two storage methods, and who has that access. Which one are you more comfortable with, in terms of being sure that five minutes/hours/days/weeks from now, your code will still be there? It's true that access control in the database can be controlled in lots of interesting ways, but is your setup one of the relatively rare cases where DBAs actually implement detailed specifications for access permissions on particular fields of particular tables for particular users on a regular basis, and do you consider that method of control to be preferable (all things considered) than what you get with normal file-permission settings? I expect some people would answer "yes", but not most people.	[reply]
Re^2: storing perl code in database by sflitman (Hermit) on Nov 18, 2008 at 03:35 UTC
Very grateful for your insightful response, graff. I think you've sold me on the require approach, since the bigger problem might be that I could lose referential integrity if the database table got corrupted. The permission system with Postgres is not as detailed as MySQL, and it may make more sense from a performance standpoint for the production system to have .pm files anyway for frequently used code snippets. Would something like AUTOLOAD be useful in this scenario? SSF	[reply]
Re: storing perl code in database by jethro (Monsignor) on Nov 17, 2008 at 18:10 UTC
I couldn't find Module::Loader. Can it reload the module while mod_perl is running? If not, you would have to restart your apache after changing a script Do you trust your users to write correct scripts? If not, you want to use eval to at least recover from syntax errors gracefully.	[reply]
Re: storing perl code in database by gone2015 (Deacon) on Nov 18, 2008 at 01:15 UTC
Perhaps I'm paranoid, but I'd worry about evaling stuff straight out of a database... do you have sufficient controls to ensure that someone cannot put Bad Stuff (tm) in the database ? Unless there's a need for rapid and frequent extensions, where you really have to build this out... I'd favour storing some sort of name in the database, and mapping that at run time to the relevant routine -- with careful validation of any arguments (of course).	[reply]
Re^2: storing perl code in database by sflitman (Hermit) on Nov 18, 2008 at 03:31 UTC
At present I have absolute security, in the sense that only someone with root access to the server could modify the _perlsub data inappropriately. At the same time, I know how lame that sounds! I kind of like the idea of the database holding stubs, with the code in an external file...but then that comes down to just using .pm files again. SSF	[reply]
Re: storing perl code in database by eye (Chaplain) on Nov 17, 2008 at 21:42 UTC
I'm not a DB programmer, so this may be off base: Does your version/configuration of postgres support PL/Perl? PL/Perl handles storing Perl functions for you. I believe it shifts execution of the stored Perl functions onto the database's host, so it may not be what you want. It would probably also require a change to the architecture of your application.	[reply]