Any benefit of putting MySQL code and PHP code in different files?

Question

I wonder what are benefits of separating MySQL code from PHP code.

Currently our MySQL code is not separated and we're still evaluating if this is a good thing to do, what are pros / cons, if any or is it the same?

Below is a simple example to help you understand what I mean.

PHP script (log.php):

require_once '../dbconnect.php';
require_once '../classes/database/db_log.php';

$dbActivitylog = new db_tblactivitylog;

$distinct_users = $dbActivitylog->distinct_users();

foreach($distinct_users as $user)
{
  echo '<span class="fancy_color">{$user}</span><br />";
}

MySQL code => db_log.php:

class db_tblactivitylog
{
    // Return an array of distinct users in the activity log
    public  function    distinct_users()
    {
        $query = "SELECT DISTINCT user FROM log ORDER BY user ASC";
        $result = full_query($query);
        $distinct_users = array();
        while($data = mysql_fetch_array($result))
        {
            $distinct_users[] = $data['user'];
        }
        return $distinct_users;
    }

    // This function deletes $max_rows_to_delete rows in excess of the activitylimit in config, that match the where condition, keeping the newest rows
    public  function    truncate_rows($where, $max_rows_to_delete=9999)
    {
        global  $CONFIG;

        $max_rows_to_keep = $CONFIG['ActivityLimit'];
        $result = select_query( "log", "", $where, "id", "DESC", $max_rows_to_keep.",".$max_rows_to_delete);
        while($data = mysql_fetch_array($result))
        {
                    // delete query function, nothing unusual
            delete_query( "log", array( "id" => $data['id'] ) );
        }
    }
}

We are planning to use MySQLi, just wonder if there are any direct benefits of separating code as we can do it on the way?

Please don't use `mysql_*` functions anymore, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. Instead you should learn about [prepared statements](http://bobby-tables.com/php.html) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you pick PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — Marcel Korpel, Nov 21 '13 at 14:20

score 2 · Accepted Answer · answered Nov 21 '13 at 14:20

2

One direct benefit of putting database parameters in a separate file is that by doing it you are able to put them in a place where they are not publicly accessible - like above your DocumentRoot folder. By doing that you are greatly increasing the security of your database access.

answered Nov 21 '13 at 14:20

Hm...not entirely sure I see how that makes sense. Any strings written in a PHP file (`$defaultUsername = "Mingebag";`) have just as much risk of being output to the client as files read from another folder into a variable - which is to say none if the code is written as intended. Maybe I'm missing something though...? – Katana314 Nov 21 '13 at 14:23
If the file is outside of publicly accessible place it prevents attackers from using direct access exploits like manipulating server handlers (i.e. making Apache believe that a `.php` file is a plain text file, thus outputting it). Furthermore putting it in a separate folder allows for setting a more restrictive set of permissions on a file-system level. – Nov 21 '13 at 14:39
Okay, makes sense. I guess the biggest vulnerabilities of the sites I write in the future will be my lack of hacking imagination. – Katana314 Nov 21 '13 at 14:43

score 0 · Answer 2 · edited May 23 '17 at 11:50

by using db_connect in a separate file, it becomes inaccessible to general users and this way, provides better security to login credentials and other sensitive data.....read here for clearing out your query How to secure database passwords in PHP?
also, speaking of security, your are using mysql_ which offers sql_injection risk and is depreciated....use either mysqli or PDO for better security!

hope this helps!!

Any benefit of putting MySQL code and PHP code in different files?

2 Answers2