Hopefully, I'm not stirring up a hornet's nest or getting myself crucified with this 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. :)

In reply to perl & SQL best practices by ksublondie

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.