MySql in cgi

Chaotic Jarod has asked for the wisdom of the Perl Monks concerning the following question:

I have been working on setting up the environment for my term project for quite a few hours and now that I finally got it working, my cgi won't insert the data into the database. I can do all database functions within a terminal without any problems, and when I execute this script I get the "data submitted" screen.

#!/usr/bin/perl -w
use CGI qw(standard);
use DBI();
use DBD::mysql;
use warnings;
use strict;
use diagnostics;

my $q = CGI->new;

my $name = $q->param('name');
my $day = $q->param('day');
my $number = $q->param('number');

my $dbh = DBI->connect('dbi:mysql:oncall','webuser','password')
    or die "Connection Error: $DBI::errstr\n";
    $dbh->do('INSERT INTO users (name, day, phone) VALUES($name, $day,
+ $number)');

    #Disconnecting from database
    $dbh->disconnect();

  print $q->header,
  $q->h1('you submitted the new user!'),
  $q->end_html;
[download]

Comment on MySql in cgi Download Code

Replies are listed 'Best First'.
Re: MySql in cgi by davido (Cardinal) on Jul 17, 2013 at 21:10 UTC
This line... `$dbh->do('INSERT INTO users (name, day, phone) VALUES($name, $day, $nu +mber)');` [download] ...makes two mistakes; one related to syntax and semantics, the other related to best practices. First, it's using single quotes but expecting interpolation. You use double quotes when interpolation is intended. `my $bill = 'Text plus $ted'; # no interpolation. my $bill = "Text plus $ted"; # Interpolation occurs.` [download] Second, and really more importantly, it's not using placeholders / bind-values. Do it like this: `my $sth = $dbh->prepare('INSERT INTO users (name, day, phone) VALUES(? +,?,?)' ); $sth->execute( $name, $day, $number );` [download] Placeholders and bind-values are described in the documentation for DBI. Their most important function is to prevent SQL injection attacks. They also protect you from unintentional interpolation which may not be malicious, but may be problematic nevertheless. Dave	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: MySql in cgi
by davido (Cardinal) on Jul 17, 2013 at 21:10 UTC

This line...

$dbh->do('INSERT INTO users (name, day, phone) VALUES($name, $day, $nu
+mber)');
[download]

...makes two mistakes; one related to syntax and semantics, the other related to best practices. First, it's using single quotes but expecting interpolation. You use double quotes when interpolation is intended.

my $bill = 'Text plus $ted'; # no interpolation.  
my $bill = "Text plus $ted"; # Interpolation occurs.
[download]

Second, and really more importantly, it's not using placeholders / bind-values. Do it like this:

my $sth = $dbh->prepare('INSERT INTO users (name, day, phone) VALUES(?
+,?,?)' );
$sth->execute( $name, $day, $number );
[download]

Placeholders and bind-values are described in the documentation for DBI. Their most important function is to prevent SQL injection attacks. They also protect you from unintentional interpolation which may not be malicious, but may be problematic nevertheless.

Dave

[reply]
[d/l]
[select]