query help!

[olobraecky]

Hi all,

I am new to perl - just started to learn it a few weeks ago. Basically, what I am trying to do is a "register user" script using a mySQL database.

I have managed to do the insert query (see below). But my problem is the following:

user table contains in the right order:
- email (primary key)
- password
- first name
- last name
- scientist
- observer

so when the script is run, it first needs to check that no user already registered is using that email address (i know the query will not be done if it exists but i want the page to be redirected to "login.html" with a GET parameter so that I can let the user know why it did not work).

I am having problems getting the select query working...

Could anybody show me a way to do it using the fetch method in perl?

Also, i would appreciate if you could explain so i can understand what I was doing wrong. Here is the original code I have working for the registering process.

Thanks,

olo

#!/usr/bin/perl
use CGI;
use DBI;
 
$cgiquery = CGI::new();
$|=1;
 
$fname = $cgiquery->param("fname");
$lname = $cgiquery->param("lname");
$email = $cgiquery->param("email");
$password = $cgiquery->param("password");
$observer = $cgiquery->param("observer");
$scientist = $cgiquery->param("scientist");
 
my $dbh;
 
$dbh = DBI->connect("DBI:mysql:database=ornisoft;host=localhost",
            "root","student",{'RaiseError' => 1} );
 
$query = 'INSERT INTO users 
            VALUES("'.$email.'", "'.$password.'", "'.$fname.'", "'.$lname.'", "'.$scientist.'", "'.$observer. '");';
 
 
 
$sth = $dbh->prepare($query);
$sth->execute();
 
$sth->finish();
 
print $cgiquery->redirect('http://localhost/~student/login.html');

[eWish]

Welcome to TSDN olobraecky!!!

Here is some commented code that should help you in the right direction. The '?' in the statements are called placeholders. The DBI will escape the data when placeholders are used.

Untested Code

#This query is used to check to see if the user exists.
my $check_user = $dbh->prepare(qq{SELECT email FROM table_name WHERE email = ?});
 
#This query will insert the user data into the database.
my $insert_user = $dbh-prepare(qq{INSERT INTO table_name(email, password, first_name, last_name, scientist, observer) VALUES(?,?,?,?,?,?)});
 
#This is the redirect url.
my $redirect_url = 'http://somewhere.com/redirect_page.html';
 
 
#Check to see if the user is already in the db.
if ($email) {
 
 #If this returns true then the user is already in the db. 
 if( my ($existing_user) = $dbh->selectrow_array($ckeck_user, undef, ( $email ))){
 
  #If the user exists then redirect.
   if ($existing_user) {
    $cgiquery->redirect($redirect_url);
 
  #Since the user did not exist insert into db 
   } else {
    $insert_user->execute($email,$password, $fname, $lname, $scientist, $observer );
  }
 
 
   $check_user->finish();
   $insert_user->finish();

Also, it appears that you are not validating the user data. That can be unsafe. I strongly recommend that you check the user data before using it. There is a module that you can use to check and validate the email address called Email::Valid

Checking other data can be done with a regex

if ($cgiquery->param('email') =~ /^(?:([\w\s+\d\']+))$/) {
     $email = $1;
}

[olobraecky]

hi eWish,

I've just tried your implementation. But i get the same problem "500 Internal Server Error" which has been driving me mad all day.

I knew that I would have to validate data but i wanted to get the checking users feature to work first.

here is the exact code of my file:

#!/usr/bin/perl
use CGI;
use DBI;
 
$cgiquery = CGI::new();
$|=1;
 
$fname = $cgiquery->param("fname");
$lname = $cgiquery->param("lname");
$email = $cgiquery->param("email");
$password = $cgiquery->param("password");
$observer = $cgiquery->param("observer");
$scientist = $cgiquery->param("scientist");
 
my $dbh;
 
$dbh = DBI->connect("DBI:mysql:database=ornisoft;host=localhost", "root","student",{'RaiseError' => 1} );
 
my $check_user = $dbh->prepare(qq{SELECT email FROM table_name WHERE email = ?});
 
my $insert_user = $dbh-prepare(qq{INSERT INTO table_name(email, password, first_name, last_name, scientist, observer) VALUES(?,?,?,?,?,?)});
 
my $redirect_url = 'http://localhost/~student/login.html/';
 
if ($email) {
 
   if( my ($existing_user) = $dbh->selectrow_array($ckeck_user, undef, ( $email ))){
    if ($existing_user) {
        $cgiquery->redirect($redirect_url);
    } else {
        $insert_user->execute($email,$password, $fname, $lname, $scientist, $observer );
    }
   }
}
$check_user->finish();
$insert_user->finish();

did i make a mistake? or did i misunderstand your post?

sorry if i'm being silly, but perl is really new to me... it's for a uni coursework... php is so much more my thing... lol

thanks for ur help!

olo

[eWish]

When posting code please use the [code][/code] tags. I have added the code tags in your posts. Please use this them with all future posts. Thank You.

Replace the first part of your code with the following.

#! /usr/bin/perl 
 
use strict;
use warnings;
 
use CGI;
use CGI::Carp qw/fatalsToBrowser/;  #Used to help debug
 
my $cgiquery = CGI->new;
 
print $cgiquery->header; 

One of the reasons you are getting the error is because you are not printing the proper headers. Using the CGI::Carp module will print errors to the screen. This is a big help when debugging.

[eWish]

I did not realize that this was course work until after I posted my reply. Please read the Posting Guidlines.

Regards,
Kevin

[olobraecky]

Indeed, that debugging feature is really useful.

I'm still having difficulties to get it working though:

Global symbol "$fname" requires explicit package name at register.cgi line 10.
Global symbol "$lname" requires explicit package name at register.cgi line 11.
Global symbol "$email" requires explicit package name at register.cgi line 12.
Global symbol "$password" requires explicit package name at register.cgi line 13.
Global symbol "$observer" requires explicit package name at register.cgi line 14.
Global symbol "$scientist" requires explicit package name at register.cgi line 15.
Global symbol "$email" requires explicit package name at register.cgi line 27.
Global symbol "$ckeck_user" requires explicit package name at register.cgi line 29.
Global symbol "$email" requires explicit package name at register.cgi line 29.
Global symbol "$email" requires explicit package name at register.cgi line 33.
Global symbol "$password" requires explicit package name at register.cgi line 33.
Global symbol "$fname" requires explicit package name at register.cgi line 33.
Global symbol "$lname" requires explicit package name at register.cgi line 33.
Global symbol "$scientist" requires explicit package name at register.cgi line 33.
Global symbol "$observer" requires explicit package name at register.cgi line 33.
Execution of register.cgi aborted due to compilation errors.

man, perl is soooooo hard... i've never been struggling so much with a server-side language...

[olobraecky]

i see... so i'm not allowed to ask for help?

as i mentioned, i'm trying to understand (which is why i asked if you could explain)

if not, i understand and i'll try to do it by myself.

let me know,

olo

[eWish]

You are getting those message's because you are not delaring your $variables first. I would bookmark perldoc.perl.org this is a great resource.

[eWish]

i see... so i'm not allowed to ask for help?

as i mentioned, i'm trying to understand (which is why i asked if you could explain)

if not, i understand and i'll try to do it by myself.

let me know,

olo

We are here to help everyone. When it comes to course work we will try to guide you in the proper direction and help you understand why your code is not working rather than just offering up solutions.

Regards,

Kevin

[olobraecky]

Kevin,

Glad we got that sorted because I was expecting nothing more from this forum.

I'm now trying some more code using the notes I had taken last week. But that is not even working :s

it's a simple select query that is supposed to output email adresses (from my db) into html table.

#! /usr/bin/perl
use strict;
use warnings;
use CGI;
use CGI::Carp qw/fatalsToBrowser/;  #Used to help debug
use DBI;
my $cgiquery = CGI->new;
 
print $cgiquery->header;
 
my $dbh;
my $sth;
my $resptr;
my $email;
 
$dbh = DBI->connect("DBI:mysql:database=XXX;host=localhost", "XXX","XXX",{'RaiseError' => 1} );
 
$sth = $dbh->prepare("SELECT * FROM users");
$sth->execute();
 
print "<table border=2>\n";
while ( $resptr = $sth->fetchrow_hashref() )
{
   print "<tr>";
   print "<td>" . $resptr->{"$email"}."</td>";
   print "</tr>\n";
}
 
print "</table>";
 

strange thing is that there are no errors. table is shown but no data inside it.

there must be something wrong with line 25...

[eWish]

You are correct, your error is in line 25. You don't have the hashref correct.

print "<td>" . $resptr->{email}."</td>";

[olobraecky]

hey Kevin,

finally got it working! i was actually really close in the beginning.

thanks for your help! i'm now gonna work on fields validation!

olo