User management in a web application

[Easyb]

Hi there,

I am working on the user side of a new webapp. Am coding using php on netbeans. I have a situation as below:

Background:I like the first page (index.php) to have the login page (with link to recover the password if forgotten only). After successful login, user must be checked if is admin or not. If admin, user is given other pages like register and view users with links to edit or delete user accounts. If user is not admin, link is given to perform other normal activities on the app including managing their account (like password change only...)

My problems are below:
i) creating that initial admin account to be used to create the users (either admin or normal user) , whose details will be communicated to them (users) via some other means..(since the app is in-house).
ii) integrating the login on the first page (index.php), and after successful login, user is redirected to respective pages based on their rights and if not successful, user is returned to index.php to login (ofcourse with details of any encountered error).

Grateful for any idea.

[guido2004]

i) creating that initial admin account to be used to create the users (either admin or normal user) , whose details will be communicated to them (users) via some other means..(since the app is in-house).

What exactly is the problem you're having here?
Can't you just use the registration form the admin will use to add users?

ii) integrating the login on the first page (index.php), and after successful login, user is redirected to respective pages based on their rights and if not successful, user is returned to index.php to login (ofcourse with details of any encountered error).

I usually have a single entry point (index.php). Then all I have to do is check if the user is logged in, and if he has the right authorisation to view the page requested. If the user isn't logged in, show the login form. If he hasn't got the right authorisation, display an error or whatever you want to do in that situation.

[Easyb]

Thanks Guido for tip..
My main problem is managing login from the single entry (index.php). I have so far a login script (login.php) as below:

<?php
//PHP Script - login.php

include ('../mysqli_connect.php');

if(isset ($_POST['submit']))
{
    $username = strip_tags($_POST['username']);
    $username = mysql_escape_string($username);
    $password = strip_tags($_POST['pwd']);
    $password = mysql_escape_string($password);
    
    if($username == "" OR $password == "")
    {
        echo "Either username or password field is empty.";
        echo "<br />";
        echo "<a href='index.php'>Return to login page</a>";
    }
    else
    {
       $sql = "SELECT * FROM users WHERE user_name ='$username'AND pass = SHA1

('$password')";
       $res = mysqli_query($dbc, $sql) or die (mysqli_error($dbc)); //line 45
       
       $row = mysqli_fetch_assoc($res, MYSQLI_ASSOC);//line 46
       
       if(is_array($row) && !empty ($row))
       {
           $validuser = $row['user_name'];
           $_SESSION['valid'] = $validuser;
       }
       else {
           echo "Invalid username or password.";
            echo "<br />";
            echo "<a href='index.php'>Return to login page</a>";
       }
    }
}

?>

I have a problem with this code as well. Brief description below:
When I introduce

or die (mysqli_error($dbc)); 

on line 45, I get the error below:

Unknown column 'user_name' in 'where clause'

When I take out the die(); on line 45, I get the error below after running it:

Warning: mysqli_fetch_assoc() expects exactly 1 parameter, 2 given in D:\USBWebserver v8_en\root\login\login.php on line 46

With these errors, I can't log in using a superadmin account i created during the DB design. If I can log in, then l have to add some user check after successful login so they are redirected to the right page based on their user level.

Grateful for your advice..

[guido2004]

That error tells you that you don't have a column called 'user_name' in the users table.

[Easyb]

Thanks again Guido..
Below is my users table sql structure:

CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL,
user_name CHAR (20) NOT NULL,
first_name VARCHAR (20) NOT NULL,
last_name VARCHAR (40) NOT NULL,
email VARCHAR (60),
pass CHAR (40) NOT NULL,
POSITION VARCHAR (30),
dept VARCHAR (40),
registration_date DATETIME NOT NULL,
user_level CHAR(6) NOT NULL,
PRIMARY KEY (user_id)
);

i don't understand why the error is persistent..
help is appreciated

[guido2004]

If that is the users table, it looks like the user_name column does exist. Strange. Try putting a space before AND.
Another thing to try: echo $sql, and run it in phpMyAdmin. What result does that give?

The second error is quite clear: mysqli_fetch_assoc() has only 1 parameter. Eliminate MYSQLI_ASSOC

[Easyb]

Adding the extra space before AND gives the same error.

When I run query in phpmyadmin, $sql is successful (as below):

SELECT * 
FROM users
WHERE user_name = 'user1'  #this is $username entered in login form
AND pass = SHA1( 'userwims11' ) #this is $password entered in login form
LIMIT 0 , 30;

[guido2004]

And you copied and pasted that query from the echo of $sql ? You didn't fill in the name and password by hand?
In your code, the query is on one line. The query you posted now is formatted on more lines. Just copy and paste the query that is echoed and run it as is.

[Easyb]

phpmyadmin echoes this:

$sql = "SELECT * \n"
    . "FROM users\n"
    . "WHERE user_name = \'user1\' #this is $username entered in login form\n"
    . "AND pass = SHA1( \'userwims11\' ) #this is $password entered in login form\n"
    . "LIMIT 0 , 30"; 

I have to add the value of user_name and pass to reflect the one input by the user, giving me this:

$sql = "SELECT * \n"
    . "FROM users\n"
    . "WHERE user_name = '$username'"
    . "AND pass = SHA1( '$password' )"
    . "LIMIT 0 , 30"; 

Which gives the same error:

Unknown column 'user_name' in 'where clause'

[guido2004]

phpmyadmin echoes this:

Sorry I don't understand.

I meant you have to add the following statement to your PHP code:

echo $sql; 

so the actual query that is being executed will be displayed in your browser.
Copy the query displayed in your browser, and run it in phpMyAdmin. No need to insert the value of user_name and pass, because they will already be there.

[Easyb]

Setting & echo..

$sql = "SELECT * FROM users WHERE user_name = '$username'  AND pass = SHA1('$password')";
echo $sql; 

Result on browser:

SELECT * FROM users WHERE user_name = 'admwims' AND pass = SHA1('adminwims12')

On phpmyadmin (php code generated)..

$sql = "SELECT * FROM users WHERE user_name = \'admwims\' AND pass = SHA1(\'adminwims12\')"; 

This is succesful on phpmyadmin..

[guido2004]

On phpmyadmin (php code generated)..

I don't know what you mean by this.

Can't you just copy and paste the query you see in your browser?

Anyway, if the same identical query works fine in phpMyAdmin, and it doesn't in your script, then I really have no idea why. Are you sure you're connecting to the right database?

[Easyb]

the php code generated is when I entered the result from the browser on phpmyadmin sql interface, it ran okay and I clicked Create PHP Code.

I am connecting to the right database, which details are below:

//mysqli_connect.php
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 't1212');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'test');

//Make the connection to the database.
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die ('Could not connect to MYSQL: ' . mysqli_connect_error()); 

Could there be something wrong with this code snippet?:

if(isset ($_POST['submit'])){
$username = strip_tags($_POST['username']);
$password = strip_tags($_POST['pwd']);

if (isset ($username) && isset ($password))
{
    $sql = "SELECT * FROM users WHERE user_name = '$username'  AND pass = SHA1('$password')";
   
    $rs = mysqli_query($dbc,$sql ) OR die (mysqli_error($dbc));
       
    $row = mysqli_fetch_assoc($rs);
    
    if(mysql_num_rows($row)>0)
    {
        if($row['user_level']== 'admin')
        {
            header("Location:admin/welcome_admin.php");
        }
        else{
            header("Location:admin/register.php");
        }
}
} 

Help is appreciated

[Easyb]

Found out the problem with the location of my mysqli_connect.php file.

There were two versions..one pointing to another DB and one I was using pointing to my right DB.

Will continue the coding and if I have trouble, will post here..

Thanks again (from UGANDA with love too..) for support GUIDO (from ITALY with Love..)..