ksublondie has asked for the wisdom of the Perl Monks concerning the following question:
As the only programmer in the office and with full rein of the sql servers (albeit, not fully knowledgeable however much I would like to be), I have always in the past developed my sql queries directly from my perl scripts...of course doing my due diligence in testing any input to prevent injection and controlling user security. Now that I'm discovering more and more about what can be done directly inside sql itself, I find myself in a dilemma on what I should do inside my perl and what should be done on the sql server (sql scripting, stored procedures, dynamic sql, etc).
Of course security is priority #1 followed closely by performance. So testing my input through perl is a no-brainer. Maybe I'm missing something, but does dynamic sql on the server provide THAT much extra protection from injection? Is perl faster in developing ever changing complex queries or is sql faster with cached dynamic sql through stored procedures on the server? What are the best practices? What is the preferred course if you don't have a dba controlling what access you have?
I'm going to assume that the default answer by a dba is going to be "do everything on the db server" and likewise, the programmer will say "do everything on the client", but I like to think that the perlmonks rise to a higher level and will honestly recommend the best course of action independent of their own prejudices. :)