It's a security requirement for the system. No SQL can be run against the database unless it's reviewed by a human first. This is fine, because we need to run less than one query a day, but the queries are long and complicated and we could save a significant amount of time (and, hence, money) by using a script to generate them, then just have a human sanity check them. | [reply] |
Hmm. A human is going to review an sql statement that "...includes random bytes than can span the entire 0-255 range." That sounds like a fascinating challenge. But the more I think about, the more it sounds like the human won't really be able to look at such an sql statement the same way that a DBI database handle would.
If the goal is to provide for sensible manual validation of sql transactions before they are executed, I would look for a method that involves printing the sql statement in a manner similar to what you would prepare via DBI: print the sql syntax legibly (with line feeds and other whitespace as appropriate), and suitable placeholders for data values -- but maybe something more detailed than just "?" -- then print the list of data values (maybe in escaped form, if that's relevant, but the formatting would depend on whether the human should be validating the data values as well as the sql syntax). For example:
update big_table set easy_column=VAR1, messy_column=VAR2
where index_column=VAR3
VAR1='easy_data_value'
VAR2='%01f%F8%F6-b$E5r%04'
VAR3='123'
I'm assuming that after the human has approved the proposed statement (and data), the method for actually executing the statement will be programmatic, and based directly on the data that the human reviewed and approved. The sort of review format suggested above could satisfy that sort approach. | [reply]
[d/l] |
Once the basic template of your SQL-query is agreed upon and approved by whatever security-chief needs to sign-off on it, all you have to check is the data which is being entered. The usual placeholders and their implied quoting rules should take care of avoiding anything bad happening to your database (such as random SQL-injection attacks). The problem is IMHO probably more with the data than its quoting.There is only so much paranoia you can live with: if you cannot trust the placeholder and quoting rules of DBI/DBD (a tried and trusted set of modules), you can as well ask to check and approve the raw stream of bytes going through your sockets, pipes or TCP/IP stack to the database. Who knows wether or not someone has recompiled your modules or Perl to inject something into these streams?
CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law
| [reply] |