Process a form in perl and write results to a database

Question

This is my first time programming with both perl and databases, I'm having some issue determining how exactly to write a perl script that will input the results from an html form into a mysql database.

This particular snippet is what is causing me issue:

    # CONFIG VARIABLES
    $platform = "mysql";
    $database = "*****";
    $host = "localhost";
    $port = "3306";
    $user = "*****";
    $pw = "*****";

    # DATA SOURCE NAME
    $dsn = "dbi:$platform:$database:$host:$port";

    # PERL DBI CONNECT
    $connect = DBI->connect($dsn, $user, $pw);

    # PREPARE THE QUERY
    $query = "INSERT INTO result (name, console, character, series, bday, steam) VALUES (fname, favConsole, fCharacter, favSeries, birthday, steamAcc)";
    $query_handle = $connect->prepare($query);

    # EXECUTE THE QUERY
    #$query_handle->execute();

I replaced the database name, username and password with asterisks but the real code has the true values.

I'm not certain why the connect and execute lines are giving me issue.

Also where exactly would I put the prompt so the information is written once the user hits the submit button? I tried adding execute as the form action but that didn't work.

This is my work so far:

    #!/usr/bin/perl

    use CGI qw( :standard );
    use DBI;
    use DBD::mysql;

    # CONFIG VARIABLES
    $platform = "mysql";
    $database = "*****";
    $host = "localhost";
    $port = "3306";
    $user = "*****";
    $pw = "*****";

    # DATA SOURCE NAME
    $dsn = "dbi:$platform:$database:$host:$port";

    # PERL DBI CONNECT
    $connect = DBI->connect($dsn, $user, $pw);

    # PREPARE THE QUERY
    $query = "INSERT INTO result (name, console, character, series, bday, steam) VALUES (fname, favConsole, fCharacter, favSeries, birthday, steamAcc)";
    $query_handle = $connect->prepare($query);

    # EXECUTE THE QUERY
    #$query_handle->execute();

    $dtd =
    "-//W3C//DTD XHTML 1.0 Transitional//EN"
       "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";

    print( header() );

    print( start_html( { dtd => $dtd,
      title => "~Video Game Survey~" } 
      #style=>{"background-image:url(/images/blue_line_background.jpg) ";
      #}
      ) );
    #print( " 
 " );


    print( "
" );
    print(     "
" );
    print(      "Please let us know a bit more about yourself.
" );
    print(     "
" );
    print( "
" );

    print( "

 " );

    #Start of the form.
    print( " 
" );

    #First question, asking the visitor's name.  Name will be stored in the database         but not displayed on the
    #survey results.
    print( "
" );
    print(      "First of all, what is your name?
" );
    print(      "
" );
    print( "
" );

    print( "


" );

    #Second question, type dropdown list.
    print( "
");
    print( "
" );
    print(      "Gaming comes in all sorts of shapes and sizes.  What is your favorite console?
" );
    print( "
" );
    print( "
" );
    print(      "
" );
    print(              "
" );
    print(                  "
" );
    print( "
");

    print( "


" );

    #Third question, type radio.  Default value: Mario
    print( "
" );
    print( "
" );
    print(      "Out of the following list, who is your favorite video game character?
" );
    print( "
" );
    print(      "Mario
" ); 
    print(      "Sephiroth
" );
    print(      "Sonic
" );
    print(      "Thrall
" );
    print(      "Pacman
" );
    print(      "Cloud
" );
    print(      "Sylvanas
 ");
    print(      "Kerrigan
 ");
    print( "
" );

    print( "


" );

    #Fourth question, type dropdown.  Required.
    #Dropdown options will not appear unless the window is maximized in terms of height.
    print( "
" );
    print( "
" );
    print(      "Which of the following series is your favorite?
" );
    print( "
" );
    print(      "
" );
    print(              "
" );
    print(                  "
" );

    print( "


" );

    #Fifth question, type date.  No default value, but still required.
    print( "
" );
    print( "
" );
    print(      "What is your cake day?
" );
    print( "
" );
    print(      "(yyyy-mm-dd)
" );

    print( "


" );

    #Sixth question, type radio.  Default value: no.
    print( "
" );
    print(      "Last but not least, do you have a Steam account?
" );
    print(      "        Yes
" );
    print(      "        No
" );
    print( "
" );

    print( "
" );
    print(      "
" );
    print(      "
" );
    print( "
" );

    #End of the form
    print( "
" );

    print( end_html() );

stevenl · Accepted Answer

A few things to start off with:

Add these lines to the top of your script for safety in your code.
```
use strict;
use warnings;
```
The above step means you will need to declare ALL of your variables with my, e.g.
```
my $dsn = ...
```
Tell DBI to throw an error if anything is wrong with the connection or execution (it doesn't do this by default). From the error messages you can get some more clues to figure out what is happening.
```
my $dbh = DBI->connect($dsn, $user, $pw, {RaiseError => 1});
```

Use placeholders for your SQL (which prevents SQL injection, and also adds quotes your insert values in the SQL automatically)

my $query = 'insert into (name, console) values (?, ?)';
my $sth = $connect->prepare($query);
$sth->execute('fname', 'favConsole');
# this is equivalent to:
# "insert into (name, console) values ('fname', 'favConsole')"

Your values need to be pulled from the CGI params (which I'm guessing you're not up to that stage just yet).

Process a form in perl and write results to a database

Answers (2)

Related Questions