using a database to store username/password combos.

[amplexus]

Hi,

 

my goal is to be able to create a web based interface for someone who has no programming skills or interest to be able to maintain a list of usernames and passwords for protecting a page of links on a website.

 

so, I've created a page that can write to a database, and I can see that it is working, I can read entries, etc.

 

now, I need to know how to make a script that will check against that database for valid UN/PW combinations.

 

what is the best method for this?

 

James

[amplexus]

this is the script for writing to the database. 

 

<?

include("dbinfo.inc.php");

mysql_connect($servname,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM $newdbname WHERE id='$id'";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

 

$i=0;

while ($i < $num) {

$username=mysql_result($result,$i,"username");

$password=mysql_result($result,$i,"password");

$real_name=mysql_result($result,$i,"real_name");

 

?>

 

<form action="updated.php">

<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">

Real Name: <input type="text" name="ud_first" value="<? echo "$real_name"?>"><br>

Username: <input type="text" name="ud_last" value="<? echo "$username"?>"><br>

Password: <input type="text" name="ud_phone" value="<? echo "$password"?>"><br>

 

<input type="Submit" value="Update">

</form>

 

<?

++$i;

}

?>

[amplexus]

I guess I should note that I have a book/tutorial on how to use a form to write to a txt file, and how to read that text file into an array that can be checked, but I wonder if that is less secure?  either way I'd like to know how one reads table columns into an array.

 

if my question is too stupid, someone please let me know.  I've been searching all over this site for an answer I can comprehend.

[amplexus]

I've even got this much written, although I fear it is horribly wrong. 

<?php 

include("dbinfo.inc.php");
mysql_connect($servname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM $newdbname WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

// Define username and password as stings 
$username = "$user_name"; 
$password = "$password"; 

if ($_POST['txtUsername'] != $username || $_POST['txtPassword'] != $password) { 

?> 

<h1>Login</h1> 

<form name="form" method="post" action="WHAT DO I PUT HERE"> 
    <p><label for="txtUsername">Username:</label> 
    <br /><input type="text" title="Enter the new Username" name="username" /></p> 

    <p><label for="txtpassword">Password:</label> 
    <br /><input type="password" title="Enter the user password" name="password" /></p> 

    <p><input type="submit" name="Submit" value="Login" /></p> 

</form> 

 

[amplexus]

nobody's even going to say "i need more info" or " this is a stupid question"?

[DavidAM]

First things first.  When storing the password in a database (or a file for that matter) do NOT store it in the clear (that is, as entered by the user). Store a hash of the password. That way if someone hacks the database and reads your users table, they do not have the user's password. Why? Lots of users use the same password for many sites, if someone can get passwords from your database, they might use it to get into some other site. And if they got someone's password from another site, it should not hash to the same thing on your site.

 

Ok. On to your question.  The process is fairly straightforward:

if the form was POSTed
  if they did not enter ALL the data (both username and password)
    set an error message
  elseif  there is not a match in the database
    set an error message (do NOT tell the user which one is wrong)
  else
     record the fact that the user is logged in (use a session)
    send them off to another page
  endif
endif
display the form (with error message)

When you use this approach, the form and the login code are in the same file. So the ACTION in your form tag will be the same filename (i.e. "/login.php") as the one that generates it.  Do NOT use PHP_SELF or an empty string (""). The safest action value is a hard-coded absolute pathname.

 

You need to use a session to track the fact that they are logged in when you get to the next page.

 

filename:login.php (for instance)

<?php
// During development, we always want to see any errors or warnings
error_reporting(E_ALL);
ini_set('display_errors', 1);

// If your form was POSTed, the button name will be in the POST array
if (isset($_POST['Submit'])) { // Was the form posted?
  if ( (empty($_POST['username'])) or (empty($_POST['password'])) ) { // Is either field missing?
    $msg = 'Please enter your username and password';
  } else { // Need to check the database
    include("dbinfo.inc.php");
    if (! mysql_connect($servname,$username,$password)) {
      $msg = 'Database access error. Try Again Later';
    } elseif (!mysql_select_db($database) ) {
      $msg = 'Database access error. Try Again Later';
    } else { // OK we're connected
      $sql = sprintf("SELECT id FROM users WHERE username = '%s' AND password = '%s'",
          mysql_real_escape_string($_POST['username']), 
          md5($_POST['password'])); // Use the same hash algorithm used to store the password
      $res = mysql_exec($sql);
      if ($res === false) { // Did the query fail?
        $msg = 'Database access error. Try Again Later';
        // For development you can add:
        $msg .= mysql_error();
      } elseif (mysql_num_rows($res) != 1) { // There should only be 1 matching record
        $msg = 'Invalid Username or Password';
      } elseif (! $row = mysql_fetch_assoc($res)) { // $row is FALSE if we failed
        $msg = 'Database access error. Try Again Later';
      } else {
        start_session();
        $_SESSION['UserID'] = $row['id'];
        header('Location: /index.php'); // send them to the next page
        exit; // ALWAYS EXIT AFTER A HEADER() REDIRECT
      }
    }
  }
}

// Now your form - Almost exactly like you had it 
?>
<h1>Login</h1> 

<form name="form" method="post" action="/login.php"> 
    <p><label for="txtUsername">Username:</label> 
    <br /><input id="txtUsername" type="text" title="Enter the new Username" name="username" /></p> 

    <p><label for="txtpassword">Password:</label> 
    <br /><input id="txtpassword' type="password" title="Enter the user password" name="password" /></p> 

    <p><input type="submit" name="Submit" value="Login" /></p> 

</form> 
<?php
if (!empty($msg)) {
  echo '<P>' . $msg . '</P>';
}

Notes:

• I added the message at the bottom (below the form)
  
• In your INPUT tags, you needed an ID attribute with a value that matches the FOR attribute in the LABEL
  
• I guessed at the name for your user's table, as well as the columns in that table.
  

 

Now you have let the user login.  On any page that requires a logged-in user, you have to add (near the top of the file):

session_start();
if (empty($_SESSION['UserID'])) {
  header('Location: /login.php');
  exit();
}

That checks to see if they have logged in and will send them to the login page if they have not.

 

Disclaimer: This code is not tested. I typed it off the top of my head. It may contain syntax errors, typographical errors, logic errors or alzhiemers errors. The code's not pretty, and may not be the most efficient.  Even so it is worth at least twice what you paid for it.

[Namtip]

<?php
session_start();

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

// filter incoming values
$username = (isset($_POST['username'])) ? trim($_POST['username']) : '';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
$redirect = (isset($_REQUEST['redirect'])) ? $_REQUEST['redirect'] : 'main.php';

if (isset($_POST['submit'])) {
    $query = 'SELECT admin_level FROM site_user WHERE ' .
         'username = "' . mysql_real_escape_string($username, $db) . '" AND ' .
         'password = PASSWORD("' . mysql_real_escape_string($password, $db) . '")';
    $result = mysql_query($query, $db) or die(mysql_error($db));

    if (mysql_num_rows($result) > 0) {
        $row = mysql_fetch_assoc($result);
        $_SESSION['username'] = $username;
        $_SESSION['logged'] = 1;
        $_SESSION['admin_level'] = $row['admin_level'];
        header ('Refresh: 5; URL=' . $redirect);
        echo '<p>You will be redirected to your original page request.</p>';
        echo '<p>If your browser doesn\'t redirect you properly automatically, ' .
            '<a href="' . $redirect . '">click here</a>.</p>';
        mysql_free_result($result);
        mysql_close($db);
        die();
    } else {
        // set these explicitly just to make sure
        $_SESSION['username'] = '';
        $_SESSION['logged'] = 0;
        $_SESSION['admin_level'] = 0;

        $error = '<p><strong>You have supplied an invalid username and/or ' .
            'password!</strong> Please <a href="register.php">click here ' .
            'to register</a> if you have not done so already.</p>';
    }
    mysql_free_result($result);
}
?>
<html>
<head>
  <title>Login</title>
</head>
<body>
<?php
if (isset($error)) {
    echo $error;
}
?>
  <form action="login.php" method="post">
   <table>
    <tr>
     <td>Username:</td>
     <td><input type="text" name="username" maxlength="20" size="20"
       value="<?php echo $username; ?>"/></td>
    </tr><tr>
     <td>Password:</td>
     <td><input type="password" name="password" maxlength="20" size="20"
       value="<?php echo $password; ?>"/></td>
    </tr><tr>
     <td> </td>
     <td>
      <input type="hidden" name="redirect" value="<?php echo $redirect ?>"/>
      <input type="submit" name="submit" value="Login"/>
    </tr>
   </table>
  </form>
</body>
</html>
<?php
mysql_close($db);
?>

To get this to work save the script as "login.php" and put it in the right localhost folder. Give it a test.

There ya go, theres a nifty login script. I assume you have a registration script? the 'action="what do I put here" ' is the part of the form that tells the browser what page to go to when the user clicks the submit/log in button. As you can see I've selected the same file. which might seems wierd but try and figer out what it is actually doing.

 

It filters the input to see if you've entered anything, then checks if its correct. If it is correct it redirects you to the page you might want to go to(main.php). Have a play with that and see if it works.

I'll be back in an hour to see if anyone has any answers for my nightmare wtf post in an hour (hint hint).

[amplexus]

First things first.  When storing the password in a database (or a file for that matter) do NOT store it in the clear (that is, as entered by the user). Store a hash of the password. That way if someone hacks the database and reads your users table, they do not have the user's password.

Okay.  I'm working on establishing this code into the pages, thanks so far for your help!  wondering about this part though,,,

 

where in this code do I make a "hash" of the password?

<?


include("dbinfo.inc.php");
mysql_connect($servname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 

$query = "INSERT INTO $newdbname VALUES ('','$real_name','$username','$password')";
mysql_query($query);

mysql_close();
?>
<html>
<body>
user added
</body>
</html> 

 

Still trying to make some sense of the other code.  I read code like I read music.  One line(note) at a time and if it gets fancy, It takes me a while to get in the mode of reading it.

 

James

 

[DavidAM]

Just before the INSERT statement:

$password = md5($password); // SEE NOTE BELOW
$query = "INSERT INTO $newdbname VALUES ('','$real_name','$username','$password')";

 

The hash in my example is NOT sufficient. You want to read up on this and pick an algorithm and a seed that will provide the security you want.

 

On a side note.  I see that you are using the same username and password variables for your database login and the insert statement.  Is this what you intended?

mysql_connect($servname,$username,$password);

$query = "INSERT INTO $newdbname VALUES ('','$real_name','$username','$password')";

 

Usually, you have a single database username and database password which have no relation to the users you are allowing in the application.

[amplexus]

David: 

 

as you were writing that, I was going through and changing those very variables.  that was causing me no shortage of head pain before i had that "aha" moment

[amplexus]

okay.  I'm in up to my neck, and sinking fast.  I thought I had it working but now when I use my submit script, it creates a new entry to the database, but it's blank.  I have the ID on the table auto incrementing, so I know I'm adding new stuff, but the username, password and real name are all blank. And, yes, I know I haven't put in the hash algorithm yet.  one bridge to collapse at a time.  here's my revised script with the fixed variables, and also the form I'm using to get it there.

<form action="insert.php" method="post">
Username: <input type="text" name="username"><br>
Password: <input type="password"  name="password"><br>
User Real Name: <input type="text" name="real_name"><br>
<input type="Submit">
</form>

<?php
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database"); 

$query = "INSERT INTO $newdbname VALUES ('','$username','$password','$real_name')";
mysql_query($query);

mysql_close();

echo "User Created!";

?>

 

[DavidAM]

You need to assign the values to the $username, $password, and $real_name variables.  When you POST a form, the fields end up in a super-global array called $_POST.  The keys to the array are the field names and the values are the user entered values.  There used to be a setting to have this done automatically, but that setting has been depricated, turned off by default, and will go away in the next release.  There were some serious security issues with it.  Also, you need to protect against SQL injections, so you need to run the POSTed values through mysql_real_escape_string().  So, before your insert statement you need something like this:

$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$real_name = mysql_real_escape_string($_POST['real_name']);

$query = "INSERT INTO $newdbname VALUES ('','$username','$password','$real_name')";

 

[amplexus]

thank you!

 

now on to the more complex stuff.  did I say thank you?

[amplexus]

<?php
session_start();

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

// filter incoming values
$username = (isset($_POST['username'])) ? trim($_POST['username']) : '';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
$redirect = (isset($_REQUEST['redirect'])) ? $_REQUEST['redirect'] : 'main.php';

if (isset($_POST['submit'])) {
    $query = 'SELECT admin_level FROM site_user WHERE ' .
         'username = "' . mysql_real_escape_string($username, $db) . '" AND ' .
         'password = PASSWORD("' . mysql_real_escape_string($password, $db) . '")';
    $result = mysql_query($query, $db) or die(mysql_error($db));

    if (mysql_num_rows($result) > 0) {
        $row = mysql_fetch_assoc($result);
        $_SESSION['username'] = $username;
        $_SESSION['logged'] = 1;
        $_SESSION['admin_level'] = $row['admin_level'];
        header ('Refresh: 5; URL=' . $redirect);
        echo '<p>You will be redirected to your original page request.</p>';
        echo '<p>If your browser doesn\'t redirect you properly automatically, ' .
            '<a href="' . $redirect . '">click here</a>.</p>';
        mysql_free_result($result);
        mysql_close($db);
        die();
    } else {
        // set these explicitly just to make sure
        $_SESSION['username'] = '';
        $_SESSION['logged'] = 0;
        $_SESSION['admin_level'] = 0;

        $error = '<p><strong>You have supplied an invalid username and/or ' .
            'password!</strong> Please <a href="register.php">click here ' .
            'to register</a> if you have not done so already.</p>';
    }
    mysql_free_result($result);
}
?>
<html>
<head>
  <title>Login</title>
</head>
<body>
<?php
if (isset($error)) {
    echo $error;
}
?>
  <form action="login.php" method="post">
   <table>
    <tr>
     <td>Username:</td>
     <td><input type="text" name="username" maxlength="20" size="20"
       value="<?php echo $username; ?>"/></td>
    </tr><tr>
     <td>Password:</td>
     <td><input type="password" name="password" maxlength="20" size="20"
       value="<?php echo $password; ?>"/></td>
    </tr><tr>
     <td> </td>
     <td>
      <input type="hidden" name="redirect" value="<?php echo $redirect ?>"/>
      <input type="submit" name="submit" value="Login"/>
    </tr>
   </table>
  </form>
</body>
</html>
<?php
mysql_close($db);
?>

 

I'll be back in an hour to see if anyone has any answers for my nightmare wtf post in an hour (hint hint).

 

Namtip:

 

this seems pretty straightforward, except for the "admin_level" bits, which I can't figure out where that should exist in the table.  it's not supposed to be the table name, is it?

 

DavidAM:

 

I tried to make sense of your login script too, it was a Little over my head, as I am not yet well versed enough in the if:elseif aspects.  trying though!

 

thanks

[amplexus]

hey,

I am lost about one or two things.  I've been able to make it work by changing all of the "admin_level" tags to something like the "id" column header i have in my table, but it still doesn't' actually verify with any of the user/pass combos I've loaded into the table.  is "admin_level" a MySQL function?

 

here's that script  along with some mods already made , although I put the admin_level tags back in for demonstration purposes

 

<?php

session_start();
include"dbinfo.inc.php";
$db = mysql_connect($servname,$dbusername,$dbpassword) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db($database, $db) or die(mysql_error($db));

// filter incoming values
$username = (isset($_POST['username'])) ? trim($_POST['username']) : '';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
$redirect = (isset($_REQUEST['redirect'])) ? $_REQUEST['redirect'] : 'secret.php';

if (isset($_POST['submit'])) {
    $query = 'SELECT admin_level FROM test2 WHERE ' .
         'username = "' . mysql_real_escape_string($username, $db) . '" AND ' .
         'password = PASSWORD("' . mysql_real_escape_string($password, $db) . '")';
    $result = mysql_query($query, $db) or die(mysql_error($db));

    if (mysql_num_rows($result) > 0) {
        $row = mysql_fetch_assoc($result);
        $_SESSION['username'] = $username;
        $_SESSION['logged'] = 1;
        $_SESSION['admin_level'] = $row['admin_level'];
        header ('Refresh: 5; URL=' . $redirect);
        echo '<p>You will be redirected to your original page request.</p>';
        echo '<p>If your browser doesn\'t redirect you properly automatically, ' .
            '<a href="' . $redirect . '">click here</a>.</p>';
        mysql_free_result($result);
        mysql_close($db);
        die();
    } else {
        // set these explicitly just to make sure
        $_SESSION['username'] = '';
        $_SESSION['logged'] = 0;
        $_SESSION['admin_level'] = 0;

        $error = '<p><strong>You have supplied an invalid username and/or ' .
            'password!</strong> Please <a href="register.php">click here ' .
            'to register</a> if you have not done so already.</p>';
    }
    mysql_free_result($result);
}
?>
<html>
<head>
  <title>Login</title>
</head>
<body>
<?php
if (isset($error)) {
    echo $error;
}
?>
  <form action="login.php" method="post">
   <table>
    <tr>
     <td>Username:</td>
     <td><input type="text" name="username" maxlength="20" size="20"
       value="<?php echo $username; ?>"/></td>
    </tr><tr>
     <td>Password:</td>
     <td><input type="password" name="password" maxlength="20" size="20"
       value="<?php echo $password; ?>"/></td>
    </tr><tr>
     <td> </td>
     <td>
      <input type="hidden" name="redirect" value="<?php echo $redirect ?>"/>
      <input type="submit" name="submit" value="Login"/>
    </tr>
   </table>
  </form>
</body>
</html>
<?php
mysql_close($db);
?>

 

thanks for your time.

[Namtip]

Hello amplexus!

  Nice to hear from you again, I'm sorry I'm late with my reply I wasn't checking up on the post because I thought you didn't like my script. Bet you love me now eh?

OK, my friend, admin_level isn't a mysql function. It is the name of a column in the data base code I was using(does that make sense?). The column would give some users special privileges in the site. So If I were you I'd just delete the relevant code.

  So you've loaded the script with no erros but you can't log in? I'm guessing you're getting the error message from the script you've written not a parse error( if its a parse error show us? If not, hmm.. I'm guessing the admin_level column and the login script don't match. Could you post your database stuff? Don't worry I'll be checking your post frequently tonight and tomorrow. hopefully get something sorted tonight. But yeah post your mysql database up please.

[amplexus]

so, I'm not sure about how to post up the sql stuff, so I'll try to explain it.

 

I have created a table called "test2" which is described in teh dbinfo.inc.php file that is an include at teh top of the script.

 

the table has four columns,  "id" which is an auto incrementing column, username, password, and real_name (included so the admin can tell who is who in an encrypted database.

 

right now when I change everything that was "admin_level" to "id", it "works" in that i get no error other than what the script is programmed to give when the user/pass combo doesn't match the database.  I'm not sure if that's because it's not reading the database correctly, or at all....

 

make sense?

[amplexus]

and yes namtip, you're the best 

[fortnox007]

Thx david for your structured approach! Was looking for something alike  And thx for the extra insight and tips for php_self.

[Namtip]

I'm tired :-\ I've changed the code run these scripts.

save the script as "db_namtipisawesome".

<?php
require 'db.inc.php';

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

// create the user table
$query = 'CREATE TABLE IF NOT EXISTS site_user (
        user_id     INTEGER     NOT NULL AUTO_INCREMENT,
        username    VARCHAR(20) NOT NULL,
        password    CHAR(41)    NOT NULL,

        PRIMARY KEY (user_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// populate the user table
$query = 'INSERT IGNORE INTO site_user
        (user_id, username, password) 
    VALUES
        (1, "namtip", PASSWORD("secret")),
        (2, "amplexus", PASSWORD("password"))';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Success!';
?>

Save as login.php:

<?php
session_start();

include 'db.inc.php';

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

// filter incoming values
$username = (isset($_POST['username'])) ? trim($_POST['username']) : '';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
$redirect = (isset($_REQUEST['redirect'])) ? $_REQUEST['redirect'] : 'main.php';

if (isset($_POST['submit'])) {
    $query = 'SELECT user_id FROM site_user WHERE ' .
         'username = "' . mysql_real_escape_string($username, $db) . '" AND ' .
         'password = PASSWORD("' . mysql_real_escape_string($password, $db) . '")';
    $result = mysql_query($query, $db) or die(mysql_error($db));

    if (mysql_num_rows($result) > 0) {
        $row = mysql_fetch_assoc($result);
        $_SESSION['username'] = $username;
        $_SESSION['logged'] = 1;
        header ('Refresh: 5; URL=' . $redirect);
        echo '<p>You will be redirected to your original page request.</p>';
        echo '<p>If your browser doesn\'t redirect you properly automatically, ' .
            '<a href="' . $redirect . '">click here</a>.</p>';
        mysql_free_result($result);
        mysql_close($db);
        die();
    } else {
        // set these explicitly just to make sure
        $_SESSION['username'] = '';
        $_SESSION['logged'] = 0;

        $error = '<p><strong>You have supplied an invalid username and/or ' .
            'password!</strong> Please <a href="register.php">click here ' .
            'to register</a> if you have not done so already.</p>';
    }
    mysql_free_result($result);
}
?>
<html>
<head>
  <title>Login</title>
</head>
<body>
<?php
if (isset($error)) {
    echo $error;
}
?>
  <form action="login.php" method="post">
   <table>
    <tr>
     <td>Username:</td>
     <td><input type="text" name="username" maxlength="20" size="20"
       value="<?php echo $username; ?>"/></td>
    </tr><tr>
     <td>Password:</td>
     <td><input type="password" name="password" maxlength="20" size="20"
       value="<?php echo $password; ?>"/></td>
    </tr><tr>
     <td> </td>
     <td>
      <input type="hidden" name="redirect" value="<?php echo $redirect ?>"/>
      <input type="submit" name="submit" value="Login"/>
    </tr>
   </table>
  </form>
</body>
</html>
<?php
mysql_close($db);
?>

Run both scripts in order. I use the include script to hold the variables (MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) so I can access my script. Nothing else to do with tables in there. I've changed it so the script checks if the username and password's user_id is above 0 value. Check if it works on values over 3. should ddo but I'm tired and I have to work on my script . because I newbie too. See if you can help on my port yeah? yeah!

[Namtip]

and damn mark as solved and start up a new post

[amplexus]

pretty sure that helps.  lot's of rescripting, but I'm 99% there.  thanks to all!