Secure DB connection function?

Question

I've been reading through different secure ways to connect to databases and wanted to run this idea past you before I tie myself in knots trying it with my actual databases if it has no security benefits or simplification of switching between databases.

I was thinking that it would ensure the db_* variables are killed off after the function is used and the connection has been made to the relevant database:

<?php
function dbconn($db_hostname='localhost',
                $db_username='',
                $db_password='',
                $db_database='database1'
                $db_object='connection') {

    if($db_username=='' && $db_password=='') {
        switch ($db_database) {
            case 'database1':
                $db_username='user1'; 
                $db_password='pass1';
                break;
            case 'database2':
                $db_username='user2'; 
                $db_password='pass2';
                break;
            default: 
                echo "No database defined to connect to";
                break;
        }
    }
    else if($db_password=='') {
        switch ($db_username) {
        case 'root':
            $db_password='rootpass';
            break;
        case 'user':
            $db_password='userpass';
            break;
        default:
            echo "No password known for this user";
            break;
        }
    }

    $db_object= new mysqli($db_hostname, $db_username, $db_password, $db_database);
    if ($db_object->connect_error) die($db_object->connect_error);
} 
?>

Answer 1

It's unclear to me what you're trying to do, from the code you've provided. However, I can answer your question: "What is a secure way to connect to a database?"

With PHP working as a CGI-like language, the primary concern with connecting securely, is that your configuration file could be exposed. There are two possible solutions to this:

1. A PHP file as your configuration

This is probably the most common approach, and especially used often in redistributable software. By making your "configuration file" a PHP file that simply sets variables and is included in other pages, the PHP interpreter will parse this file, rather than returning its contents. The location of the file doesn't really matter - as long as it's somewhere where PHP files can be executed. In a typical application, this is anywhere in the document root except for the uploads directory.

An example configuration file (db_config.php):

<?php

$db_host = "localhost";
$db_user = "username";
$db_pass = "password";
$db_database = "database_name";

You would simply require('db_config.php'); in your initialization/header/etc. code.

You should only use the configuration file for configuring the connection, not for making it. This way, it makes it easy to change your connection code later on, without having to modify your configuration file on every server that your application runs on.

2. A serialized configuration file

Alternatively, you could use something like JSON or YAML to create a configuration file. The main advantage is that you can use the same configuration file for any auxiliary scripts or applications that need to connect to the same database, but aren't written in PHP.

However, you should never place this file in your document root. As far as your webserver is concerned, a JSON or YAML file is a 'viewable' file, and it will happily show it to anybody who has the URL.

The correct way to use a JSON/YAML/etc. file as your configuration file, is to make sure that it's placed outside of the document root, so that the webserver cannot possibly serve it up to a user. Trying to give it an 'unguessable' name is not sufficient.

You could also use something like .htaccess, but this is not recommended as it will make changing to other webservers harder, and a webserver misconfiguration could expose your database connection details. Only use this as an absolute last resort.

Other considerations

There are three other main points that I want to address here.

Multiple database credentials

In your original code, it looks like you're trying to add a feature where you can select the database you want to select to. In reality, this is almost never what you want. Every server/installation should only have its own database credentials.

If you follow the suggestions I gave for storing your configuration data, then it will be very easy to have a separate configuration file on each system, without having to ever change it. If you're using Git or another version control system that lets you ignore files, you can safely (and should) make it ignore the configuration file. You'll simply have a different configuration file on each server.

Overall security

Of course, you should make sure that your overall security is in proper working order. If you have a LFI vulnerability or somebody can upload a shell to your server, then no amount of putting files outside a document root is going to protect your database credentials. OWASP is a decent resource on general (web) application security.

PDO or mysqli_?

Judging from your snippet of code, you're using mysqli_. While this can be a valid choice if you use parameterization / prepared queries correctly, I would not recommend using it. PDO is a database-independent SQL library that focuses more heavily on parameterized queries. It's included by default in more or less every recent PHP installation, and as a bonus it'll let you switch between different SQL servers.

Using parameterized ("prepared") statements is absolutely critical - it is the only reliable way to prevent SQL injections, which seems to be the most important issue you'll want to protect yourself against here. A goood introductory guide can be found here.