It is absolutely essential that you separate query selection from SQL assembly. If there is any chance that a query can be hand crafted by a user through any form of transmission methodology, you must validate the selections and the value matches explicitly. To do otherwise is courting disaster by failing to control your data.

In every system that I have used, I have found that the program must take explicit logic to control the passage of that request. You can automate the validation, limit the query set by value, and/or provide free text fields throught text processing. I just don't assume that my or corporate best interests are shared by the user.

Well thought out query formulation on the back-end is just a matter of good design. My processes have usually been designed around separate variables for the fields to return, the from/join tables and associations, and each of the where/group/order/limit components. With simple if blocks I control sets of fields and their associated query requirements.

So, for a certain set of each field groups, inclusion of the explicit components to satisfy the query are appended.

I've also found it most useful to control the actual report returned by reading the query response and transmitting the data using the order and field filtrations appropriate for the results. Things like date format, currency/decimal format, internal security conversions (protecting privacy/security information) usually benefit from post query conversion.

There is no getting past security concerns at all levels. No matter who you are serving, it is necessary to assume that abuse will be tried. No simple conversion from user to result is going to give you that protection.

Thanks for the replies guys. I am not too concerned about abuse or security for this first run thru. I would just like to be able to do the things I mentioned above. The front end is built in ruby on rails, so I am not sure I can use template toolkit.

I believe the suggestion was to use Template Toolkit to generate the SQL, not HTML. Another approach is to use a SQL builder. For simple things, SQL::Abstract can work. For complicated things, SQL::Interpolate or Rose::DB::Object::QueryBuilder are better.

[% FOREACH item IN sqldata %]
<colitem>[% item %]</colitem>
[% END %]
[download]

In any case, if the user's job is simply to select from a finite set of column names and put the selected items in a chosen order, the "dynamic SQL" part seems pretty easy, because you have a constant statement frame that starts with SELECT ends with  FROM my_table [WHERE whatever...]", and has potentially two slots to be filled in by the user:

• the set of column names (and their relative ordering) to be placed between SELECT and  FROM ...
• (possibly?) an  ORDER BY ... clause to be added at the end.

You already have the set of column names and their ordering provided as CGI parameters, so just put those together with the rest of the query statement. If that's giving you trouble, post some code to show us what kind of problem you are having.

As for the XML generation part, it looks like XML::Generator might not be the right tool for your job here, because it seems to assume that the caller wants to use function calls as nodes in the XML tree.

I think you want something that will generate XML from a hash structure (which can easily be built from the data you get after executing your dynamic select statement) -- look at XML::Simple instead. Again, if you have trouble with that, post some code and sample data, or else our help will be limited to generalities or poor examples.

I think I got SQL Abstract working like it should, selecting the correct columns (but I am not inserting them sorted based on lowest to highest, how would I do that?) But this is what I am doing now...

my $sql = SQL::Abstract->new;

        ($group,$source,$orig,$term,$i_sig_start,$cdr_day,$call_status
+,$e_rel_cause,$pdd,$asr,$call_duration,$npa,$i_tg_name,$e_tg_name,$st
+ate,$gc_id,$dn,$noacpn,$i_rel_cause) = @_;

        if ($gc_id) {
                push @fields, "GC_ID";
        }

        if ($pdd) {
                push @fields, "PDD";
        } 
        
         %where = (DAY => '2007-10-06');
        $table = "TABLE_NAME";

        my($stmt, @bind) = $sql->select($table, \@fields, \%where);

       my $sth = $dbh->prepare($stmt);

        unless ($sth) {
                $dbh->disconnect;
        }
        $sth->execute(@bind);

         while (my $row = $sth->fetchrow_hashref ){
                 >>>>>>WHAT DO I DO HERE FOR XML????>>>>
         }
[download]

Ideas on how to push the column names into the XML tag elements, and the values inside the tags? As for RoR, I am digging it. I don't know very advanced functions with it, but it was pretty easy to pick up, and I like incorporating it with AJAX, making this a pretty rich application. This is what I am doing with the XML::Generator on other canned/static reports, but the stuff it returns is hard coded, and not dynamic variables.

        $sth->execute();

        while (
                (
                        $gc_id, $cdr_day, $call_status, $direction, $p
+dd, $state, $noacpn, $i_cause
                )
                = $sth->fetchrow_array
          )
        {
                push @output,
                  $xml->cdr(
                        $xml->GC_ID($gc_id),
                        $xml->Date($cdr_day),
                        $xml->CallStatus($call_status),
                        $xml->Direction($direction),
                        $xml->State($state),
                        $xml->PDD($pdd),
                        $xml->NOACPN($noacpn),
                        $xml->I_REL_CAUSE($i_cause)
                  );
        }

        my $cgi = CGI->new;
        $hdr = "Custom Report";
        print $cgi->header('text/xml');
        print "<dataset name=\"$hdr\">\n";
        print @output;
        print "</dataset>";
[download]

Maybe do a...?

my $row = $sth->fetchrow_hasharray;
[download]

Just need help pushing the hash into the XML format properly. But since the amount of rows will always be different, I am not sure how to do it. Thanks!

... I know this is totally incorrect, because this way will always leave a trailing comma.

So just do chop $SQL; or $SQL =~ s/,$//; to get rid of the comma, before you add the next part of the statement ($SQL .= " FROM ...").

But I think the sample code you just gave is a bit off track. Let's go back to your CGI param string (from the OP):

../cgi-bin/script.pl?column3=1&column2=2&column1=3
[download]

If you get your params like this:

my $q = new CGI;
my $href = $q->Vars;
[download]

Then you can form the string of column names like this:

my $col_string = join( ',', sort {$$href{$a} <=> $$href{$b}} keys %$hr
+ef );
[download]

I don't think you'll ever need to worry about any extra commas with that approach, but if you want to be totally certain:

$col_string =~ s/^,+//;      # remove leading commas
$col_string =~ s/,+$//;      # remove trailing commas
$col_string =~ s/,{2,}/,/g;  # reduce adjacent commas to ","
[download]

Or, using something more like your approach, just append column names in order, and get rid of the last comma:

my $col_string = '';
for my $var ( sort {$$href{$a} <=> $$href{$b}} keys %$href ) {
    $col_string .= "$var,";
}
chop $col_string;  # remove final comma
[download]

Either way, now you just finish the SQL statement:

my $sql = "SELECT $col_string FROM my_table";
[download]

Also, in all cases, if/when you decide (or realize) that you do need to worry about security issues, make sure to test the "$href" keys (assumed table column names) against a known (untainted) list that is based directly on the how the table was defined. Delete any hash keys from the CGI parameter string that do not exactly match a known table column.

This sort of dynamic query construction is an area where things can go horribly wrong if you are not meticulously careful about checking your tainted input. If you don't have "-T" on the shebang line of your script (to turn on taint-checking), add it now.

This is what I am doing with the XML::Generator, but the stuff it returns is hard coded, and not dynamic variables...

That's why I say you should use XML::Simple (or something else besides XML::Generator) -- you need to write XML data based on a data structure, where the highest (outermost) level of the structure is a hash.

Hi, I think I have another method to do this. (It assumes that you pass only the selected field names as per your example URI ).

use CGI;
my $q = new CGI;
my %args = $q->Vars;

$fields = join(",", sort { $args{$a} <=> $args{$b} } keys %args ) # So
+rt with values of hash and Join. It won't put ',' at the end

$SQL = "SELECT $fields FROM YOUR_TABLE_NAME";
[download]

Cheers !

--VC