I thought I had finally devised a good method to dynamically construct SQL queries in , but it turns out I can make a horrible mess of everything.

@ayo I have a theory that the only #SQL that can appear in a client application is "call proc_name (arguments);", if the server is properly configured to accept only these, SQL injection is no longer an issue.

@amiloradovsky SQL injection isn't the problem, though - the code in the screenshot is safe from that. The problem is overly dynamic queries with filters and sorting depending on user input. And this is actually a simple example...

@ayo Oh, I wasn't criticizing the code in the screenshot per se. But sure the more complex the composed query is, the harder it is to ensure it's safety.

@amiloradovsky That is certainly true. Integrating SQL into the host language type system (if it has a static one, i.e. not Perl) really helps with that, but tends to heavily complicate and limit the flexibility of queries.

@ayo But all the flexibility should only be used on the server anyway.

@ayo Have you ever tried DBIx::Class? It's a pretty flexible ORM that allows you to construct queries using nothing but hashes and other #perl data structures. If you're looking to dynamically construct SQL queries easily, it's probably a better place to start (even if you don't use it directly you can mine it's source code for ideas)

@splatt9990 I have, but I wasn't impressed. Not sure about the exact reasons because it's been a while, but from what I remember it seemed both over-engineerd, inflexible and a memory hog. What I'm using now is a thin abstraction on top of SQL::Interp, and that kinda works.

Sign in to participate in the conversation

We are a cute and loving international community O(≧▽≦)O !