Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Programming Tutorials : How-To: Create A Common MySQL Connect File

[Fixago]

On just about every domain I own, or for every client I do a job for, there's some interaction with MySQL and PHP. Here's a good "common database" file to use. I call mine common.inc and use it as an include for every page, but of course you can other other information into your common file, which is always a good thing to have.

PHP Code:

// Database Variables
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "database";
 
$MYSQL_ERRNO = "";
$MYSQL_ERROR = "";
 
// Connect To Database
function db_connect() {
  global $dbhost, $dbuser, $dbpass, $dbname;
  global $MYSQL_ERRNO, $MYSQL_ERROR;
 
  $link_id = mysql_connect($dbhost, $dbuser, $dbpass);
 
  if(!$link_id) {
    $MYSQL_ERRNO = 0;
    $MYSQL_ERROR = "Connection failed to $dbhost.";
    return 0;
  }
  else if(!mysql_select_db($dbname)) {
    $MYSQL_ERRNO = mysql_errno();
    $MYSQL_ERROR = mysql_error();
    return 0;
  }
  else return $link_id;
}
 
// Handle Errors
function sql_error() {
  global $MYSQL_ERRNO, $MYSQL_ERROR;
 
  if(empty($MYSQL_ERROR)) {
    $MYSQL_ERRNO = mysql_errno();
    $MYSQL_ERROR = mysql_error();
  }
  return "$MYSQL_ERRNO: $MYSQL_ERROR";
}
 
// Print Error Message
function error_message($msg) {
  printf("Error: %s", $msg);
  exit;
}
 
// Connection String Example
# $link_id = db_connect($dbname);
# if(!$link_id) error_message(sql_error());
#
# $query = "SELECT * FROM test_table";
# $result = mysql_query($query);
#
# if(!$result) error_message(sql_error());
#
# $data = mysql_fetch_array($result); 


[adaml]

It makes more sense to have your connection settings as constants rather than variables, as they shouldnt change dynamically! Plus you dont need to globally call them from within the functions.

[white_2kgt]

OMG, you call it what? connect.inc and you store passwords in there? You know anyone can request that file from your webserver in plain text if they know the location right? No, security through obscurity is not an option. Once downloaded, they have your db username/password combo. Don't know how many 'clients' you have but you will want to go back and rename it connect.inc.php at the least.

[innova]

Calm down psycho

The easy answer is to store all non-user-accessible content outside the webroot. See? That wasnt so hard

That is good practice regardless of the file extension.

[Fixago]

Originally Posted by white_2kgt

OMG, you call it what? connect.inc and you store passwords in there? You know anyone can request that file from your webserver in plain text if they know the location right? No, security through obscurity is not an option. Once downloaded, they have your db username/password combo. Don't know how many 'clients' you have but you will want to go back and rename it connect.inc.php at the least.

*cough* Apache Handlers *cough* Do you think I'm that stupid? I make sure that Apache makes the .inc extension act as a script so it can't be read.

I just call it by the .inc extension because it's an include and I'm just organized like that I guess. Innova makes a good point too, I could just do that instead. Thanks.

[white_2kgt]

Originally Posted by innova

Calm down psycho

The easy answer is to store all non-user-accessible content outside the webroot. See? That wasnt so hard

That is good practice regardless of the file extension.

Sorry, I missed that in the Tutorial where he said to be sure to store this file outside of the webroot. I also run a firewall does that make me psycho?

Originally Posted by Fixago

*cough* Apache Handlers *cough* Do you think I'm that stupid? I make sure that Apache makes the .inc extension act as a script so it can't be read.

I just call it by the .inc extension because it's an include and I'm just organized like that I guess. Innova makes a good point too, I could just do that instead. Thanks.

OOPs, I also missed in the tutorial where you said I had to configure an apache handler to mark .inc as a script.

Come on. What is the most likely scenario to someone that is going to take your code and use it? They are on a shared host (moding the httpd.conf isn't an option) and are going to upload the file directly to the root of whatever script they are writing and include it, being inside the webroot. The simplest solution is to put the .php extension on the end of ALL files with php code in them, wow imagine that, you can also have the .inc if you wish, just call it common.inc.php . Nothing to configure, nothing to worry about and you are still organized, and secure.

I'm not trying to pick a fight, just trying to get you, and everyone else, to think about this kind of stuff when writing code.

[innova]

The proper thing to do is really to cover your bases on all fronts. You are correct in noting that *most* shared host customers are not very knowledgable and should cover their bases by appending a .php to their inc files.. that is a good recommendation.

I think that many tutorial writers arent necessarily directing their work towards novices and file security might be assumed.

Fixago - Your way works fine, but it relies on proper webserver config to do it. A host might temporarily reveal INC content during an apache upgrade (ie, its out of your control). If allowed, you can also use .htaccess to force the issue as a backup measure.

Putting them outside the webroot allows you to (largely) avoid your host's administrative mistakes / misconfig and is probably safer. Lets not get upset, this is good for the newbs to read

[webviz]

I suggest using Object Oriented Programming (OOP) to create a MySQL class.

[Burhan]

If you do a lot of MySQL interaction in all your scripts (lets face it, who doesn't?) try to start using a known framework or develop your own. This way, you aren't re-writing your code each time.

My favorite framework to use is Qcodo simply because it doesn't try to be a do-all be-all framework (note: its PHP5-only).

[GoodFellas]

I prefer to write my own framework, it may not be AS secure as some of the more popular ones, but hey, i know exactly what it can do and what goes in it.

[focusinterview]

I think mysql encrypts the password for the username and its not shown in the my.ini file

[mayan]

thank you for sharing the coding.