?
Solved

Retrieving values from SQL tables based on form input

Posted on 2003-03-12
13
Medium Priority
?
187 Views
Last Modified: 2013-12-25
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
Comment
Question by:klukac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 51

Expert Comment

by:ahoffmann
ID: 8119591
$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
 
LVL 2

Accepted Solution

by:
Itatsumaki earned 500 total points
ID: 8129931
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
 
LVL 51

Expert Comment

by:ahoffmann
ID: 8131066
> .. 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
Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8131243
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
 

Author Comment

by:klukac
ID: 8173920
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8177900
# 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
 

Author Comment

by:klukac
ID: 8180250
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8184068
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
 

Author Comment

by:klukac
ID: 8188129
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
 

Author Comment

by:klukac
ID: 8196131
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8196387
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
 

Author Comment

by:klukac
ID: 8231492
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8239460
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question