Multi-user PHP MySQL database environment

[#1]

I Need help!
Previously i have used .htaccess to contol access to a protected part of my site but .htaccess and .htpasswd does not protect .php files so I'm going to go down the route of adding a users table to mysql database.
Scenario:
An authorised users wants to login to write a news article. He enters his username and password and logs in.
If sucessful (username and password matches an entry in the tbl_users table) then store his realname and jobtitle etc in global variables like $_SESSION['realname'] etc so that it can be used later.
Problem:
On page2.php, <?php echo($_SESSION['realname']);?> does not output the users name as expected.
I have previously written a similar system in .asp with MS Access at the back end and no problem at all. I can't apply the same in PHP for some reason.
I have included the main part of the code to help make my point

<?php
	function opendb()
	{
		$db_host='XXXXX';
		$username='XXXXXXXX';
		$password='XXXXXXXXX';
		$db_name='XXXXXXXXXXXXXX';
		
		$chan=mysql_connect($db_host,$username,$password);
		mysql_select_db($db_name,$chan);
		
		return $chan;
	}
	
	if(isset($action))
	{
		if($action=="login")
		{
			if($name == "")
			{
				$noname = "<p class='redalert'>User Name not entered!";
				$action = "fail";
			}
			if($pwd == "")
			{
				$nopwd = "<p class='redalert'>Password not entered!";
				$action = "fail";
			}
	
			if($action=="login")//UserName and Password contain values so check against the DB
			{
				//connect to the database
					
				$chan=opendb();
				
				if(!$chan)
				{
					echo "Could not connect to database";
					exit();
				}
				$sql = "SELECT * FROM tbl_users WHERE logon_name='$name' AND pwd='$pwd'";
				$result = mysql_query($sql,$chan);
				
				if(!result)
				{
					$badnameorpwd = "<p class='redalert'>Login credentials are not valid!";
					$action = "fail";
				}else{
					//successful login, get user info
					$row = mysql_fetch_assoc($result);
					
					if($name == $row['logon_name'] and $pwd == $row['pwd'])
					{
						session_start();
						
						$_SESSION = array();
						
						$_SESSION['username'] = $row['user_name'];
						$_SESSION['password'] = $pwd;
						$_SESSION['jobtitle'] = $row['job_title'];
						header("Refresh: 0; url=protected/index.html");
						exit();
					}else{
						$badnameorpwd = "<p class='redalert'>Login credentials are not valid!";
						$action = "fail";
					}
				}
			}
		}
	}
?>

from this point on it's simple html with a form containing username and password fields so the page first loads asking the user to logon and then is reloaded to validate the login (?action=login)

	<form method="POST" action="?action=login">
		<table border="0" width="800" id="table1" style="border-collapse: collapse">
			<tr>
				<td width="180" height="24" valign="bottom"><b>User Name</b></td>
				<td>
					<p><input type="text" name="name" size="20" value="<?php echo($name);?>"></p>
				</td>
				<td><?php echo($noname);?></td>
			</tr>
			<tr>
				<td width="180" height="24" valign="bottom"><b>Password</b></td>
				<td><input type="password" name="pwd" size="20"></td>
				<td><?php echo($nopwd);?></td>
			</tr>
			<tr>
				<td width="180"><?php echo($badnameorpwd);?></td>
				<td>&nbsp;</td>
				<td>&nbsp;</td>
			</tr>
			<tr>
				<td width="180">&nbsp;</td>
				<td><input type="submit" value="Submit" name="B1"><input type="reset" value="Cancel" name="B2"></td>
				<td>&nbsp;</td>
			</tr>
		</table>
	</form>

Any help would be much appreciated

[#2]

You need

session_start() ;

on each page for php to know it is using sessions.
Phil

[#3]

Should have said that needs to be at the top of each script.
Phil

[#4]

Thanks very much phil
I've struggled for ages with that and yet it was so simple!

[#5]

Based on the code you have seen above, how secure is the method i am using?
If i include a validation check similar to the one shown here on each page that is protected, is it secure?

<?php
	if($_SESSION['username']=='')
	{
		echo("<p class='redalert'>Unauthorized Access! Protected area");
		exit();
	}
?>

Thanks

[#6]

Assuming you are using register_globals to get the values of $name and $pwd in your SQL, you have an SQL injection vulnerability there.
Our ccgi servers use register_globals by default, so it is likeley this is the case.
In your code you have:

  $sql = "SELECT * FROM tbl_users WHERE logon_name='$name' AND pwd='$pwd'";
  $result = mysql_query($sql,$chan);

Without any sanitiation of these values, arbirary SQL can be inserted into the statement, for example if I knew a username (let's say 'admin' for argument's sake), I could put that in the username box and put the following in the password box:

' OR 1='1

When substituted, your query would look like this:

  SELECT * FROM tbl_users WHERE logon_name='admin' AND pwd='' OR 1='1'

As 1='1' will be true, $result will contain all the information for that user. A cracker would only need to guess usernames to gain access to your site.
Personally I would never use "SELECT *" as it's lazy and can really confuse your scripts if you ever need to add or remove a coloumn from the table in question.
Also, your script relys on having the password in plain text in your table, which is not desirable, it's best to hash it using PHP's "crypt" function or similar.
HTH

[#7]

You should not rely on register_globals to set your variables as it is both insecure and depreciated.
You should use $_POST[] or $_GET[] to get the info passed via a GET or POST.
While SELECT * may be lazy, it does have its uses as adding additional fields does not mean the select query needs to changed, only the code to use the additional fields.
To stop the SQL injection just don't allow (and thus specifically strip out) any single quotes in usernames and passwords.

[#8]

Quote from: Peter

To stop the SQL injection just don't allow (and thus specifically strip out) any single quotes in usernames and passwords.

I meant to add, addslashes() will fix the SQL injection. I always use addslashes() on strings, and intval when I expect an integer.
Regarding register_globals, it is indeed depricated and in my opinion should be turned off (it has been off my default on PHP for a long time).
The only reason it is on our current ccgi platform is because there were far too many customer scripts relying on it for us to turn it off for everyone without a huge upset. You can turn it off for your scripts (recommended) by adding the following to a file called php.ini in your ccgi home directory:

register_globals = off

As Peter says, you then need to use the superglobals $_POST and $_GET to get the information.

[#9]

Hmm, does that php.ini trick work then?
[me=Colin]wonders why he's never known that before now...[/me]

[#10]

Thanks to you guys for this info.
I'm a VB programmer normally and PHP is new to me so there are lots of functions i don't know about yet. I will look up 'crypt' and addslashes(), they sound useful.
I've still got a lot to learn yet and some of the things you have said are not clear to me yet but these post will definately help me. Thank you.
ie. I'm not intentionally using register_globals. $name and $pwd are the 'names' of the UserName and Password textboxes on the login.php form and the method is 'POST'
After a successful login, the session variables $_SESSION['username'] etc carry the info around the protected area.
Have I misunderstood the concept?
What else can i do with the php.ini file and where does it go?

[#11]

The php.ini file needs to be in the same directory as the script you are running, so you may need multiple copies (or symlinks) of it depending on the layout of your cgi space, and yes Colin it really does work!
There's a lot of things you can overwrite there, none of which I can remember off the top of my head unfortunately (I don't do PHP professionally, it's just a hobby for me).
Once register_globals is turned off you wont be able to refer to the fields in the form directly, you need to use the superglobals I meantioned before.
As you are using the POST method, they will all be in $_POST, e.g. what is put in the "name" field will end up in $_POST['name'].
You'll still need to do some sanitation of the data, the simplest way would be along these lines:

$name = addslashes($_POST['name']);

HTH

[#12]

The full list of options is at http://uk.php.net/manual/en/ini.php along with where you can set it.

Quote from: Ben

The php.ini file needs to be in the same directory as the script you are running, so you may need multiple copies (or symlinks) of it depending on the layout of your cgi space, and yes Colin it really does work!

Wish I'd known about that earlier