How To Set Up Your Mysql Connection

**surefire** · May 31, 2003

I'll try to quickly cover main concepts in connection to your database so that

1- Your website security isn't weak

2- You can quickly change your username and passwords if need be

3- Your code is portable to another website (why do the same work twice?)

Please understand that there are a million and one tutorials on writing PHP scripts that store, sort, and retrieve information in your database. I won't cover this in depth, but will provide a few good links for further study.

First, you need a database.

There is a great movie tutorial that explains setting up the mysql database from cpanel:

http://www.totalchoicehosting.com/help/moviepage.htm

When you set up your database, and assign users to the database, cpanel prints out some code that you can copy and paste into your php code... don't.

We're going to connect in a way that saves you time and creates a more secure website.

Setting up your "keys"

Let's create a file, in this example we'll call it keys.php

You can call it whatever you want, but I strongly suggest you end it with .php

Here's what it's going to look like:

<?php
$host="localhost";

$db="usernm_dbname";

$user="username";

$passwd="password";

?>

The items in bold need to be replace with the actual information for your database.

That's it for our keys.php file. Now we need to find a home for it.

Where to store your keys

Ideally, you want to make it a little tougher for someone to access your file. The fact that we saved our keys file as php is a start... but let's go one step further.

We're going to create a new folder in our ROOT directory. In other words, it will NOT be inside our public_html folder. We're going to place it OUTSIDE the publicly accessible files.

So go ahead and ftp to your site, and create a folder in your root directory. In this example, I'll call it 'safe'

Now all we need is a way for our php pages to grab the keys right when we need them.

Grab your keys

Let's create a page that will connect to our database.

In this example I'll call it connect.php

<?php
include("/home/usernm/safe/keys.php");

$dbh=mysql_connect ($host, $user, $passwd) or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("$db");

?>

The only thing you need to change in this script is the bold part which is the username that you use to ftp to your site, log in to your capel, and everything else at TCH.

Unless there is something different about your site, this should be the correct directory location of your site root.

Taking it a step further

Let's ftp this connect.php file to our website and put it in our main public_html folder. You can put it wherever you want, so long as you adapt the rest of these instructions to point to the right location.

Now, on any page that we want to connect to our database, we can put

<?php
include $_SERVER['DOCUMENT_ROOT']."connect.php";

//MySQL queries here

?>

Learning SQL

Great resources at the TCH help page.

Also a great tutorial at DevShed

*Note. the term 'keys' is not a php term and was made up for this tutorial to be witty.

Edited May 31, 2003 by surefire

**btrfld** · May 31, 2003

Great Post, Jack. Very clear. Thanks.

**surefire** · June 1, 2003

Most welcome.

**raDeon** · July 27, 2003

Hi I have a question. I see you use the long way of the including include $_SERVER['DOCUMENT_ROOT']."connect.php"; alot. I read that you can't include the normal way? I use require() instead of include() most of the time and it works.

require("config.php"); is alot easier

**idallen** · July 27, 2003

We're going to connect in a way that saves you time and creates a more secure website. [...]

We're going to create a new folder in our ROOT directory. In other words, it will NOT be inside our public_html folder. We're going to place it OUTSIDE the publicly accessible files.

All files accessible from PHP are accessible from PHP, CGI, or shell scripts written by anyone

who shares the server on which you are hosted. If your PHP script can read the

keys file, so can a script written by anyone on your server (if they know where the file is).

Don't use PHP or .htaccess passwords for sensitive information on a shared server.

http://www.totalchoicehosting.com/forums/i...indpost&p=21336

**Mel** · July 27, 2003

If your PHP script can read the
keys file, so can a script written by anyone on your server

so what's a better way to hide the keys if putting them in the safe doesnt work?

**idallen** · July 28, 2003

what's a better way to hide the keys if putting them in the safe doesnt work?

You can use CGI scripts instead of PHP. CGI scripts run as *your* userid, not the userid

of the web server. You can put anything you want in files and directories that are set to

only be readable by you (or by CGI scripts running as you). The script you write determines

what you want people to see; the files are safe from viewing by others.

I don't yet know how to connect to a database using CGI; but, I'll probably look into it

later this summer. Maybe others can fill in those details?

**surefire** · July 28, 2003

Idallen,

Would you consider this scenario safer... ?

PHP scripts are the heart of the site, and when they need to access the database, they run a CGI script that grabs the passwords from a file in the root of the site.

Or are you saying that if you were going to build a site, that you would build the entire thing with cgi scripts?

What I am interested in... is if you can point me in the direction of a safer way to have a php site on a shared server...?

Truthfully, I'm not that concerned, given the steps that I've already taken. But if all I have to do is use a tiny cgi script to grab the passwords... then what the heck, I'll write one.

**idallen** · July 28, 2003

Nothing that uses PHP to access disk-based private data can be made

secure (unless your server runs PHP in safe_mode, or if you're willing

to ask the web user for the password every time).

If you write some PHP script that calls your CGI script, someone else on

your server can read your PHP, copy and modify it, and run your CGI too,

using their data. Anything you can do with PHP and CGI, they can do too.

Your privacy and control starts at the point where you call your own

CGI script; from then on, the CGI script itself and all the files it

accesses can be private - owned by you and only readable/writable by you.

The CGI scripts can contain or reference the passwords needed to access

your protected database.

You have no control over who calls your CGI script or what data gets

passed to it; so, your CGI script has to do all your data validation and

processing. If you use your CGI to grab and return a saved password,

anyone on your server could use your CGI to grab and return the same

password; this is not secure. Your CGI must receive user input, process

it properly, then access your protected files in a controlled way and

not divulge how it does it.

If you can structure your PHP to use your own CGI scripts in a controlled

way, yes, it's the best safety you can get on a shared server.

If you do stay with PHP only, rememember that you are only exposed to

people (1) who are on the same server as you, (2) who know how to write

PHP or CGI scripts to access your files, and (3) who care enough about

your data to bother.

With luck, not many people will bother.

**surefire** · July 28, 2003

If you do stay with PHP only, rememember that you are only exposed to
people (1) who are on the same server as you, (2) who know how to write

PHP or CGI scripts to access your files, and (3) who care enough about

your data to bother.

And to quote you again for number (4)... which I think is as important as anything else...

(if they know where the file is).

You and I have debated this in the past.

You definitely are much more knowledgeable than I when it comes to security as it pertains to Linux. I wouldn't be surprised if there were other subjects you were more knowledgeable on than I.

I am very happy to see you balance out your dire warnings with some realistic points that put the whole topic in perspective.

If there are folks that can hack the Pentagon and NSA, then security can only be seen in the context of what you're willing to do to approach 100% security. I don't believe that there is such thing as 100% security.

You make some valid points. I honestly have no idea whether cgi is more secure than PHP... but I'll take your word for it.

But my reality is that

1- I program best in PHP

2- PHP has many advantages over cgi that might outweigh security

3- I am not going to move from TCH to my own server

With that in mind, I started this thread as a way to give other PHP coders an idea of how to improve security.

As you have pointed out, if the risk comes only from those at TCH with the skill, malicious intent, skill, and the ability to root out or guess the location of my scripts... then the risk is much smaller than many other common scripting security gaps (like mysql injection attack).

You have also pointed out, that secure information shouldn't be held in a mysql database accessed by php. To a point, I would agree. I would say that anyone looking to put personal data (social security numbers, private info) online should rethink their concept. Secure, private, and vital info doesn't belong on the web. All attempts to protect it are attempts at approaching 100% protection... but there will always be loopholes to be exploited by those with the desire, skill, and time.

A lot of my sites are business sites that generate revenue. The risk reward equation is balanced FAR in the direction of going forward with the database site rather than sitting on the sidelines because someone at TCH might target me.

Change passwords often. Don't use easy to guess passwords. Don't make it easy to hack your scripts. Backup your database and site often. And don't put vital secrets online.

Any home owner with an alarm systme on their house, lights in the yard, a dog at the door, and a gun under the bed is safer than the neighbor that leaves the windows open at night and the doors unlocked.... but nothing is 100%.

Edited July 28, 2003 by surefire

**idallen** · July 29, 2003

> I don't believe that there is such thing as 100% security.

I agree. Thumbs Up

However, unlike MySQL injection, which is a security hole caused by poor

programming (i.e. broken security due to a bug in the code), the lack

of PHP password and file security isn't a bug - it's a (mis-)feature of the

way PHP works on shared servers and no amount of careful PHP programming

on your part can fix it. It's not a lock with a faulty tumbler; it's a lightly

hidden back door with no lock at all and no means of installing one.

A properly-written CGI implementation has no such built-in holes.

When you use PHP, the risk is that someone on your server will walk

through the unlocked door, or someone will find a bug in your PHP code.

When you use CGI, the only risk is that someone will find a bug in your

CGI code. There are no back doors that cannot be locked.

As long as shared-host PHP users are aware of the extra risk that they

take, everyone can make that democratic decision themselves.

As you say, despite the higher risk inherent in using PHP, the

considerable ease of using PHP may still outweigh the danger.

My particular concern is that people be made fully aware of the risks

and benefits so that they can make their own informed decisions.

As a long-time system administrator and total PHP newbie, I'm most

familiar with the Dark Side. Your superior PHP skills provide an

excellent contrast to that and demonstrate the fun and cool things that

PHP can do. I think we cover the range pretty well between us! :angry:

**surefire** · July 29, 2003

Amen.

**Mel** · July 29, 2003

Very enlightening.

Thank you for the discussion.

**Galleon** · July 3, 2005

Great topic and alot of insight into both worlds of programming.

Thanks Guys

**TCH-Rob** · July 3, 2005

Welcome to the forums Galleon.

**TCH-Don** · July 3, 2005

Welcome to the forums Galleon :flex:

**TCH-Bruce** · July 3, 2005

Welcome to the forums Galleon

**D.Slatk** · August 14, 2005

Very good tutorial! :thumbup1:

I am not an expert at security or anything, but I've always set my access keys as constants instead of variables, since constants can't be changed once set. I am not sure of any downsides to this, so I might as well do it to be a little more secture. :naughty: Here's an example:

><?php

define ('DB_USER', 'username');
define ('DB_PASSWORD', 'password'):
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'database');

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Unable to connect to mysql!');
@mysql_select_db (DB_NAME); OR die ('Unable to select a database!!');

?>

Edited August 14, 2005 by D.Slatk

**eggrock** · August 26, 2005

You can use CGI scripts instead of PHP. CGI scripts run as *your* userid, not the userid
of the web server. You can put anything you want in files and directories that are set to

only be readable by you (or by CGI scripts running as you). The script you write determines

what you want people to see; the files are safe from viewing by others.

I don't yet know how to connect to a database using CGI; but, I'll probably look into it

later this summer. Maybe others can fill in those details?

<{POST_SNAPBACK}>

So if I have a Perl script in my root (home) directory, I can have it open the connection to the DB and return the handle??? Would that be secure?

Example (db_login.pm)

>#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);
use DBI;

sub db_open()
{
 $dbh = DBI->connect("DBI:mysql:DB_NAME:localhost", "ID", "PASSWORD" or die "Database connect error: $DBI::errstr\n";

 return $dbh;
}

1;

-----------------------

Something like that perhaps? The capitalized items (excluding 'DBI') would be hardcoded, or else stored in a file that could be read by the module to get the ID/password....?

Edited August 26, 2005 by eggrock

**TweezerMan** · August 28, 2005

What determines the relative security or insecurity of of your database connection info (primarily the MySQL database username and password) is the permissions on the file they are stored in.

If the file has read permissions set for 'world' (the last digit when permissions are described numerically), the data can be read by anyone else on the server. If the file does not have read permissions set for 'world', then the file can only be read by you (and the 'root' superuser).

If you choose to store your database connection info in a separate file, that file should have only 0600 permissions (read + write permissions for you only) to protect it from being read by someone else on your server.

A perl CGI script will not magically protect database connection info hard-coded into the script, if the permissions on the script are not set strictly enough.

Most people set permissions on perl CGI scripts to 0755 (read + write + execute permissions for you; read + execute permissions for everyone else). Since everyone has read permissions on the script, another user on your server could use a script to open your CGI script as a regular file and obtain your database connection info.

To protect your CGI script from being read by another user's script, the permissions on the CGI script should be set to 0700 (read + write + execute permissions for you only). Because of the way the web server runs CGI scripts, it will still be able to execute your script when it is called in a browser. Then no other users on the server would be capable of reading your script and obtaining your database username and password.

Side note: PHP scripts can also be set up to run as CGI scripts. There is some sacrifice in speed, and there are some environmental differences that sometimes need to be taken into account. But a PHP CGI script does run under your user ID, so you don't have to set file and directory permissions wide open to everyone else on your server.

Hope this helps...

**TCH-Thomas** · August 28, 2005

Welcome to the forum, eggrock.

**TCH-Bruce** · August 28, 2005

Welcome to the forums eggrock

**TCH-Don** · August 28, 2005

Welcome to the forum, eggrock :thumbup1:

**TCH-Rob** · August 28, 2005

Welcome to the forums eggrock

**eggrock** · August 30, 2005

What determines the relative security or insecurity of of your database connection info (primarily the MySQL database username and password) is the permissions on the file they are stored in.

If the file has read permissions set for 'world' (the last digit when permissions are described numerically), the data can be read by anyone else on the server. If the file does not have read permissions set for 'world', then the file can only be read by you (and the 'root' superuser).

If you choose to store your database connection info in a separate file, that file should have only 0600 permissions (read + write permissions for you only) to protect it from being read by someone else on your server.

A perl CGI script will not magically protect database connection info hard-coded into the script, if the permissions on the script are not set strictly enough.

Most people set permissions on perl CGI scripts to 0755 (read + write + execute permissions for you; read + execute permissions for everyone else). Since everyone has read permissions on the script, another user on your server could use a script to open your CGI script as a regular file and obtain your database connection info.

To protect your CGI script from being read by another user's script, the permissions on the CGI script should be set to 0700 (read + write + execute permissions for you only). Because of the way the web server runs CGI scripts, it will still be able to execute your script when it is called in a browser. Then no other users on the server would be capable of reading your script and obtaining your database username and password.

Hope this helps...

<{POST_SNAPBACK}>

Thanks, but I'm more concerned about any possibility of people off the server being able to use some exploit or another to see the Perl code. In other words, is code itself secure from being seen by anyone external to the server; there's no exploit or hack that will allow people to see it...? (Barring errors on my part or other things like SQL injection etc.)

Thanks for the welcome everybody. :xmas:

[edit] I guess that's more of an Apache & OS question than database security so if this needs to be moved feel free.

Edited August 30, 2005 by eggrock

**TweezerMan** · August 30, 2005

Thanks, but I'm more concerned about any possibility of people off the server being able to use some exploit or another to see the Perl code. In other words, is code itself secure from being seen by anyone external to the server; there's no exploit or hack that will allow people to see it...? (Barring errors on my part or other things like SQL injection etc.)

<{POST_SNAPBACK}>

It is not totally secure. That's why the recommended 'best practice' is to store sensitive data such as usernames and passwords in a separate file not within the web server's document root directory (stored outside of the /public_html directory).

If everything is configured correctly, it is relatively secure. If there is a mistake in configuration though, your code could inadvertantly end up being viewable to anyone who browses to it.

**eggrock** · August 30, 2005

It is not totally secure. That's why the recommended 'best practice' is to store sensitive data such as usernames and passwords in a separate file not within the web server's document root directory (stored outside of the /public_html directory).

If everything is configured correctly, it is relatively secure. If there is a mistake in configuration though, your code could inadvertantly end up being viewable to anyone who browses to it.

<{POST_SNAPBACK}>

Thanks David, that's what I'm using right now.

Sign In

How To Set Up Your Mysql Connection

Recommended Posts

surefire

btrfld

surefire

raDeon

idallen

Mel

idallen

surefire

idallen

surefire

idallen

surefire

Mel

Galleon

TCH-Rob

TCH-Don

TCH-Bruce

D.Slatk

eggrock

TweezerMan

TCH-Thomas

TCH-Bruce

TCH-Don

TCH-Rob

eggrock

TweezerMan

eggrock

Hosting