• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Retrieving values from SQL tables based on form input

I am trying to retrieve information from an SQL table row which contains value(s) that match the input value(s) on an html form.  I have 5 fields for each user in a table called users, and I want to retrieve field values from a row where the username (and other values, such as password or email) match the username etc that is typed into the html form (the form names are ok, no error there)
So far I am able to print out all the SQL table values (select *), and I can input fields into the SQL table from another form, but I can't select specific data from the table to print to the html page based on form input.

Here is my script - the print statements print the username from the form regardless, and no SQL table values will print.  

#!/usr/bin/perl -wT
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;

$cgi = CGI->new;
@params = $cgi->param();

print $cgi->header;
print $cgi->start_html("Log In");

#Connect to database with DBI
$dbh = DBI->connect("dbi:mysql:klukac", "klukac", "nejola6") or
&dienice("Can't connect to database: $DBI::errstr");

$username = $cgi->param('username');
$password = $cgi->param('password');
$email = $cgi->param('email');

print "Your username is $username - 1st print statement.\n\n";
print "<br>";

$sth = $dbh->prepare("select email from users where username = $username") or &dienice("Don't know who you are: ");
$rv = $sth->execute();

print "Your username is $username - 2d print statement.\n\n";
print "<br>";

@row_ary = $sth->fetchrow_array;

while (@row_ary = $sth->fetchrow_array)
{
print "$username has this address: $email \n\n";
}

print "Your username is $username - 3d print statement.";
print "<br>";

print <<EndHTML;
<p>The database value for $username is $email.</p>
</body>
</html>
EndHTML

$dbh->disconnect;

sub dienice {
my($msg) = @_;
 print $msg;
 exit;
 }











0
klukac
Asked:
klukac
  • 6
  • 5
  • 2
1 Solution
 
ahoffmannCommented:
$username=~s/'/''/g; # sanitize
$username=~s/%//g;   # sanitize
$sth = $dbh->prepare("select email from users where username='$username'")

I also highly recommend that to sanitize $username anywhere you print it to the HTML page, you need at least to replace < and > and " and &
0
 
ItatsumakiCommented:
Three things:

1. It appears that you have tainting on, so you'll have to touch all the cgi parameters with reg-ex's to get this to work.

2. You should be using the DBI feature called "place-holders" for efficiency in your queries.  Further, this saves you the string interpolation and the problem of worrying about proper quoting of your variables.  Your code as it is won't work on most databases because $username isn't properly quoted.  Further, $email is never defined and fetchrow_array() your data into @row_ary instead.

3. In all, you probably want your query code to look like this:

########
$username=~s/'/''/g;
$username=~s/%//g;

$sql = 'select email from users where username = ?';
$sth = $dbh->prepare($sql) or &dienice("Don't know who you are: ");

if ($sth->excecute($username)) {
    while (my $email = $sth->fetchrow_array()) {
         print "Email for $username is $email\n";
         }
    }
############
0
 
ahoffmannCommented:
> .. you'll have to touch all the cgi parameters ..
no, only variables which are used for "writing" to the system, somehow (see man perlsec)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ItatsumakiCommented:
Write, ahoffmann is write about the tainting -- you only have to touch (regex) variables that are used for anything other than print or symbolic references.  My bad!

Tats
0
 
klukacAuthor Commented:
Thank you Itatsumaki!  Sorry I was late responding to your reply, I have been on travel.  
I tried to fetchrow_array() my data into @row_ary instead
but got an error like the lvalue for the subroutine could not be modified, in any case
@row_ary = $sth->fetchrow_array still works.

I tried to add an else statement to follow your if statement, so that the user sees an error message if the username does not match the sql table, but it did not work (print statements or calling &dienice produced syntax errors).

I can post this as a separate question, whatever is most efficient.  
Also will review the perlsec man pages for touch(regex) variables

Thanks again,
Christine


0
 
ItatsumakiCommented:
# Hmm, let's try this.  Do you still get error messages?

#!/usr/bin/perl -wT
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;

$cgi = CGI->new;
@params = $cgi->param();

print $cgi->header;
print $cgi->start_html("Log In");

#Connect to database with DBI
$dbh = DBI->connect("dbi:mysql:klukac", "klukac", "nejola6") or
&dienice("Can't connect to database: $DBI::errstr");

$username = $cgi->param('username');
$password = $cgi->param('password');
$email = $cgi->param('email');

print "Your username is $username - 1st print statement.\n\n";
print "<br>";

my $sql = 'SELECT email FROM users WHERE username = ?';
my $sth = $dbh->prepare($sql);
$sth->execute($username);

my $ret_val = $sth->fetchrow_array();

if (!$ret_val) {
      &dienice("Don't know who you are!\n");
      }
else {
      print "Your username is $username - 2d print statement.\n\n<BR>";
      print "$username has this address: $email \n\n";
      }

print "Your username is $username - 3d print statement.";
print "<br>";

print <<EndHTML;
<p>The database value for $username is $email.</p>
</body>
</html>
EndHTML

$dbh->disconnect;

sub dienice {
my($msg) = @_;
print $msg;
exit;
}
0
 
klukacAuthor Commented:
Wow this is great, all I had to do was modify the select statement to get the authentication part to work.  It didn't print the $email no matter what was in the select statement, but in your previous suggestion I could, and for now I don't need to combine these two functions.  

My next step is to redirect the user to a web page once they have logged on.  However the redirect script I got to work inside the authentication script does the same thing I am already doing: printing to a web page and asking the user to link to where they need to go.  I'd like to skip that intermediate step.  Here's what I have so far:

if (!$ret_val) {
     &dienice("Your username and password are not in our database. Please use our
<a href=\"register.htm\">registration form</a> or
<a href=\"mailto:kluka@skynet.be\">contact us</a>.\n");
     }

else {
print redirect('http://www.cgi101.com/~klukac/registered.htm'),
   start_html("Authenticated"),
   h1("You are authenticated"),
   "You may continue ",
a({href=>'http://www.cgi101.com/~klukac/registered.htm'},
   "here"),
   '';
   }
0
 
ItatsumakiCommented:
Hmm, that is how I do my redirects, usually.  What about using this time of code for the redirect:

print start_html(-head=>[
  meta({-http_equiv => 'Content-Type',
        -content    => 'text/html'}),
  meta({-http_equiv => 'refresh',
        -content=>
'5;URL=http://mydomain.whatever.net/login.html'})
     ]),

p('Redirecting to login page'),

end_html;
0
 
klukacAuthor Commented:
The redirect you suggested worked the first time, I love when that happens.  There's a moment when you wait for the page to load, but the redirecting to login page appears and the pause is only a few seconds which is fine.

Unfortunately, when I went back to your first suggestion and tried to print more than one paramater (email) from a lookup, the best I could get was to output the criteria typed into the form (lastname) and email - it wouldn't print the first name or any other parameter from the fetchrow array.  I tried the same script with a username and firstname lookup, and the results were worse than that.
I tried the same queries in mysql, it outputs all parameters.
 
Here's a recap of the cgi script:
$sql = 'select * from users where lastname = ?';
$sth = $dbh->prepare($sql) or &dienice("No user with that name.");
$sth->execute($lastname);

@row_ary=$sth->fetchrow_array;

if($sth->execute($lastname)){
while (my $username, $firstname, $lastname, $email = $sth->fetchrow_array()){
print " User information: $username, $firstname, $lastname, $email \n";
               }
}

The parameters are listed in the order in which they appear in the mysql table.  Let me know, thanks!
0
 
klukacAuthor Commented:
I finally subscribed to Premium Search on this site and found an answer that works for me.  The code below gives me the parameters I need:

if($sth->execute($lastname)){
while ( @row_ary = $sth->fetchrow_array() ) {
( $lastname, $firstname, $email ) = @row_ary;
   print "User Information:  ";
   print "$lastname, $firstname, $email \n";
     }
   }

So you can go ahead and disregard the last question/comment.
0
 
ItatsumakiCommented:
Looks good -- glad you got it to work!  One suggestion for your code: don't bother defining new variables if you don't have to.  Try this instead:

if ($sth->execute($lastname)) {
     while (my @row = $sth->fetchrow_array()) {
          print 'User information: ',
                join(',', @row),
                "\n";
          }
      }

That will be a fair chunk faster than the way you've got it currently (fewer variables, only a single print statement).  Plus, it's safer to redefine the @row with every iteration of the while loop.

Best of luck!
Tats
0
 
klukacAuthor Commented:
Tats,

Thanks for that, I used your suggestion in another script to make it work.  Unfortunately, I couldn't format the returns into a table, so I'll have to poke around a bit to figure out how join works.

Best,
Christine
0
 
ItatsumakiCommented:
Do you mean, format the results of the DBI query into an HTML table?  That's not too bad, actually:

use CGI;
my $cgi = new CGI;
print $cgi->header();
print $cgi->start_html();

print table(
     {  -border      => '1',
        -cellpadding => '5',
        -cellspacing =>'5'
        },
     Tr(
        { -align  => 'left',
          -valign => 'top'
          },
        [
          td(['R1,C1','R1,C2','R1,C3']),
          td(['R2,C1','R2,C2','R2,C3'])
          ]
        )
      );

Incidentally, I don't go on EE all that much anymore, so I'll usually reply faster if you just e-mail me direct: itatsumaki@hotmail.com
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now