Is it okay to make a connection to the database in a session?

[hanaa]

Hello,

I am making an interface to a database(MySQL) of questions. Is it possible to connect to the database using mysql_connect() just once, in a session and use the connection thereafter in the following pages and until the user logs out? If it is, Is it okay to do so.. I just read that the session files are stored in some directory on the computer. I wanted to know if its okay and wise to connect this way, given connecting requires the password to be given.

Thank you :)
Hanaa.

[Dormilich]

this will probably fail, since any mysql_connect() connection is closed at script end. you can use mysql_pconnect( ), but what's wrong with connecting to MySQL each time when the next page is processed (build/loaded/...)? (despite that this is probably more secure)

regards

[hanaa]

Is there no problem.? I mean, is it okay to connect to the database on each page, I thought having to connect many times is some kind of an overhead. Please correct me if 'm wrong. I read about persistent connections too, and also that they are not closed until the server is shut down/restarted.. Would you please give me a few lines on as to why connecting each time is okay..?

Would you also tell me if storing username and password in a session is okay and safe? Cant session files be intercepted in any manner?

Thanks,
Hanaa

[pbmods]

Heya, Hanaa.

Opening a connection is not a whole lot of overhead, and there are negatives to using persistent connections (you won't always get the same connection, so you can't rely on temporary tables and variables in between page loads).

You shouldn't need to store the User's password in cleartext; run it through sha1() or md5() before storing it instead.

Storing sensitive information (including login credentials) in a session is generally a bad idea because it is possible to intercept them.

Session data is stored (usually) in files in the /tmp directory on your server, so if the attacker can access those files, he can read your Users' session data.

A really simple (if unlikely) example of an exploit would occur if you had an insecure image loader script:

[code=php]
<?php readfile("/path/to/images/{$_GET['imagefile']}"); ?>
[/code]

An attacker could abuse this by setting $_GET['imagefile'] to '../../../tmp/{session_key}', where {session_key} is the 40-char session ID. Then he would be able to see the User's session info.

[hanaa]

Thanks pbmods and Dormilich.

I am making a database interface. So, I granted the necessary privileges to a user. Since (I'm considering that) there will be only one administrator for the database, I didnt see the need to make a table for users of the database interface. And so, I thought I'd pass the username & password entered by the admin in mysql_connect() ; To secure the password in a session, how could I use md5 here, because what the admin enters is what directly goes into mysql_connect() function. And if i use md5, to store it in a session, i'd have to pass the actual unencrypted password in the function, which cannot be retrieved from the encrypted thing.

And since you say that saving password in a session is not advisable, could you suggest some other method using which I can retain the username and pass entered by the user, until he logs out.. because i'll need those everytime I use mysql_connect() ..

This is what I did

<?php
session_start();

if(isset($_POST['submit_uname']))
{
	$_SESSION['username']=$_POST['username'];
	$_SESSION['pass']=$_POST['pass'];
}
?>
//
 //after a few lines
//
$conn=mysql_connect('localhost', $_SESSION['username'], $_SESSION['pass']);

And on the next page too, I use the same session variables to connect to the database.

Thanks :)
Hanaa.

[hanaa]

:) Will someone please tell me how the username and password entered by the user be retained over the next few pages.. I've detailed what I did in the post above.

[pbmods]

As long as you call session_start() at the top, the session variables will be available.

It's rather... unconventional to make the User's login credentials the same as the database login.

Generally, this is frowned upon because it makes not just your site but your data vulnerable if your credentials are compromised. You might not even notice you've been phished; someone could be hosting their own database from your SQL server (think identity theft database... but with YOUR name attached to the hosting contract!), and you'd never know until you opened up PHPMyAdmin!

Generally a better idea is to hard-code your SQL login info into the script and then store a separate set of credentials in the database... or just hard-coded into the script, if you prefer.

[ifedi]

To agree with the preceding post, the point is that you're attempting to make a mysql connection for each and every person who visits the site. This is most definitely not the best approach. Rather, a code section or a file (named e.g. "connections.ph p" could be created like so:

$user="siteuser";
$password="opensesame";
//shortly later
$link = mysql_connect('localhost', $user, $password);
//etc

The real point of security now lies in defining limited privileges for 'siteuser' within mysql.
Of course, you could create a number of users for different site features, depending on the levels of privileges you wish to assign for these features.

[hanaa]

Generally a better idea is to hard-code your SQL login info into the script and then store a separate set of credentials in the database... or just hard-coded into the script, if you prefer.

Did you mean hard code it into the script, and use that to check against the user entry? That way, it'd be safe and not in a session.. Did I get that right?
Something like the following?

if(isset($_POST['submit']))
{
 if($_POST['id']=='username' && $_POST['pass']=='password')
    { //log him in
     mysql_connect('--', 'username', 'password');
     //
    }
 else
    { //dont log him in
    }
}

Or to hard code my login and make a table of users, and check the form entries with that in the table? ( I wanted to avoid that. lazy me) I am doing that with the front end users. I'm making an online test taker.
Just to clarify, the script that i posted earlier is part of a database interface, and is meant to be used only by one person (or so I assumed for convenience's sake). I did define limited and only necessary privileges to the user ('username'). On the whole, what I wanted to avoid was to make a table for the database interface users(there's only one). Which is why I decided to pass the form entries directly into mysql_connect() . So, its still a bad idea? even if I've granted only the necessary privileges (I'll hard code it now anyway, just asking.)
Bear with me this one time :)
Thanks,
Hanaa

[pbmods]

You could do it that way, though I'd be sure to make the website login/password different from your MySQL login/password.

It's generally a bad idea to design your security around what *should* happen; nobody's omniscient, and one just simply cannot predict what will happen if someone gets ahold of his MySQL credentials.

If you're only going to have one User working on this part of your app, it's perfectly acceptable to forego the traditional database-driven authentication model and simply hard-code the credentials you are looking for.

What I'd be a little more insistent upon, however, would be encapsulating your MySQL credentials so that an attacker would only be able to compromise your application, not your entire database.

[hanaa]

What I'd be a little more insistent upon, however, would be encapsulating your MySQL credentials so that an attacker would only be able to compromise your application, not your entire database.

Thanks a lot pbmods
Encapsulating the mysql credentials means?

[coolsti]

I am not sure about the issue of one connection per page when there are many many users. I have not encountered this myself since my applications have been for a limited amount of simultaneous users (a company app).

If a user is not logged into the application already (see below), then the user is redirected to a log in page where he/she is requested to enter username and password. Submitting this will then do the usual code to see if the user is authorized. If so, then the user is logged in to the application for his/her session. When doing this, I create a large string composed of the user's IP address, a 16 character random string, the user's username, and an application specific string (since I have more than one application that the user can log in to on this server). This string is then saved in a Session variable, let us call it here $_SESSION['loginID']. I also save another variable, let us call it $_SESSION['loginstump'] which is composed of only the 16 character random key part of $_SESSION['loginID']. Note that the string does not contain the user's password.

When the user requests a page of the application, one of the first things that is done is to see if the user is logged in. This is done by checking to see if the $_SESSION['loginID'] is equal to $_SESSION['loginstump'] catenated with the user's username and IP address and application specific string. If so, the user is considered to be logged in, and can proceed with the page he/she requested. If not, then the user is redirected to the login page.

This is not totally secure against someone stealing the session. But at least there is some safeguard because I do a check here against the IP address. If someone steals the session but is coming from a different IP address, that user will not get the requested page.

I am open to any suggestions from others out there how to do this better, but this is how I do things. It requires a password only during the initial login, and will not require it again as long as the user does not a) time out, b) change computers or opens a completely new browser window.

[pbmods]

Thanks a lot pbmods
Encapsulating the mysql credentials means?

Keeping the MySQL credentials separate from the application credentials.

[hanaa]

Yes Okay... :) Thanks coolsti and pbmods.