Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple security scheme for MySQL info portal 1

Status
Not open for further replies.
GKChesterton

GKChesterton

Programmer
Aug 17, 2006
278
US
I'm enabling a knowledge base for spare parts. I need a simple logon/off scheme. No commerce, no high-level security needs. I will have a few users, a stable bunch. MySQL holds the data (converting from MS Access).

I'm new to PHP; getting pretty good at running HTML forms through and using them to query records, but session variables are beyond me so far. My ISP seems to provide mysql not mysqli.

I have a table user_list with three fields: an auto-incr primary, name_user and pass-user. That seems like a decent way to store my users. I should be able to use cookies.

Can anyone recommend a scheme and point me to some scripts? And can I learn about session handling along the way?



[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Sort by date Sort by votes
here is a very simple script. to use it just store it in a file called logon.php. at the top of each page you want to protect include the following code:
Code: 
<?php require_once "logon.php"; ?>

insert your sql connection data where the relevant comment is in processLogon.

note that it would be better to encrypt or encode your user's passwords but that is a different discussion.

Code: 
<?php
if (!loggedOn()){
	showLoginBox();
}

function loggedOn(){
	//start the session
	if (session_name() === ""){
		session_start();
	}
	
	//test for logged on
	if (isset($_SESSION['loggedOn'])){
		return true;
	} else {
		return false;
	}
}

function showLoginBox($msg=""){
	if (isset($_POST['login']['submit']) && $msg=="" ){
		processLogon();
		exit();
	} else {
	if (!empty($msg)){
		$msg = "<div class=\"errorMessage\">$msg</div>\r\n";
	} else {
		$msg = NULL;
	}
	echo <<<HTML
$msg	
<form method="post" action="{$_SERVER['PHP_SELF']}">
<fieldset>
	<legend>Logon</legend>
	User Name: <input type="text" name="login[username]"/><br/>
	Password: <input type="password" name="login[password]"  /><br/>
	<input type="submit" name="login[submit]" value="Login" />
</fieldset>
</form>
HTML;
	exit();
	}
}

function processLogon(){
	//validation routines
	$msg="";
	if (!empty($_POST['login']['username']) && !empty ($_POST['login']['password'])){
		$username = trim($_POST['login']['username']);
		$password = trim($_POST['login']['password']);
	} else{
		showLoginBox("You have not completed the form properly");
	}
	
	//form is valid.
	//now test the password
	
	//MYSQL CONNECT AND SELECT GOES HERE
	
	$result = mysql_query("
				Select 
					count(*) 
				from user_list 
				where 
					name_user='".mysql_escape_string($username)."' 
					and 
					pass-user = '".mysql_escape_string($password) ."'");
					
	if (mysql_result($result, 0,0) !== 1){
		showloginBox("No such username-password combo");
	} else {
		$_SESSION['loggedOn'] = true;
	}
}
function logOff(){
	unset($_SESSION['loggedOn']);
	showLoginBox();
}
?>
 
Upvote 0 Downvote
Gee, you even filled in my field names, thanks. However, I'm getting "no user-password combination," which is mystifying: I've tweaked your code:
Code: 
    //MYSQL CONNECT AND SELECT GOES HERE
    make_connection ()   //from an included file
	    or exit ();
	    
    $result = mysql_query("
                Select
                    count(*)
                from user_list"); [COLOR=red]omit where.[/color]
                       [COLOR=red]This table has 1 record only.[/color]
                    
    if (mysql_result($result, 0,0) !== 1){
        showloginBox([COLOR=red]mysql_result($result, 0,0))[/color];
    } else {
        $_SESSION['loggedOn'] = true;
    }

My result is:

Code: 
<div class="errorMessage">[COLOR=red][b]1[/b][/color]</div>
    
<form method="post" action="/te-st/logon.php">
<fieldset>
    <legend>Logon</legend>
    User Name: <input type="text" name="login[username]"/><br/>
    Password: <input type="password" name="login[password]"  /><br/>
    <input type="submit" name="login[submit]" value="Login" />
</fieldset>

</form><br />
So looks to me like my return is 1 and the if statement should take me to logged. I wonder why it doesn't.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
i'm surprised. i wonder whether the count(*) is coming through as something other than an integer.

could you try casting the result to an integer?

Code: 
$num = (int) mysql_result($result, 0,0) !== 1){
    if ($num !== 1){
     showloginBox(mysql_result($result, 0,0));
    } else {
        $_SESSION['loggedOn'] = true;
    }

you might try qualifying the query with an error handler just in case there is something wrong with the column names
Code: 
mysql_query( ...) or die(mysql_error());
 
Upvote 0 Downvote
A star and a big wet kiss for jpadie. It even works from the ISP.

Several changes were required. First, casting as integer did solve the problem with the == statement. (I was trying to think of that ... but could only think of CInt() from VBA and needed the help.)

Second, there was a logical error:
Code: 
function showLoginBox($msg=""){
    if (isset($_POST['login']['submit']) && $msg=="" ){
        processLogon();
        [s][COLOR=red]exit();[/color][/s]
    } else {
    if (!empty($msg)){
        $msg = "<div class=\"errorMessage\">$msg</div>\r\n";
    } else {
        $msg = NULL;
    }
    echo <<<HTML $msg

Until I took out the exit(), I couldn't get to my logged-in result.

Finally, my editor is picky about mysql_free_result($result), so I added that.

Thanks for great portable code, and a great model for clean text arrangement of the code. Now ... on to more about session handling ...

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
well spotted on that error. I cut this code down from a full-featured class that i have been working on for a while.

as for mysql_free_result - the result set is implicitly freed once the script ends. methinks your editor is overly picky.
 
Upvote 0 Downvote
Argh! Doesn't really work! I was fooled because the initial page responds to the form input ... but the session variable does not actually show up and do its part on the next page!

My laboratory consists of two .php pages. For both of these, the logon form awaits my entries:

sessionA.php:
Code: 
<?php require_once "logon.php"; ?>
<html><head><title>Session A</title>
</head><body>
This is Session A!
<a href=sessionB.php>Now go to Session B.</a>
</body>


sessionB.php:
Code: 
<?php require_once "logon.php"; ?>
<html><head><title>Session B</title>
</head><body>
This is Session B!
<a href=sessionA.php>Return to Session A.</a>
</body>

Now here is the current version of logon.php:
Code: 
[small]
<?php
include ("data.inc");
if (!loggedOn()){
    showLoginBox();
}

function loggedOn(){
    //start the session
    if (session_name() === ""){
        session_start();
    }
    
    //test for logged on
    if (isset($_SESSION['loggedOn'])){
        echo "The SESSION variable IS set !!! <br>";
        return true;
        //exit();
    } else {
        echo "The SESSION variable is not set !!! <br>";
        return false;
        
    }
}

function showLoginBox($msg=""){
    if (isset($_POST['login']['submit']) && $msg=="" ){
        processLogon();
     //   exit();
    } else {
    if (!empty($msg)){
        $msg = "<div class=\"errorMessage\">$msg</div>\r\n";
    } else {
        $msg = NULL;
    }
    echo <<<HTML
$msg    
<form method="post" action="{$_SERVER['PHP_SELF']}">
<fieldset>
    <legend>Logon</legend><br/>
    User Name: <input type="text" name="login[username]"/><br/><br/>
    Password: <input type="password" name="login[password]"  /><br/><br/><br/>
    <input type="submit" name="login[submit]" value="Login" />
</fieldset>
</form>
HTML;
    exit();
    }
}

function processLogon(){
    //validation routines
    $msg="";
    if (!empty($_POST['login']['username']) && !empty ($_POST['login']['password'])){
        $username = trim($_POST['login']['username']);
        $password = trim($_POST['login']['password']);
    } else{
        showLoginBox("You have not completed the form properly");
    }
    
    //form is valid.  now test the password
    
    //MYSQL CONNECT AND SELECT GOES HERE
make_connection ()   //function from data.inc
	or exit ();
	    
    $result = mysql_query("
                Select
                    count(*)
                from user_list
                where
                    name_user='".mysql_real_escape_string($username)."'
                    and
                    pass_user = '".mysql_real_escape_string($password) ."'");                  
    $num = (int) mysql_result($result, 0,0);
    mysql_free_result($result);  //my picky editor (PHP Designer 2007)
    if ($num !== 1){
        showloginBox("No such username-password combo");
    } else {
        $_SESSION['loggedOn'] = true;
    }
    
}
function logOff(){
    unset($_SESSION['loggedOn']);
    showLoginBox();
}
?>
[/small]

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
let's check out whether sessions are working.

please run this code

Code: 
<?php
session_start();
if (isset($_SESSION['test'])){
 echo "sessions work.";
} else {
 $_SESSION['test'] = "some value";
 session_write_close();
}
echo "<br/>click <a href=\"".$_SERVER['PHP_SELF']."\">here</a>";
?>
 
Upvote 0 Downvote
sessions work.
click here
Click to expand...

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
Clearly yes, but apparently not when invoked from another page, per my post of 15:00.

sessionA.php
and
sessionB.php
still indicate the session info is not retained.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
try this
Code: 
<?php
session_start();
$_SESSION['test'] = "hello world";
session_write_close();
echo "<br/>click <a href=\"sessionB.php\">here</a>";
?>

Code: 
<?php
session_start();
echo "<pre>".print_r($_SESSION, true);
?>
 
Upvote 0 Downvote
Looking good ...
Array
(
[test] => hello world
)
Click to expand...

By the way, I did try inserting a session_write_close(); in the logon.php script, but it didn't make a difference.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
the session_write_close just avoids a race condition where you might be trying to open the session file before the last page has written to it and closed it down. this typically only happens when you are testing on a local platform.

so what is happening with your example above?

i'd expect it to:

1. show the logon form
2. on reload (with correct credentials) display the link to sessionB.php
3. on clicking the link return to sessionA.php
 
Upvote 0 Downvote
Yes. I'm sorry to frustrate. That's why my 15:00 post had [highlight #FF99FF]all[/highlight] the code.

None of the emoticons really express my consternation.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
i think this is just an html problem.

try this. just has the login code put inside the <body> tag in the second file.

Code: 
<?php require_once "logon.php"; ?>
<html><head><title>Session A</title>
</head><body>
This is Session A!
<a href=sessionB.php>Now go to Session B.</a>
</body>



Code: 
<html><head><title>Session B</title>
</head><body>
<?php require_once "logon.php"; ?>
This is Session B!
<a href=sessionA.php>Return to Session A.</a>
</body>
 
Upvote 0 Downvote
No, I just go back and forth entering the name and password again and again. I also ran these two docs onto the ISP host, and they acted the same way there.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
let's make sure that the errors are being properly reported

can you make sure that the error display code is at the top of the page

Code: 
ini_set("display_errors". "on");
error_reporting(E_ALL);
 
Upvote 0 Downvote
A HA! These ARE all the same topic! Okay, I'll give it a try after supper and a nap. Oh, and I got cookies going.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
I solved a bunch of problems by changing ISPs. I changed security approach to HTTP authentication with protected directories. Thanks for your help and sorry for neglecting this thread; my time pressures and fatigue took me out for while. Your troubleshooting was helpful in provoking me to get a new ISP.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Locked
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
435
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
584
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
384
spamjim
spamjim
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
614
EdLentz
EdLentz
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
669
ersatz
ersatz

Part and Inventory Search

Sponsor

Back
Top