Overview of SqlHtmlRpt and the Rest Of The Contents of This Directory

Abstract

SqlHtmlRpt.pm is a Perl module for templated reporting from SQL databases. To the best practicable extent, it attempts to conform to the tiered model. The templates that drive it consist of:

• an HTML template
• SQL database queries embedded as comments
• no active middle tier (e.g. Perl) statements embedded in the templates

Substitutions are performed into the template and iterated for each row returned by the query. (Since Content Management Frameworks have become all the rage of late, this paradigm is presumed to be readily assimilable.)

SqlHtmlRpt

The base class is SqlHtmlRpt (pod code). This module handles unsigned integer (sqlnx) and string (sqlsx) CGI parameters which are (hopefully, presumably) safe for substitution into SQL statements, as well as the unsafe "raw" (sqlpx) form. The model is recursive, meaning that another SQL statement can be executed once for each row in the primary statement. Kind of like nested subqueries, but not quite.

OptParamRpt

OptParamRpt (pod code) is a subclass of SqlHtmlRpt which addresses a common and unsafe perversion of sqlpx parameters, which is to alter the SQL statement(s) themselves. You can make this safe by performing some preliminary parameter validation in the enclosing CGI wrapper, certainly. But it has come to my attention that there are hacks for doing this in the template, and then that led to the notion that maybe there "ought to be a better way": something that actually gave something back for the trouble, rather than being a good hack which gets around a particular problem.

This guy introduces sqlox parameters, which might be seen in the context of a dropdown menu of choices. Then he goes kind of wild, and you can do all kinds of substitutions into SQL statements (or elsewhere), (hopefully, presumably) in a safe manner.

Sample Templates

I need to point out that none of these examples will run here!! They are template sourcefiles, and you are supposed to look at the raw source and appreciate it. If your web browser refuses to let you do that, well that is another problem entirely (and not mine!).

rpt-event_summary_by_ip_a.tmpl

This is a basic SqlHtmlRpt template which demonstrates nested queries. It is from a firewall management tool. The tool does not run wherever you found this template... guaranteed!

rpt-safe-sqlpn-hack.tmpl

This is similar to the following template, except that it demonstrates a way of rendering what would have been a sqlpx parameter "safe" by replacing it with a sqlnx parameter, and actually utilising the SQL engine to render it safe! Admit it, that's pretty cool.

rpo-contact-field-sort.tmpl

This is more or less the same as the preceding item, except that it is done with OptParamRpt instead of SqlHtmlRpt. Notice the additional substitution which is utilized for a descriptive header (sorry example, but easy at hand).

Additional Assistance

I really don't mind answering occasional questions either on the telephone or via e-mail. Keep it short and succinct, though. You should be able to find contact information in the source.

Additional Notes

Version Numbers

The version numbers come from an application from which I periodically yank what you find here. Therefore they are not specifically revision numbers of SqlHtmlRpt. The previous publicly posted version was 2.4.

Why are there no complete calling frameworks?

Because I primarily utilize these tools in situations where there are custom calling frameworks, and I make this stuff available because people have said "that's pretty cool, can I use it for...". Nobody has seen fit to provide for posting a framework suitable for general public consumption; if they did I'd post it or link to it.

Why isn't there a version written in Python?

A couple people have asked me this. In fact I utilize Python (as well as Perl) for mail filtering, and the idea has a definite hackish appeal. If I ever have any spare time, it's up there on the list.

Why isn't this on CPAN?

Because I don't have time to package it for CPAN.