Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

comment on

( [id://3333] : superdoc . print w/replies, xml ) Need Help??
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

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

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.