Better way of securely connecting to the database?

[yoursurrogategod]

Hey all,

 

  In our organization we have a bunch of little apps that connect to MySQL/MSSQL as their backend.  Usually the databases have specific roles such as database_read, for these apps.  The username and password for database_read are stored inside of the code-base.  Now, this is in PHP, so the users don't see it.  But it still seems like a kludgy way of connecting to the database to do what you need.  Is there a way to somehow avoid having to write down the connection information in the application source, but still have it connect?

 

[edit]

 

Oops, had a brain-fart.  Someone please move this thread to the PHP coding help form.

[smoseley]

Read up on safe_mode DB connections.  Basically, you define your connection information in php.ini, and your app is completely unaware of auth info.

 

Edit: sorry, it's sql.safe_mode.  Here's the doc: http://www.php.net/manual/en/ini.core.php#ini.sql.safe-mode

[yoursurrogategod]

Thank you.  I didn't know whether something like this could be done in PHP.

[smoseley]

Well, yeah, even without the sql.safe_mode setting, you should be able to store any params in php.ini and retrieve them in php with ini_get().  If you do that, just be sure not to persist them in a global variable.

[scootstah]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

[smoseley]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

Not entirely accurate. FS access doesn't imply root access. 

 

Besides that, it's definitely more secure than persisting the values in variables in your code.

[scootstah]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

Not entirely accurate. FS access doesn't imply root access. 

 

Besides that, it's definitely more secure than persisting the values in variables in your code.

 

But they can still upload a script to manipulate the database.

 

EDIT: And how is it more secure? The only way anyone will ever see the credentials if you store them in variables is:

- by accessing the file system

- you echo'ing them

 

You could argue that you might have a security hole where the credentials get echo'd...but that would be a ridiculously amateur thing to happen, so the only real threat here is gaining access to the file system.

 

And assuming someone does somehow manage to get your credentials, your database server should only be accepting a local connection anyway - therefore nobody can connect remotely, and in fact the only way to manipulate the database would be, again, access to your file system.

[smoseley]

And how is it more secure? The only way anyone will ever see the credentials if you store them in variables is:

- by accessing the file system

- you echo'ing them

For one thing, if you're in a distributed development environment, your DBAs can keep production database credentials unknown to PHP devs.

 

For another, if a script gains FS access, it can be limited so as not to read the file.

 

Finally, dumping $GLOBALS (a common hack) wouldn't work.

[yoursurrogategod]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

Not entirely accurate. FS access doesn't imply root access. 

 

Besides that, it's definitely more secure than persisting the values in variables in your code.

You mean keeping the login credentials in local variables?

[scootstah]

For another, if a script gains FS access, it can be limited so as not to read the file.

 

I think you misunderstood me. If you have file system access you can upload a script that can manipulate the database. It doesn't matter where you define the connection, because if a connection exists any script in the file system can utilize it. So while you may not actually be able to see the credentials, you can still do anything you want to the database since you have an active connection.

 

For this reason alone, the location of the credentials is irrelevant. If they are stored in a .php file an attacker requires file system access to view the .php file and get the credentials - which is completely moot because if they have file system access you are already screwed.

[smoseley]

For another, if a script gains FS access, it can be limited so as not to read the file.

 

I think you misunderstood me. If you have file system access you can upload a script that can manipulate the database. It doesn't matter where you define the connection, because if a connection exists any script in the file system can utilize it.

 

Ah yeah, true... slipped my mind for a minute that mysql_query() will use any open DB connection, whether or not you know where to find its reference.  That's kind of crappy. 

[yoursurrogategod]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

Not entirely accurate. FS access doesn't imply root access. 

 

Besides that, it's definitely more secure than persisting the values in variables in your code.

You mean keeping the login credentials in local variables?

Soo... best to keep the login credentials in non-global variables, yes?

 

[scootstah]

Keep in mind that if someone gains access to your file system, setting the username/password in the php.ini is completely irrelevant.

Not entirely accurate. FS access doesn't imply root access. 

 

Besides that, it's definitely more secure than persisting the values in variables in your code.

You mean keeping the login credentials in local variables?

Soo... best to keep the login credentials in non-global variables, yes?

 

 

Like I said, barring a ridiculously foolish mistake the only way the credentials will get out is if someone gains access to the file system - in which case it doesn't matter anyway.

[yoursurrogategod]

Thanks scootstah, I'll keep that in mind.

[cpd]

your database server should only be accepting a local connection anyway

 

I'm inclined to disagree. An MSSQL database - or any database for that matter - doesn't have to reside on the same server as the web server. SQL Server is a typical example where the database often resides elsewhere. Therefore, someone can gain access remotely should they get hold of the credentials.

 

Consider setting up levels of access including permissions granted to specific schema's - should your database warrant them - tables and whatever else. If your deleting records consider setting up a processing system where by a cron, or similar, is set off to process a deletion list every night. This will stop anybody using the front-end UI from removing any records in the database should they find a loop-hole. Might be a little off topic but its still security related.

[scootstah]

your database server should only be accepting a local connection anyway

 

I'm inclined to disagree. An MSSQL database - or any database for that matter - doesn't have to reside on the same server as the web server. SQL Server is a typical example where the database often resides elsewhere. Therefore, someone can gain access remotely should they get hold of the credentials.

 

I believe in that case you could set it up to only allow certain connections, like your server. I might be wrong though.

[smoseley]

your database server should only be accepting a local connection anyway

 

I'm inclined to disagree. An MSSQL database - or any database for that matter - doesn't have to reside on the same server as the web server. SQL Server is a typical example where the database often resides elsewhere. Therefore, someone can gain access remotely should they get hold of the credentials.

Not entirely accurate... you can grant permissions to specific IP addresses (rather than to '%' - which is open priveleges from anywhere) - best to do so with LAN IPs, which will prevent any outside connections:

 

GRANT SELECT, INSERT ON my_database.* to username@'192.168.x.y' IDENTIFIED BY 'remote_password';