Web Reporting Enhancement

Discussion in 'Perl Misc' started by pmcgover@gmail.com, Sep 23, 2006.

  1. Guest

    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
    , Sep 23, 2006
    #1
    1. Advertising

  2. >>>>> "pmcgover@gmail" == pmcgover@gmail com <> writes:

    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 </etc/passwd";

    Idiot. People without clues should NOT be writing articles.

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

    --
    Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
    <> <URL:http://www.stonehenge.com/merlyn/>
    Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
    See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

    --
    Posted via a free Usenet account from http://www.teranews.com
    Randal L. Schwartz, Sep 23, 2006
    #2
    1. Advertising

  3. Guest

    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


    Randal L. Schwartz wrote:
    > >>>>> "pmcgover@gmail" == pmcgover@gmail com <> writes:

    >
    > 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 </etc/passwd";
    >
    > Idiot. People without clues should NOT be writing articles.
    >
    > print "Just another Perl hacker,"; # the original
    >
    > --
    > Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
    > <> <URL:http://www.stonehenge.com/merlyn/>
    > Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
    > See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
    >
    > --
    > Posted via a free Usenet account from http://www.teranews.com
    , Sep 23, 2006
    #3
  4. wrote:
    > 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, Sep 23, 2006
    #4
  5. Randal L. Schwartz wrote:

    > my $cmdline = MYSQL .
    > ' -H -u ' .
    > USERID .
    > ' -p' .
    > PASSWD .
    > ' ' .
    > DBNAME .
    > "< $query ";
    >
    > What if param('query') contains:
    >
    > "whocares; mail </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.
    Brian McCauley, Sep 23, 2006
    #5
  6. Ted Zlatanov Guest

    On 23 Sep 2006, wrote:

    > 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, Sep 25, 2006
    #6
  7. Ted Zlatanov Guest

    On 23 Sep 2006, wrote:

    > 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
    Ted Zlatanov, Sep 25, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marlene

    Re: asp.net calendar enhancement

    Marlene, Jun 25, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    951
    Marlene
    Jun 25, 2003
  2. Fred.

    Re: asp.net calendar enhancement

    Fred., Jun 25, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    3,767
  3. randau
    Replies:
    30
    Views:
    1,374
    Travis Newbury
    May 20, 2005
  4. Stephen

    nested if enhancement?

    Stephen, Mar 1, 2004, in forum: XML
    Replies:
    1
    Views:
    403
    Dimitre Novatchev [MVP XML]
    Mar 1, 2004
  5. redguardtoo
    Replies:
    0
    Views:
    301
    redguardtoo
    Jul 26, 2004
Loading...

Share This Page