homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

PHP/mySql individual user records
Users accessing their own records only

 7:35 pm on Oct 30, 2002 (gmt 0)

I wish to set up a db so that a user can log on and access only their own records. These records will then be sent to various handler scripts to be manipulated. Within the db there will be up to 30 users.

I have written a php user log on script which allows access to a home page for registered users whose user names/passwords are stored in the db. What I can't work out at the moment is a way to enable a script to only go to the users records, ie. the user that has logged on. Do I need to use sessions and how do I go between the user log on and then a unique db query?



 4:39 am on Oct 31, 2002 (gmt 0)


Sticky me some code, and I'll take a look.


 9:00 am on Oct 31, 2002 (gmt 0)

danec, thanks but what I really need is the general concept and then I think I will be ok. I'm sure this is possible I just have a mental block on the next step.


 1:41 pm on Oct 31, 2002 (gmt 0)

You have a table of users already, right..? So jus why not just have a column for the user id as foreign key to your users tables. Then tack on a simple join.


 1:50 pm on Oct 31, 2002 (gmt 0)

Your initial query to the db to verify the username and pwd is valid is the trigger. I'm not sure what language you're using but in PHP you can return the ID of the last query - meaning that if the last query (the check for a valid user) was true, then you can query the db for the same ID and get the rest of the user's information.

At this point, I stuff their uname into a session var and check for it on every secure page. If it's not there, send the visitor to the login page.


 3:43 pm on Oct 31, 2002 (gmt 0)

Josk, thank you but I don't think that mySql supports foreign keys although I see what you are getting at.

Lorax, thanks. I think I'm with you. I'm using php so if I return the query from the log-in script they must be a validated user name. If I start a session on each page this will check each time for the same user - is this correct?

How do I return the id of the last query? I'm not sure of the function to achieve that - much page turning going on!


 5:18 pm on Oct 31, 2002 (gmt 0)

I don't think that mySql supports foreign keys although I see what you are getting at.

Reminds me why I decided to switch to Postgres. MySQL is getting more feature-complete, but that's one I'd have a hard time designing a good data model without. You can do the same thing without telling the databse that the columns are foreign keys, but it's nice to have the referential integrity enforced by the database.


 6:02 pm on Oct 31, 2002 (gmt 0)

I'm sorry but I mislead you. The function for returning the ID is associated with an INSERT only - mysql_insert_id(). But - you can still perform a quick validation of uname & pwd.

You can use 'select count(*) to verify they are valid:

$login_query = "Select count(*)
From admin
Where uname = '".$log_user."'
and pwd = '".$pwd."'";
$login = mysql_query($login_query);

$count = mysql_result($login, 0, 0);

if ($count > 0) {
$login_query = "Select *
From admin
Where uname = '".$log_user."'
and pwd = '".$pwd."'";
$login = mysql_query($login_query);
$user_details = mysql_fetch_array($login);

$log_user = $user_details["uname"];
$user_name = $user_details["name"];

header("Location: admin/admin_home.php");

As for the checking part - at the very top of each page you need to begin with session_start() which will automatically refresh all session variables for use and immediately follow it with you check code.


 6:08 pm on Oct 31, 2002 (gmt 0)

lists all of the mysql functions.

Can you do that just for a select lorax? I know there is mysql_insert_id but that is for last inserted. At any rate you can just
select * from db where username="user" and password="pass" // where user and pass come from login form

if they get no data then they aren't in there and if they do then you have the id already.

Yes, no foreign keys is a pain but I still use them. You have to use them logically as opposed to physically but it works just as well.

There are a bunch of ways to store the user data cyclic. You could have directories on the site that are user specific. If they must be in the db then you could have seperate tables that have the username as the tablename. You could lump it all in one and use the user_id as the glue to associate everything together.

just a few thoughts


 6:12 pm on Oct 31, 2002 (gmt 0)

Re: ID - No, I was mistaken - see above.


 6:15 pm on Oct 31, 2002 (gmt 0)

I saw it but we were posting at the same time. ;)


 6:21 pm on Oct 31, 2002 (gmt 0)

select * from db where username="user" and password="pass" // where user and pass come from login form

Rather than storing the password in the db, I generally store a cryptographic hash (md5 or the like) of it. That way, in the event that I screw up and there is some way to get my web site code to divulge data from the table with user data in it an attacker would still have to mount a dictionary attack on the passwords they found.

That doesn't do anything about sniffing, of course, and you'd have to either use https (what I do) or have some client-side scripting involved in the authentication (I've thought about it, and as soon as I have a text-mode browser with that much Ecmascript support I'll start doing it.) to protect against that. But it's one more obstacle to a cracker, and n easy one to implement. It's pretty much 2 calls to the md5() function in your scripting language. One on insert/update of passwords, and one on authentication of a password.


 6:33 pm on Oct 31, 2002 (gmt 0)

Good point dingman - I don't bother with encryption on certain clients because they're just too small a fish to be a target. Besides there's simply nothing there worth getting at except maybe to change the content around.

But now that you mentioned it - how much of a performance hit do you take when implementing encryption?


 6:34 pm on Oct 31, 2002 (gmt 0)

Sorry about that, I do the same. I was using a very simplistic example. It also depends, sometimes I just leave them in there as is because I really don't care if someone gets in.

Most often though I use md5().


 6:57 pm on Oct 31, 2002 (gmt 0)

Assessing how much damage someone could do to you and how likely they are to try does make a lot of sense. Heroic security measures aren't worth it for a lot of sites. I use password hashes all the time because I find it so easy to implement that there's no point in *not* doing it.

SSL I use less often, for two reasons. First, it's a bit of a hassle to set it up. Second, for most of the stuff I've done so far the cost of a certificate is prohibitive. I roll my own regularly, but browsers tend to complain about that, and your average user doesn't know enough about the operation of SSL to know what the warning means. I think users are in most cases going to feel better about an insecure site that doesn't trigger a warning from their browser than a secure one that does.

As for overhead in encryption, I really don't know how it stacks up. I tend to write *very* light-weight pages, and have a small enough user community right now that it hasn't been an issue. My guess would be that it works best if you have sticky visitors and use persistent connections, so as to avoid repeating the connection overhead, which is significantly higher for an encrypted connection than an unencrypted one. That's just a guess, though.


 9:35 pm on Oct 31, 2002 (gmt 0)

Thanks one and all. I have just solved the problem using the user name as a common link and sessions on each page to make sure that the user remains consistent.

The people I am doing this for want the site hosted on SSL. This will be a first for me, does this give any particular problems and is it worth it? The data is sensitive but not not in monetary terms. I really don't think that ssl is justified but they have seen the "padlock" and think that is the answer to everything!


 9:53 pm on Oct 31, 2002 (gmt 0)

My bias is always toward the more paranoid side of reasonable security precautions. If they want to use SSL, it's relatively easy to set up and the cert probably costs less than a few hours of your time.

If security was highly important, there would of course be other things to worry about and the problem would be getting across to the client that the padlock has a very narrow meaning. If it'll make them happy, though, I don't see any harm in letting them have the padlock.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved