How to add datat to MySQL from CGI script with static html?

Question

I have a CGI script that reads an HTML file. The HTML file is located in directory called htdocs and CGI file is located in directory called cgi-bin. I can access the html form from the web and when I submit I see the result I expect, but when I check MySQL the table has a new blank line.

I usually put CGI and HTML in same file because I find it easy, but I was wondering how I can do same action with CGI and HTML in different files. Here is my CGI file code:

    #!/usr/bin/perl -w

use DBI;

$db="user1";
$user="user1";
$passwd="P@ssw0rd";
$host="db-mysql.system1";
$connectionInfo="dbi:mysql:$db;$host";

#print HTTP Header
print "Content-type:text/html\n\n";

if ($ENV{REQUEST_METHOD} eq "GET") {
    &parseform();
    &insertperson();
}
#else {
#   &parseform();
#   &insertperson();
#   exit;
#}

sub parseform {
    read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
    @pairs = split(/&/, $buffer);
    foreach $pair (@pairs) {
        ($name, $value) = split(/=/, $pair);
        $value =~ tr/+/ /;
        $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
        $FORM{$name} = $value;
}

sub insertperson {
    $insert=qq~insert person(name,email,age,color)
        values('name','email','age','color')~;
    $dbh=DBI->connect($connectionInfo,$user,$passwd);
    $sth=$dbh->prepare($insert);
    if ($sth->execute()) {
        &displaysuccess;
    }
    else {
        &displayfail;
    }
    $dbh->disconnect0;
}

sub displaysuccess {
    print qq~<html>\n
        <head>
        <title>Person</title>
        </head>
        <body>
        <h2>Record Added</h2>
        </body>
        </html>
    ~;
}   

sub displayfail {
    print qq~<html>\n
        <head>
        <title>Person</title>
        </head>
        <body>
        <h2>Record Not Added</h2>
        </body>
        </html>
    ~;
}

Replace insert person to insert into person. You current insert statement is not a valid sql syntax. — Dekel
– Dekel, Commented Nov 6, 2016 at 13:57
@Dekel — dev.mysql.com/doc/refman/5.7/en/insert.html — The into is just sugar. It's optional. — Quentin
– Quentin, Commented Nov 6, 2016 at 13:58

Dekel · Accepted Answer · 2016-11-06 14:25:48Z

3

Perl is case sensitive.

You used the variable $FORM to set values, but later on you use $form (in '$form{name}','$form{email}','$form{age}','$form{color}').

Change them to '$FORM{name}','$FORM{email}','$FORM{age}','$FORM{color}'

The $FORM variable is local to the parseform routine (therefor - it is not available inside the insertperson routine).

answered Nov 6, 2016 at 14:25

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Hassen Fatima Over a year ago

I have changed form to FORM, but still the issue is there. I have posted a screenshot of what I see in MySQL table in my question!

Dekel Over a year ago

1. what about the second part? 2. update the question with the updated code

Dekel Over a year ago

And you completely ignored the 2nd part in my answer

Hassen Fatima Over a year ago

I only changed form to FORM. I did not add or remove anything else!

Dekel Over a year ago

So your FORM is still local and available only inside the parseform routine.

|

Dave Cross · Accepted Answer · 2016-11-07 14:12:21Z

0

Here's the subroutine you use to insert the data into the database:

sub insertperson {
    $insert=qq~insert person(name,email,age,color)
        values('name','email','age','color')~;
    $dbh=DBI->connect($connectionInfo,$user,$passwd);
    $sth=$dbh->prepare($insert);
    if ($sth->execute()) {
        &displaysuccess;
    }
    else {
        &displayfail;
    }
    $dbh->disconnect;
}

The SQL you execute is:

insert person(name,email,age,color) values('name','email','age','color')

That's inserting hard-coded values into your database. You want to insert data from your %FORM hash.

%insert = "insert person(name,email,age,color)
           values('$FORM{name}','$FORM{email}',
                  '$FORM{age}','$FORM{color}')";

But this code is a terrible idea. You are taking data from an unknown user and inserting it into an SQL statement. That's an SQL injection attack waiting to happen.

A far better approach is to use bind variables and extra parameters to execute().

sub insertperson {
    $insert = "insert person(name,email,age,color)
               values(?, ?, ?, ?)";
    $dbh=DBI->connect($connectionInfo,$user,$passwd);
    $sth=$dbh->prepare($insert);
    if ($sth->execute($FORM{name}, $FORM{email},
                      $FORM{age},$FORM{color})) {
        &displaysuccess;
    }
    else {
        &displayfail;
    }
    $dbh->disconnect;
}

An even better idea is to look at DBIx::Class - but I'm guessing that's a step too far at this point.

Some other points on your code:

Please replace -w with use warnings and add use strict (you will then need to declare all of your variables).
Please use CGI.pm to parse your CGI parameters and print the CGI header.
Please stop using & to call subroutines. Something like displaysuccess() looks more recognisable as a subroutine call and doesn't have hidden side effects that will bite you one day.
Please use a templating engine (I recommend the Template Toolkit) to handle your HTML.
Your HTML should really have an h1 element before the h2 element. You should use CSS to fix the appearance, rather than messing with the semantics.

edited Nov 7, 2016 at 14:12

answered Nov 7, 2016 at 9:52

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

4 Comments

Dekel Over a year ago

actually the 'name', email','age' etc is new, his original code was '$FORM{name}', '$FORM{email}', '$FORM{age}'

Dave Cross Over a year ago

Yes, I know. I looked at the edit history. The original code used $form{name}, etc. which wouldn't have worked. When he switched to $FORM{name}, etc., if should have started working.

Dekel Over a year ago

Thats exactly what I told him, but he said it didn't work (you can check my answer).

Dave Cross Over a year ago

I have. But he's obviously very confused. Who knows what tests he actually ran :-/

Collectives™ on Stack Overflow

How to add datat to MySQL from CGI script with static html?

2 Answers 2

11 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related