PHP and MS SQL Secure Authentication system

[oneillj]

Hi,

I need to create a PHP script which can read an MS SQL database in order to authenticate users onto my website. Once they're authenticated, they get sent to their own personal log in page which allows them access to certain areas of the website. This needs to be dependant on whether or not they're in a specific user group, for instance, certain types of users get more access, basically just get sent to a different page.

Is this possible? I'm using an ODBC connection. All the serve side stuff to have the PHP and MS SQL server "talk" to each other has been done. I'm not very sure of the code as I've never done this before.

I apologise if I've posted in the wrong part of the forum, or if no one knows what I'm talking about.

Thanks in advance
Janet

[phper]

There are many ways you could do this. The simplest way could possibly be for you to use sessions.

http://uk3.php.net/manual/en/ref.session.php

If you follow that link it gives you some information on what session are and how to use them.

An example of how you could implement it would be:

<?php
session_start();

// DATABASE CONNECTION & QUERY
// IF THE USER IS AUTHENTICATED THEN SET A SESSION
$_SESSION['authenticated_user'] = true;

?>

Whenever a user accesses a page you just need to run this code:

<?php
session_start();

if($_SESSION['authenticated_user']!=true){
header('Location: login.php');
die();
}

?>

If the user has been authenticated then they won't get re-directed.

Write back if you need further help.

If you've found this post useful please add to my reputation!

[oneillj]

Thanks for that! I'm still very confused, I'm just not sure where to start as I've never done this before. I've ran a user authentication script just with php with basic username and passwords, but I just need something a bit better which stores a lot more user details, obviously this is where the MS SQL ties in. All the articles I find are always to do with MySQL and full scripts are provided for that, but I can never find any articles for MS SQL so I'm really stuck.

This was a great help as a start though, but if anyone else can give me a bit more advice, possibly show me some scripts and give me more info that would be fantastic.

Thanks in advance everyone, and phper, you have been a great help so far!

[phper]

If you need to store more information in the session you can.

All you need to do is define another $_SESSION variable. For example if you wanted to store the person's username you would write:

$_SESSION['username'];

You can store almost anything you want.

It's probably going to be better for you in the long run for you to write your own script. That's how I learnt - although there's no harm in finding other scripts that do what you want and looking at how they've been written.

[oneillj]

I'm still confused as to how I would write this alongside HTML script that actually has the username and password box for people to log in... or can I not do it like this?

Sorry, I'm a complete noob to php and sql.

[phper]

Ok - no problem.

Say you have your login page called login.php - this page will contain a form with the username & password fields along with a button that send the users to page called logincheck.php

On the logincheck.php page you would have something like the following:

<?php

session_start();

// DATABASE CONNECTION STUFF GOES HERE

$username = $_REQUEST['username'];
$password  = $_REQUEST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password='$password'";
$result = mssql_query($query);
$num = mssql_num_rows($result);

if($num==1){
// IF THE NUMBER OF ROWS IS EQUAL TO ONE THEN THE USERNAME AND PASSWORD // ENTERED ARE PRESENT IN THE DATABASE SO THE USER IS VALID.
// AS IT'S A VALID USER SET THE SESSION VARIABLES TO SHOW THEY ARE VALID

$_SESSION['valid_user'] = true;
$_SESSION['username'] = $username;

header('Location: index.php'); // PUT WHATEVER PAGE YOU WANT TO MOVE THE USER TO ONCE LOGGED IN
die(); // KILL THE SCRIPT SO IT DOESN'T GO ANY FURTHER

}else{
// THE USER OR PASSWORD IS NOT VALID SO SEND THEM BACK TO THE login.php PAGE

header('Location: login.php');
die();

}

On each page of your secure area you need to have the following:

<?php
session_start();

if($_SESSION['valid_user']!=true){
// IF THE USER IS NOT A VALID USER AND THEY TRY TO ACCESS A PAGE IN THE //SECURE AREA THEY GET SENT TO login.php
header('Location: login.php');
die();
}

?>

All you need to do with this script is add the connection details.

Any problems let me know!!

Regards
Alex.

[alias120]

I am having issues with this, I have a login.php file along with a login_success.php file that is directed once the username and password are verified out of MS SQL. I think my syntax might be wrong on the connection information, anyone mind taking a look?

$server = 'WIN-SOY1OB4EEEX\';

$link = mssql_connect($server, 'WIN-SOY1OB4EEEX\Administrator', '');

if(!$link)
{
    die('Something went wrong while connecting to MSSQL');
}


mssql_select_db('MyDatabase', $link);

[gromikov]

Why dont you try useing the ip address of the server instead of the ID (server name).
Also try useing your user id ... for instance "Admin" and not the whole session line as in your PHP fragment.

$link = mssql_connect($server, 'userid', 'password');

Good luck!
Visit Aztek Source Code
I am having issues with this, I have a login.php file along with a login_success.php file that is directed once the username and password are verified out of MS SQL. I think my syntax might be wrong on the connection information, anyone mind taking a look?

$server = 'WIN-SOY1OB4EEEX\';

$link = mssql_connect($server, 'WIN-SOY1OB4EEEX\Administrator', '');

if(!$link)
{
    die('Something went wrong while connecting to MSSQL');
}


mssql_select_db('MyDatabase', $link);