Re: Fwd: Query results - Mailing list pgsql-novice

From Jason Earl
Subject Re: Fwd: Query results
Date
Msg-id 874rjcbiee.fsf@npa01zz001.simplot.com
Whole thread Raw
In response to Fwd: Query results  (Trevor Morrison <trevor@hailix.com>)
List pgsql-novice
Trevor Morrison <trevor@hailix.com> writes:

> Hi,
>
> I am trying to setup a simple databaes authoriztion of users using
> Postgresql 7.2 and PHP 4.1.  I have
> included the code below:
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN">
> <!--Call this file login2.php-->
> <!--This file begins like any other file-->
>
> <html>
>
> <head>
> <title>My Login Form</title>
> </head>
> <body>
> <?php
> switch($do) {
>
>     case "authenticate":
>
>     $Host = "localhost";
>     $User = "trevor";
>     $Password = "";
>     $DBName = "users";
>     $TableName="users";
>
>     $Link = pg_connect("host=$Host dbname=$DBName user=$User") or die ("Couldn't
> connect to the database");
>
>     $Query = "SELECT  id from $TableName where username='$username' and
> password='$password'";
>
>     $results = pg_exec($Link, $Query) or die ("Couldn't connect to the
> database");
>
>     $num = pg_numrows($results) or die ("Couldn't count rows");
>
>     if ($num == 1) {
>
>     echo "<P>You are a valid user!<BR>";
>     echo "Your user name is $username<BR>";
>     echo "Your user password is $password</P>";
>
>     }
>         else if ($num == 0){
>             unset ($do);
>             echo "<P>You are not authorized! Please try again.</p>";
>             include("login_form.inc");
>     }
>     break;
>
>     default:
>     include("login_form.inc");
> }
>
> ?>
> </body>
> </html>
>
>
> This script works great as long as the name is in the database, but if it is
> not then $num has no value and conseqently errors out.  Even if you use the
> correct firstname and and an incorrect password the pg_numrows errors out.
>
> Any help would be appreciated.
>
> Trevor

You probably don't want to die if you don't return any rows.  Let's
take a closer look at your logic.  First of all let's take a look at
your SQL query.

$Query = "SELECT id from $TableName where username='$username' and
        password='$password'";

This query will select all of the ids from the users table where
username is equal to $username and password is equal to $password.
This is probably not what you want.  If the user put in a correct
username, but an incorrect password then the query would return no
rows and it would die due to the following line:

$num = pg_numrows($results) or die ("Couldn't count rows");

The exact same thing would happen if they put in a username that
didn't exist.

What you probably want is to retrieve the password from the table so
that you can compare it with the password supplied in the form.  The
query for this would look like (NOTE, I haven't used PHP for years, so
I am probably doing it wrong):

$Query = "SELECT password from $TableName WHERE username=$username";

The logic should also be different.  It should look something like
this:

$Query = "SELECT password from $TableName WHERE username=$username";
$results = pg_exec($Link, $Query) or die ("Couldn't connect to the
        database");
# Make sure the user exists
$num = pg_numrows($results) or die ("User $user does not exist!");
# Make sure the user supplied the correct password
$arr = pg_fetch_array($result, 0, PGSQL_NUM);
if ($password == $arr[0]) {
        echo "valid user";
} else {
        echo "intruder alert!";
}

I hope this is helpful,

Jason

pgsql-novice by date:

Previous
From: "Henshall, Stuart - WCP"
Date:
Subject: Re: Triggers in postgres
Next
From: "Joel Burton"
Date:
Subject: Re: rename a table name