Jump to content

Implementing Authentication with PHP and MySQL


wolocaw

Recommended Posts

Hello.

 

Through MySQL, I created a database using the following code:

create database auth;
use auth;
create table authorized_users ( name varchar(20), 
                                password varchar(40),
                                        primary key     (name)
                              );
insert into authorized_users values ( 'username', 
                                      'password' );

insert into authorized_users values ( 'testuser', 
                                      sha1('password') );
grant select on auth.* 
             to 'webauth' 
             identified by 'webauth';
flush privileges;

 

 

Then I used the following code to set up a simple log in page.

 

<?php
  $name = $_POST['name'];
  $password = $_POST['password'];

  if ((!isset($name)) || (!isset($password))) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php
  } else {
    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

 

 

When I open up this page, above "Please Log In", I get this error:

 

Notice: Undefined index: name in C:\xampp\htdocs\learning\secretdb.php on line 2

 

Notice: Undefined index: password in C:\xampp\htdocs\learning\secretdb.php on line 3

 

 

 

Moreover, when I type in an authorized username and password, I get another error:

 

Warning: mysqli_connect() [function.mysqli-connect]: (28000/1045): Access denied for user 'webauth'@'localhost' (using password: YES) in C:\xampp\htdocs\learning\secretdb.php on line 19

Cannot connect to database.

 

 

Does anyone know what I'm doing wrong? I'm fairly new to PHP and MySQL and I'm pretty stuck. Thanks.

Link to comment
Share on other sites

for your MYSQL error you need to make sure your connection info matches up including db_name password and username. and make sure you use localhost, if your hosting uses a different MYSQL server might want to check there too.

 

 

also make sure you are putting your post variables after you check if there empty or isset

Link to comment
Share on other sites

I think my second error might be occurring because of the first error.

 

Where exactly should I put my post variables? I tried putting them in a couple different places but I still get the same error.

 

For example, I still get the same error, just at different lines, when I change the code to:

<?php

  if ((!isset($name)) || (!isset($password))) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php

  $name = $_POST['name'];
  $password = $_POST['password'];
  
  } else {
    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

Link to comment
Share on other sites

You are setting $name and $password even when the post variables may not be set, you need to put them after the else:

 

<?php
  if ((!isset($name)) || (!isset($password))) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php
  } else {
    $name = $_POST['name'];
    $password = $_POST['password'];

    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

 

You should also be careful of sql injection there, you will want to use mysql_real_escape_string() around both of the user inputs, or you could look into PDO prepared statements, http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

 

For your second problem, make sure that your credentials are correct for your database, if they are then try mysql_connect(), not mysqli_connect().

Link to comment
Share on other sites

Okay. I got it to work by manually creating a new user "webauth" using localhost and password "webauth" and giving it SELECT privilege through phpMyAdmin. This is also the php code that I used:

<?php

  if (!isset($_POST['name']) || !isset($_POST['password'])) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php
  } else {
  
    $name = $_POST['name'];
    $password = $_POST['password'];

    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

 

 

1. However, I don't understand why this works but not the original code that I had. I'm still using the post variables in the very beginning, just like before.

 

2. Moreover, how should I code my sql file so that I can simply import this file through phpMyAdmin to get this to work, instead of having to manually create the "webauth" user. Here is the sql file that I used that didn't work:

create database auth;
use auth;
create table authorized_users ( name varchar(20), 
                                password varchar(40),
                                        primary key     (name)
                              );
insert into authorized_users values ( 'username', 
                                      sha1('username') );

insert into authorized_users values ( 'testuser', 
                                      sha1('testuser') );
grant select on auth.* 
             to 'webauth' 
             identified by 'webauth';
flush privileges;

 

 

3. Finally, how should I use mysql_real_escape_string() here? I haven't really dealt with SQL injection yet so I'm not sure.

 

Link to comment
Share on other sites

Oh. It seems that I have to set the "webauth" user to specifically use localhost, instead of any host. Why is this and how should I do this in an sql file? I'd like to change my 2nd question in my previous post to this one.  :D

Link to comment
Share on other sites

try using a SQL validator like mimer or yacker next time here is the validated versionaccrding to SQL 2003 server

 

 

create SCHEMA auth;
CLOSE auth;
create table authorized_users ( name varchar(20), 
                                password varchar(40),
                                        primary key     (name)
                              );
insert into authorized_users values ( 'username', 
                                      sha1('username') );

insert into authorized_users values ( 'testuser', 
                                      sha1('testuser') );
grant select on auth 
             to webauth 
             identified by webauth;
CLOSE privileges;

Link to comment
Share on other sites

Sorry. Let me clarify. The sql file that I'm using works in that it doesn't have an error. It's just that it creates a user "webauth" that uses any host. When I manually change it so that it specifically uses localhost, then my php code does what I want it to do. I'm confused as to why this is the case.

 

I also want to know why this code works

<?php

  if (!isset($_POST['name']) || !isset($_POST['password'])) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php
  } else {
  
    $name = $_POST['name'];
    $password = $_POST['password'];

    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

 

but not this one.

<?php
  $name = $_POST['name'];
  $password = $_POST['password'];

  if ((!isset($name)) || (!isset($password))) {
  //Visitor needs to enter a name and password
?>
    <h1>Please Log In</h1>
    <p>This page is secret.</p>
    <form method="post" action="secretdb.php">
    <p>Username: <input type="text" name="name"></p>
    <p>Password: <input type="password" name="password"></p>
    <p><input type="submit" name="submit" value="Log In"></p>
    </form>

<?php
  } else {
    // connect to mysql
    $mysql = mysqli_connect("localhost", "webauth", "webauth");
    if(!$mysql) {
      echo "Cannot connect to database.";
      exit;
    }
    // select the appropriate database
    $selected = mysqli_select_db($mysql, "auth");
    if(!$selected) {
      echo "Cannot select database.";
      exit;
    }

    // query the database to see if there is a record which matches
    $query = "select count(*) from authorized_users where
              name = '".$name."' and
              password = sha1('".$password."')";

    $result = mysqli_query($mysql, $query);
    if(!$result) {
      echo "Cannot run query.";
      exit;
    }
    $row = mysqli_fetch_row($result);
    $count = $row[0];

    if ($count > 0) {
      // visitor's name and password combination are correct
      echo "<h1>Here it is!</h1>
            <p>I bet you are glad you can see this secret page.</p>";
    } else {
      // visitor's name and password combination are not correct
      echo "<h1>Go Away!</h1>
            <p>You are not authorized to use this resource.</p>";
    }
  }
?>

Link to comment
Share on other sites

Here, you are trying to USE a variable that isn't set, so it gives you a warning:

  $name = $_POST['name'];
  $password = $_POST['password'];

While here, you are checking to see if the variable is set, but not actually using it for anything:

  if (!isset($_POST['name']) || !isset($_POST['password'])) {

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.