Php ms Access Login

Logging into ms access via php

[meclive]

I am doing a website where i need the admin to login. I have produced a username/password form which gets sent to my login.php page, the code of which i will show below. Unfortunatly it isnt working and it just keeps saying "login error as (username)".

Login.php code
<?php
// Main ----------
session_start();

// Get the data collected from the user
$Username =$_POST["username"];
$Password =$_POST["password"];

if (!$conn = new COM("ADODB.Connection"))
exit("Unable to create an ADODB connection<br>");
$strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("DATABASEFILE");
$conn->open($strConn);

$strSQL = "SELECT * FROM accounts WHERE username = '$Username'
AND password = '$Password' ";
$rs = $conn->execute($strSQL);

if ($rs->recordCount() < 1)
{
$_SESSION["message"] = "Login Error as $Username. " ;
header("Location: admin.php");
}
else
{
$_SESSION["authenticatedUser"] = $Username;
// Relocate to the logged-in page
header("Location: loggedon.php");
}
?>

I have a feeling this is because of the line
"if ($rs->recordCount() < 1)"
but how do i do login without using this? any help would be appreciated.

regards Meclive

[ergophobe]

For what little I know about COM and ADO, it looks fine, so I think it's working right (by which I mean that it is not finding a user, so it fails). A few things to try:

echo out $rs->recordCount in case it is a -1 ('adUnknown' value)

echo out your query and try it directly in your db client and make sure the user exists (which it won't if you are using encrypted passwords or something in your DB).

Tom

[meclive]

Thanks for your reply but unfortunatly im still having no joy! i have tried what you said, and the user does exist in the db. Any further help would be appreciated.

[ergophobe]

How about this....

change

$strSQL = "SELECT * FROM accounts WHERE username = '$Username'

to

$strSQL = "SELECT username, password FROM accounts";
(impose some sort of limit if you already have a lot of users)

Then just do this

while (!$rs->EOF)
{
echo "<br> . $rs->Fields["username"]->value . ": " . $rs->Fields["password"]->value;
$rs->MoveNext();
}

Is this working? Are you getting the values expected?

Tom

[meclive]

Yep it shows the username/passwords.

im still thinking its got something to do with the "recordcount".

Any ideas?

[StupidScript]

What about:

if (!$rs->recordCount()) {

Won't that match both -1 and 0?

[ergophobe]

A few things:

1. In PHP, -1 evaluates to true.

2. Did you echo $rs->recordCount? What is it? If not an integer, try

var_dump(get_object_vars($rs));

3. You could just do this

if ($rs->Fields["username"]->value && $rs->Fields["username"]->value == $Username)
{
echo "Username OK<br>";
if ($rs->Fields["password"]->value && $rs->Fields["password"]->value == $password)
{
echo "Password OK<br>";
$_SESSION['userAuthenticated'] = true;
}
else
{
echo "Problem with password<br>";
}
}
else
{
echo "Problem with Username<br>";
}

How's that work?

[meclive]

Thanks for your replies, i have looked at the examples given and modified my code. Now comes the weird part... it all works ok if you use a correct username/password combination, but if you enter one in wrong then you get the following error message;

"Warning: main(): PropGet() failed: Exception occurred. Source: ADODB.Field Description: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. in login.php on line 17"

The code i am using which gives this error is;

Login.php code
<?php
// Main ----------
session_start();

// Get the data collected from the user
$Username =$_POST["username"];
$Password =$_POST["password"];

if (!$conn = new COM("ADODB.Connection"))
exit("Unable to create an ADODB connection<br>");
$strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("DATABASEFILE");
$conn->open($strConn);

$strSQL = "SELECT username, password FROM accounts WHERE username = '$Username' AND password = '$Password'";
$rs = $conn->execute($strSQL);

if ($rs->Fields["Username"]->value && $rs->Fields["Username"]->value == $Username)
if ($rs->Fields["Password"]->value && $rs->Fields["Password"]->value == $Password)
{
$_SESSION["authenticatedUser"] = $Username;
// Relocate to the logged-in page
header("Location: loggedon.php");
}
else
{
$_SESSION["message"] = "Login Error as $Username. " ;
header("Location: admin.php");
}
?>

Thanks in advance for replies.

[ergophobe]

The error is because the result set is empty and you are trying to access it.

You still need to check that the query was successful and that at least one rwo was returned.

The one question I've asked twice which you still haven't answered is what happens when you echo the value of $rs->recordCount.

Try this:

print_r(get_object_vars($rs));

And tell us what you get.

Tom

[meclive]

When i type in "print_r(get_object_vars($rs));" i get the following;

Array ( [0] => Resource id #2 )

what does it mean?

[meclive]

Can i also ask on your earlier reply when you stated the code;

"if ($rs->Fields["Username"]->value && $rs->Fields["Username"]->value == $Username)"

Why is the recordset username value called twice? Just be interested to know, thanks.

[ergophobe]

Why is the recordset username value called twice?

Because I'm assuming, which I probably should not, that you do not want to allow zero-length passwords. So if the user enters "" and has no password in the database, you want to return false and not let the user in.

[ergophobe]

Array ( [0] => Resource id #2 )

Okay, sort of feeling my way around here because this is new to me.

This is because COM instances are returned as resources [phpbuilder.com], not actual objects. So it's more like a MySQL result set rather than a class object.

Now, I still don't understand why the RecordCount method does not work. It seems from your error message, though, that the EOF is set. Normally, if the record set is empty, BOF and OEF are set to true and RecordCount is set to 0, so it should work either way, but for some reason that doesn't seem to work.

Rather than testing for $rs->Recordcount > 0 what if you do a

while (!$rs->EOF)

How does that work?

[meclive]

ok i will try that, but im unsure where to insert that into my code?

Login.php code
<?php
// Main ----------
session_start();

// Get the data collected from the user
$Username =$_POST["username"];
$Password =$_POST["password"];

if (!$conn = new COM("ADODB.Connection"))
exit("Unable to create an ADODB connection<br>");
$strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("DATABASEFILE");
$conn->open($strConn);

$strSQL = "SELECT username, password FROM accounts WHERE username = '$Username' AND password = '$Password'";
$rs = $conn->execute($strSQL);

if ($rs->Fields["Username"]->value && $rs->Fields["Username"]->value == $Username)
if ($rs->Fields["Password"]->value && $rs->Fields["Password"]->value == $Password)
{
$_SESSION["authenticatedUser"] = $Username;
// Relocate to the logged-in page
header("Location: loggedon.php");
}
else
{
$_SESSION["message"] = "Login Error as $Username. " ;
header("Location: admin.php");
}
?>

[ergophobe]

Your code is missing brackets

You could use and if or a while with the same result, since it's only one row.

Thus

if (!$rs->EOF) 
{ 
 if (  $rs->Fields["Username"]->value  
    && $rs->Fields["Username"]->value == $Username 
    && $rs->Fields["Password"]->value  
    && $rs->Fields["Password"]->value == $Password 
   ) 
 { 
   $_SESSION["authenticatedUser"] = $Username;  
 } 
} 

[meclive]

THANKYOU ergophobe! your a genius! finally got it all working. I will post the code below in case anyone else was stuck on this.

Login.php code
<?php
// Main ----------
session_start();

// Get the data collected from the user
$Username =$_POST["username"];
$Password =$_POST["password"];

if (!$conn = new COM("ADODB.Connection"))
exit("Unable to create an ADODB connection<br>");
$strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("DATABASEFILE");
$conn->open($strConn);

$strSQL = "SELECT username, password FROM accounts WHERE username = '$Username' AND password = '$Password'";
$rs = $conn->execute($strSQL);

if (!$rs->EOF)
{
if ( $rs->Fields["Username"]->value
&& $rs->Fields["Username"]->value == $Username
&& $rs->Fields["Password"]->value
&& $rs->Fields["Password"]->value == $Password
)
{
$_SESSION["authenticatedUser"] = $Username;
// Relocate to the logged-in page
header("Location: loggedon.php");
}
}
else
{
$_SESSION["message"] = "Login Error as $Username. " ;
header("Location: admin.php");
}

?>

[ergophobe]

Thanks. I'm no genius, but I am persistent and I like solving problems and learning new things.

I'm still a bit perplexed that RecordCount didn't work. It is a valid ADO property and it should be available if EOF is available.

I will post the code below in case anyone else was stuck on this.

Ah, a common courtesy often forgotten. Thanks.

Tom

[meclive]

Ok im stuck again, lol. This time on deleting a record within my database. I have set it where the user inputs a product number to delete it from the database, i can get this working ok but if the user enters a wrong product number then it still outputs the same message "Successfully Deleted Product:" but does not delete the info in the table. below is the code i am using so far...

delete.php code
<?php
$partno = $_POST["partno"];

if (!$conn = new COM("ADODB.Connection"))
exit("Unable to create an ADODB connection<br>");
$strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("DATABASEFILE");
$conn->open($strConn);

$strDEL = "DELETE FROM products WHERE Part_No = '$partno'";
$rs = $conn->execute($strDEL);

if (!$rs)
{
print "Could Not Find Product: $partno";
}
else
{
print "Successfully Deleted Product: $partno";
}
?>

Any ideas?

[ergophobe]

Again, this is because $rs is a resource id so on a successful execution, it will be a resource number, which will be a positive integer, which will be true.

Not a successful execution, you say? But it is. A query that returns/affects no rows is still valid as long as it doesn't cause a server error. Just because the delete "failed" in the sense that there was no record matching the "where" criteria to delete, does not mean it should return false.

I don't know enough about the return values from ADO going through a COM server, but why is the user entering an ID?

It seems to me that your program logic should dictate that users only be allowed to delete values that exist. So

1. They choose from a list (click a link, check checkboxes and submit, something like that).

2. You check that the record exists, if so, you try to delete, if not you send an error "Record 21 does not exist". This seems really dangerous though since users could delete records for which they don't have privileges.

Tom