cgi.pm; DBI, filtering input?

Discussion in 'Perl Misc' started by robert.waters, Dec 9, 2005.

  1. Hi,

    I need to be able to input text into an html form that is submitted to
    my perl cgi script (using CGI.pm), and subsequently written to a
    database (using DBI, mysql). The script will be secured using access
    permissions, but 'just in case', I would like to know what security
    issues I need to be alert for as far as cgi input sanitization is
    concerned.
    My problem is, I need to be able to input *any* text to the cgi script
    (for all intents and purposes, I'll call the page a 'blog', and the
    form+cgi+db serves to allow me to post new content from the web), so
    filtering seems moot.
    Should I have urlencoded data entered into the database (and urldecoded
    before it is written out to the result page)? Do I even need to worry?
    I've done as much research as I can (google, perldocs, cgi faqs) but I
    am at a stand-still.
    example query:
    "INSERT INTO (field) VALUES ($cgiobj->param('cgi-parameter'))"; where
    'field' is varchar and parameter should be able to include any text.

    This issue is absolutely stopping the development of my website right
    now.

    Thank you in advance!
    -Robert
     
    robert.waters, Dec 9, 2005
    #1
    1. Advertising

  2. robert.waters wrote:
    > I need to be able to input text into an html form that is submitted to
    > my perl cgi script (using CGI.pm), and subsequently written to a
    > database (using DBI, mysql). The script will be secured using access
    > permissions, but 'just in case', I would like to know what security
    > issues I need to be alert for as far as cgi input sanitization is
    > concerned.
    > My problem is, I need to be able to input *any* text to the cgi script
    > (for all intents and purposes, I'll call the page a 'blog', and the
    > form+cgi+db serves to allow me to post new content from the web), so
    > filtering seems moot.
    > Should I have urlencoded data entered into the database (and urldecoded
    > before it is written out to the result page)?


    No. Uriencoding is for transferring data in e.g. HTTP requests.

    > Do I even need to worry?


    Yes, there is always a need to worry when dealing with CGI. :)

    If you intend to display the data as part of HTML pages, you should
    convert certain characters to HTML entities. Since you are using CGI.pm,
    check out CGI::escapeHTML(). Personally I usually do that immediately
    before printing to STDOUT, i.e. I don't do it before storing data in a
    database.

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
     
    Gunnar Hjalmarsson, Dec 9, 2005
    #2
    1. Advertising

  3. "robert.waters" <> writes:

    > I've done as much research as I can (google, perldocs, cgi faqs) but I
    > am at a stand-still.


    You might want to google for "SQL Injection". Directly interpolating vars
    into SQL strings can be problematic - you should use placeholders to help
    avoid that. They also give a nice performance boost if you're repeating the
    same query with different data.

    > example query:
    > "INSERT INTO (field) VALUES ($cgiobj->param('cgi-parameter'))"; where
    > 'field' is varchar and parameter should be able to include any text.


    Using placeholders:

    # Assuming the database handle $dbh has already been connected
    my $sth = $dbh->prepare('INSERT INTO(field) VALUES (?)');

    # I'm skipping over error-checking here for brevity - don't skip
    # it in production code!
    $sth->execute($cgiobj->param('cgi-parameter'));

    This allows no chance for the contents of 'cgi-parameter' to be interpreted
    as part of a SQL command.

    sherm--

    --
    Cocoa programming in Perl: http://camelbones.sourceforge.net
    Hire me! My resume: http://www.dot-app.org
     
    Sherm Pendley, Dec 9, 2005
    #3
  4. Thank you kindly. I believe that these two replies allay both my fears
    - HTML being parsed in the result page, and SQL being injected
    initially.
    I appreciate the help.
     
    robert.waters, Dec 9, 2005
    #4
  5. "robert.waters" skrev i meddelandet
    news:...
    > Thank you kindly. I believe that these two replies allay both my fears
    > - HTML being parsed in the result page, and SQL being injected
    > initially.
    > I appreciate the help.
    >

    Think hard do you really want them to be able to inject html tags
    i.e. an unclosed H1 tag would change everything below it....
     
    Bengt-Arne Fjellner, Dec 9, 2005
    #5
  6. Bengt-Arne Fjellner wrote:
    > "robert.waters" skrev i meddelandet
    > news:...
    >>
    >>Thank you kindly. I believe that these two replies allay both my fears
    >>- HTML being parsed in the result page, and SQL being injected
    >>initially.
    >>I appreciate the help.

    >
    > Think hard do you really want them to be able to inject html tags
    > i.e. an unclosed H1 tag would change everything below it....


    Not if you convert it to &lt;H1&gt;

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
     
    Gunnar Hjalmarsson, Dec 9, 2005
    #6
  7. "Bengt-Arne Fjellner" <Bengt-Arne(dot)Fjellner(at)ltu(dot)se> writes:

    > Think hard do you really want them to be able to inject html tags
    > i.e. an unclosed H1 tag would change everything below it....


    Gunnar's suggestion of using CGI::escapeHTML() addresses that - it will
    replace all the '<'s in the output with &lt;, and so on.

    If you want to allow a limited set of markup beyond that, you could use
    "[[...]]" style "tags" similar to those used by many message boards, and
    then transform only the specific set of tags you've defined into valid
    HTML markup.

    sherm--

    --
    Cocoa programming in Perl: http://camelbones.sourceforge.net
    Hire me! My resume: http://www.dot-app.org
     
    Sherm Pendley, Dec 9, 2005
    #7
  8. Hi,
    I've decided to use HTML::Entities to do the tag encoding.
    I have another question though:
    How would I use DBI placeholders in the case of a multiple insert?
    I've got a field that takes a comma-delimited list with no limit on
    quantity.
    For instance, how would I convert this example to use placeholders?:
    $contentid=0;
    $sql = "INSERT INTO keyword (name, contentid) VALUES (";
    for (my $i=0; $i<@keywords; $i++) {

    $keywords[$i] = "(qw[$keywords[$i]],$contentid)";

    }
    $sql .= join(',', @keywords).")"; # results in '...VALUES
    (("a",0),("b",0))' etc..
    $q=$dbh->prepare($sql);
    $q->execute;
     
    robert.waters, Dec 9, 2005
    #8
  9. robert.waters

    Paul Lalli Guest

    robert.waters wrote:

    > I've decided to use HTML::Entities to do the tag encoding.
    > I have another question though:


    Another? You had a previous? Please quote context when posting a
    reply. For more information, please see the Posting Guidelines that
    are posted here twice a week.

    > How would I use DBI placeholders in the case of a multiple insert?
    > I've got a field that takes a comma-delimited list with no limit on
    > quantity.
    > For instance, how would I convert this example to use placeholders?:
    > $contentid=0;
    > $sql = "INSERT INTO keyword (name, contentid) VALUES (";
    > for (my $i=0; $i<@keywords; $i++) {
    >
    > $keywords[$i] = "(qw[$keywords[$i]],$contentid)";


    I don't believe for a second that that does what you think it does.
    Don't re-type code. Copy and paste.

    >
    > }
    > $sql .= join(',', @keywords).")"; # results in '...VALUES
    > (("a",0),("b",0))' etc..
    > $q=$dbh->prepare($sql);
    > $q->execute;


    I see two solutions. One messier but (possibly?) more efficient than
    the other.
    The cleaner one is to convert into multiple INSERT executions:
    my $sql = "INSERT INTO keyword (name, contentid) VALUES (?, ?)";
    my $sth = $dbh->prepare($sql);
    $sth->execute($_, $contentid) for @keywords;

    The messier one is to build a multiple-INSERT statement based on the
    number of keywords you're going to have:
    my $sql = "INSERT INTO keyword(name, contentid) VALUES (";
    $sql .= join (', ', ("(?, ?)") x @keywords);
    $sql .= ")";
    my $sth = $dbh->prepare($sql);
    $sth->execute(map { $_, $contentid } @keywords);

    Like I said, this second one is definately messier. Unless there's a
    signficant performance reason to use a single statement that does all
    the insertions (and not being overly DBI-proficient, I have no idea if
    there is), I'd recommend the first choice above.

    Paul Lalli
     
    Paul Lalli, Dec 9, 2005
    #9
  10. robert.waters wrote:
    > I have another question though:


    Then you should have started a new thread with an appropriate subject line.

    > How would I use DBI placeholders in the case of a multiple insert?
    > I've got a field that takes a comma-delimited list with no limit on
    > quantity.
    > For instance, how would I convert this example to use placeholders?:
    > $contentid=0;
    > $sql = "INSERT INTO keyword (name, contentid) VALUES (";
    > for (my $i=0; $i<@keywords; $i++) {
    >
    > $keywords[$i] = "(qw[$keywords[$i]],$contentid)";

    ------------------------^^
    ??

    > }
    > $sql .= join(',', @keywords).")"; # results in '...VALUES
    > (("a",0),("b",0))' etc..
    > $q=$dbh->prepare($sql);
    > $q->execute;


    Assuming you don't want the qw// operator there, I suppose you may want
    to do something like:

    my $name = join( ',', map "($_,$contentid)", @keywords );
    $q = $dbh->prepare('INSERT INTO keyword (name,contentid) VALUES(?,?)';
    $q->execute($name, $contentid);

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
     
    Gunnar Hjalmarsson, Dec 9, 2005
    #10
  11. Gunnar Hjalmarsson <> writes:

    > robert.waters wrote:
    >
    >> How would I use DBI placeholders in the case of a multiple insert?
    >> I've got a field that takes a comma-delimited list with no limit on
    >> quantity.
    >> For instance, how would I convert this example to use placeholders?:
    >> $contentid=0;
    >> $sql = "INSERT INTO keyword (name, contentid) VALUES (";
    >> for (my $i=0; $i<@keywords; $i++) {
    >> $keywords[$i] = "(qw[$keywords[$i]],$contentid)";

    > ------------------------^^
    > ??
    >
    >> }
    >> $sql .= join(',', @keywords).")"; # results in '...VALUES
    >> (("a",0),("b",0))' etc..
    >> $q=$dbh->prepare($sql);
    >> $q->execute;

    >
    > Assuming you don't want the qw// operator there, I suppose you may
    > want to do something like:
    >
    > my $name = join( ',', map "($_,$contentid)", @keywords );
    > $q = $dbh->prepare('INSERT INTO keyword (name,contentid) VALUES(?,?)';
    > $q->execute($name, $contentid);


    Won't that give a different result than what Robert's asking for?

    With placeholders, the (s should be inserted as text into the name field,
    not interpreted as SQL. So the result will be a single record whose name
    field looks like "(a,0),(b,0),(c,0)", and contentid field is 0.

    I think Robert wanted multiple records to be inserted. For that, you'd want
    one prepare() followed by a series of execute()s, like this:

    my $q = $dbh->prepare('INSERT INTO keyword (name,contentid) VALUES(?,?)');
    foreach my $name (@keywords) {
    $q->execute($name, $contentid);
    }

    sherm--

    --
    Cocoa programming in Perl: http://camelbones.sourceforge.net
    Hire me! My resume: http://www.dot-app.org
     
    Sherm Pendley, Dec 9, 2005
    #11
  12. My apologies, you are absolutely right; I should have started another
    thread.
    Thank you for your responses.
    sherm - that is what I will do; I had my mind set on one INSERT, but I
    realize now that's not the best way.
     
    robert.waters, Dec 9, 2005
    #12
  13. robert.waters

    Guest

    "robert.waters" <> wrote:
    > Hi,
    > I've decided to use HTML::Entities to do the tag encoding.
    > I have another question though:
    > How would I use DBI placeholders in the case of a multiple insert?
    > I've got a field that takes a comma-delimited list with no limit on
    > quantity.
    > For instance, how would I convert this example to use placeholders?:
    > $contentid=0;
    > $sql = "INSERT INTO keyword (name, contentid) VALUES (";
    > for (my $i=0; $i<@keywords; $i++) {
    >
    > $keywords[$i] = "(qw[$keywords[$i]],$contentid)";


    I don't think that that is doing what you think it is doing. The qw
    is not an operator, just the letters q and w.

    >
    > }
    > $sql .= join(',', @keywords).")";
    > # results in '...VALUES (("a",0),("b",0))' etc..


    Even if that is what the results are, I don't think that that is correct
    syntax for mysql. You shouldn't have the outer parenthesis.

    Anyway, SQL text strings can be created on the fly with placeholders, just
    like they can be created on the fly with data (only easier).

    join ",", map "(?,?)", @keywords;

    But I wouldn't bother. Just roll a loop with an execute inside it. Only
    if/when that proves to be too slow would I consider trying to cram it all
    into one SQL statement.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Dec 9, 2005
    #13
    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. ulloa
    Replies:
    1
    Views:
    527
    Juha Laiho
    Jul 22, 2004
  2. LarsenMTL
    Replies:
    4
    Views:
    742
    Eric Walstad
    Nov 4, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    173
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    177
  5. Tim Haynes
    Replies:
    3
    Views:
    143
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page