cancel
Showing results for 
Search instead for 
Did you mean: 

Multi-user PHP MySQL database environment

SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Multi-user PHP MySQL database environment

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
11 REPLIES 11
pjmarsh
Superuser
Superuser
Posts: 4,030
Thanks: 1,575
Fixes: 20
Registered: ‎06-04-2007

Re: Multi-user PHP MySQL database environment

You need
session_start() ;
on each page for php to know it is using sessions.
Phil

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.

pjmarsh
Superuser
Superuser
Posts: 4,030
Thanks: 1,575
Fixes: 20
Registered: ‎06-04-2007

Re: Multi-user PHP MySQL database environment

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

Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.

SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Multi-user PHP MySQL database environment

Thanks very much phil Smiley
I've struggled for ages with that and yet it was so simple!
SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Multi-user PHP MySQL database environment

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? Huh

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

Thanks
Ben_Brown
Grafter
Posts: 2,839
Registered: ‎13-06-2007

Re: Multi-user PHP MySQL database environment

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
Peter_Vaughan
Grafter
Posts: 14,469
Registered: ‎30-07-2007

Re: Multi-user PHP MySQL database environment

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.
Ben_Brown
Grafter
Posts: 2,839
Registered: ‎13-06-2007

Re: Multi-user PHP MySQL database environment

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.
Colin
Grafter
Posts: 1,264
Registered: ‎04-04-2007

Re: Multi-user PHP MySQL database environment

Hmm, does that php.ini trick work then?
[me=Colin]wonders why he's never known that before now...[/me]
SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Multi-user PHP MySQL database environment

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?
Ben_Brown
Grafter
Posts: 2,839
Registered: ‎13-06-2007

Re: Multi-user PHP MySQL database environment

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
Colin
Grafter
Posts: 1,264
Registered: ‎04-04-2007

Re: Multi-user PHP MySQL database environment

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 Sad