Php/MySQL user authentication help

[javajenius]

I am trying to authenticate users with MySQL and php instead of using php and a text file.
The problem im having is that i do not understand how users are supposed to connect to the mysql database.

For example it says

mssql_pconnect("localhost","mssqluser","password")

If I am correct, you have to connect to a mysql database using a user account on the computer. So everytime someone logs in from the login form, i have to enter my user and password, then look at the database to authenticate the users? Is this correct?




Here is some basic code that I need to modify:

  <?

  /* SQL REQUIRED FOR THIS SCRIPT *****
   create table users (
    id INT NOT NULL,
    username VARCHAR(16),
    password VARCHAR(8),
    primary key(id));
  *****/

  function connect() {
    if(!$db = @mssql_pconnect("localhost","mssqluser","password")){  //here is the problem
      print("<h1>Cannot Connect to the DB!</h1>\n");
      return 0;
    } else {
      mssql_select_db("php", $db);
      return 1;
    }
  }

  function check_user($user, $password) {
    if(connect()) {
      $password = substr($password, 0, 8);
      $sql = "select * from users where username = '$user' and password = '$password'";
      $result = mssql_query($sql);
      if (mssql_num_rows($result) == 1) {
        setcookie("user",$user);
        setcookie("password",$password);
        return 1;
      } else {
        ?>
        <h3>Sorry, you are not authorized!</h3>
        <?
        return 0;
      }
    }
  }

  /***** MAIN *****/
  if(!isset($user) or !check_user($user, $password)) {
    ?>
    <h1>You must log in to view this page</h1>
    <form action = "DB_authenticate.php" method="post">
    <P>Username: <input type="text" name="user"><br>
    Password: <input type="password" name="password" maxlength="8" size="8"><br>
    <input type="submit" name="submit" value="Submit">
    </form>
    <?
  } else {
    ?>
    <h1>Authorized!</h1>
    <?
  }
  ?>

 

[woiwky]

If you're using MySQL, then you need to change those MS SQL function calls to their corresponding MySQL functions(which is basically just changing mssql to mysql). You can view all the MySQL functions here:

http://www.php.net/manual/en/ref.mysql.php

And yes, every time you need to access data from a database, you'll need to login to that database. If you have created the database and given a user access to it, then you just need to provide the login name/password as the user and password arguments for the mysql_pconnect() call.

 

[javajenius]

So i should create an account for every user ?

 

[woiwky]

No, I meant a db user. Sorry for the misunderstanding.

You should be able to view/create mysql users from your cpanel in the "MySQL Databases" section. One db user can be used for every script as long as the user has adequate access to the databases you want to deal with in the script. So if you made a mysql user named some_user, and made the password some_password, the call would be:

mysql_pconnect("localhost","some_user","some_password");

I have absolutely no idea why a space is appearing in 'password'. But you get what I mean I assume.

 

[javajenius]

If i am correct you are saying that as long as there is an account with privileges, you can open all databases. This account would have to be root, wouldn't it?

Sorry I am a bit confused.

 

[woiwky]

I'm not sure what you mean by 'root' in this context. However, yes, as long as the mysql user you create has access to the db(s) you want to use in your script, then you can use the login details for that account in the mysql connection function call.

 

[sunils]

According to your first statement, I can infer the following statement.

You need to authenticate users by username and password stored in a database table. If that is correct, then you can do this.

Create a database user using your Cpanel who have permission to access the database intended. Probably you can allow him to access all the databases pretaining to you site.

Then create a table to store the usernames and password for your users.

connect to your database using the database users that you have created using your cpanel.

Then execute a select query to the table which stores your users username and password and say if he is authenticated or not.

I think this will be helpful to you.

 

[tnl2k7]

I'm not sure what you mean by 'root' in this context. However, yes, as long as the mysql user you create has access to the db(s) you want to use in your script, then you can use the login details for that account in the mysql connection function call.

The MySQL root user has access to all the databases and has all privileges. You can't use it at x10Hosting, all users have to be assigned to databases via cPanel > MySQL Databases.

-Luke.

 

[woiwky]

Thanks, tnl2k7, but that wasn't what I was confused about. I was confused with what he was referring to by 'root' in the context of our discussion. Since in my post before his, I didn't mention a root user, only that the user he creates needs to have proper access. But regardless, I was never too concerned with what he meant by that anyway

 

[javajenius]

Ok I think i understand now, as long as a user has privileges to access the database, you can access all of the database tables with one user.


Lets say my account with privileges to access the database had the username: admin and password: password.

So to verify a login from the website, i would login with admin and password and check their credentials on the MySQL database?:dunno:


So every time a login was attempted i would have to connect with admin/password right?

 

[cowctcat]

The MySQL root user has access to all the databases and has all privileges. You can't use it at x10Hosting, all users have to be assigned to databases via cPanel > MySQL Databases.

-Luke.

The x10 equivilent of your root user is a user with your cpanel name and password.

 

[woiwky]

Ok I think i understand now, as long as a user has privileges to access the database, you can access all of the database tables with one user.


Lets say my account with privileges to access the database had the username: admin and password: password.

So to verify a login from the website, i would login with admin and password and check their credentials on the MySQL database?:dunno:


So every time a login was attempted i would have to connect with admin/password right?

You don't have to do anything yourself other than write the code. PHP can connect to the db and edit/retrieve data on the spot once you have created a script to do that.

So if you wanted to make a processing script for user login requests, and store the user's id in their session data to identify them as logged in, your code may look like this (assuming the admin/password mysql user, and that client user data is stored in a table named 'users' within a db called 'user_db'):

<?php
//Initialize the session
session_start();

//Only execute if the user isn't logged in and has sent a username/password
if (!isset($_SESSION['user_id']) && isset($_POST['username'], $_POST['password'])) {
    //Connect to MySQL
    mysql_connect('localhost', 'admin', 'password') or die('Unable to connect to MySQL');
    //Select the db where user data is kept
    mysql_select_db('user_db') or die ('Unable to select database');
    //Sanitize post data
    $username = mysql_real_escape_string($_POST['username']);
    $password = mysql_real_escape_string($_POST['password']);
    
    //Query the 'users' table for a matching id to the username/password
    $sql = "SELECT id
        FROM users
        WHERE username = '$username'
        AND password = '$password'";
    $res = mysql_query($sql);
    
    //If no user is found, the login is invalid
    if (!mysql_num_rows($res)) {
        die('Invalid username/password combination');
    }
    //Else get the id from the query result and add it to the user's session
    else {
        $id = mysql_fetch_assoc($res);
        $_SESSION['user_id'] = $id['id'];
    }
}

//Redirect to the index page
header('Location: index.php');
?>

Additionally, you may find this tutorial useful:

http://www.tizag.com/mysqlTutorial/mysqlconnection.php

 

[javajenius]

Ok I think I understand, thank you everyone for your help.