User validation. is this optimized?

[asantos]

Hi, i use this code to validate the already signed in user. What i am not sure about is the sql statement:

$sql = 'SELECT id_user FROM user WHERE id_user='.q($this->id).' AND user = '.q($this->user,true).' AND code = '.q($this->code,true).' AND ip = '.sprintf('%u',ip2long($_SERVER['REMOTE_ADDR'])).' AND flg_access = 1';

The field id_user is the only INDEX field in the table.

Right now the service has around 400 users... and that works ok for that. But what will happen when i have 1 millon users when executing that sql statement?

Here is the full code:

function validation() {
global $cnn;
if(is_numeric($this->id) && $this->user && $this->code) {
$sql = 'SELECT id_user FROM user WHERE id_user='.q($this->id).' AND user = '.q($this->user,true).' AND code = '.q($this->code,true).' AND ip = '.sprintf('%u',ip2long($_SERVER['REMOTE_ADDR'])).' AND flg_access = 1';
$rs = &$cnn->Execute($sql);
if($rs->EOF) {
header('Location: '.URL.'logout?msg=105');
die();
} else {
# Session valid
return true;
}
} else {
header('Location: '.URL.'logout');
die();
}
}

[studawsons]

Do you expect to have 1 million users anytime soon?

Try not to over complicate things, i reckon if this solution works well for now then keep it, optimize it gradually if you feel the need but never ever over complicate or over compensate for situations that may not even happen P)

KISS remember

[whoisgregg]

I'd put indexes on `user`, `code`, `ip`, and `flg_access` and that query should go blazingly fast.

[asantos]

thanks for the tips!

[restless]

Do you run this query everytime a visitor goes to another page in your members area? This will be a waste of mysql resources, you should learn about Sessions it is a far better solution for what you want. Basically once you validate the user upon login you can store their information in Session, then everytime a visitor goes to another page you can just check your Session information instead of querying the DB.

[coopster]

Unless you are using your database to manage the sessions.

And if you aren't, you may still have an issue with that method -- what if you suspend a user's account and that user is in the middle of a session? Without hitting the database again, you have just lost control over that session and the suspended user keeps on navigating the protected area.

As far as performance goes, the database interaction is going to be extremely fast. With a session you are still opening a file, it's just at the filesystem level as opposed to the database level (unless of course you are using the database to manage sessions as mentioned earlier).

Just some considerations ....

[pixeltierra]

I've always used $_SESSION for user sessions. What are the advantages of using a database-based method?

[coopster]

There may not be. Each project will differ. For a more detailed answer, you can take in a free webinar over at MySQL:

MySQL AB On-Demand Web Seminars [mysql.com]

Specifically, Session Management with MySQL [mysql.com]

[henry0]

Coopster, thanks for that link; that's a great place to visit ......

instead of counting sheeps :)

Bookmarked!

<<<edit
I have a doubt about my humor,
you know that I am kidding don't you
>>>>>

[coopster]

I admit, some webinars can be dry but I took that one in last summer and did have a few good takeaways so thought I would mention it in this thread.

hehe, yes, I know you are kidding around, Henry. Keep it coming, I can take it ;)

[asantos]

good consideration. right now i am accessing the DB on each page request on a member area. i think ill have to switch it to sessions.

[coopster]

I think you will find Session Handling [php.net] very useful to manage navigation and access. Hitting the database each time is not a big issue, that was my point in my first message. As a matter of fact, it has benefits, particularly when it comes to ending a user session for one reason or another.

[asantos]

exactly. in this case, i save the session_id() on a field in the user table. that way, on each page request on a members only area, i can check if the field in the DB is equal to session_id().

If it is not equal, then we know there's a problem. (perhaps the user was deleted, or code was set to NULL becasuse someone accessed with the same username from another machine).