I agree with you too.  Database backup file should not be tempered and to prevent that, prevent download and view is one of the ways I could do for now.

Hi APMuthu,

The codes work fine.   Thank you so much.

@khkoh: Anyone who takes a restricted backup using the script here can always inject the necessary sqls with different users, security_roles, etc., and upload and restore them. It can also be able to exclude certain prefixed tables that may not be part of native FA at all and managed elsewhere. If prefixes are used, non prefixed tables get excluded in the original code itself.

In preparing this code hack, understanding and documenting the backup process has been achieved.

Restoring from a completely new hacked backup file with only insert statements can be a nice way of importing records into FA and in some instances provide a base for a new CoA!

I guess you could prevent your users from uploading/downloading backups, but that won't stop them from overwriting each other’s data. That's why you don't want to give users access to the backups on a multi-user system in the first place.

I will be testing the scripts and I will let you know how it goes...  Many thanks.

Dear TM,

Thanks for responding to this post.

I noted a major security issue if we have the tables 0_users, 0_security_roles etc included in the backup sql files. The 0_users encrypted password can be replaced (by users who is familiar with the encryption) and the roles can be changed. If the data base is then restored, the admin/users' passwords can be compromised.  I have tested it and I found that this is a real vulnerability.

To the minimum, I feel that there should be 2 types of backups provided.  1. "Full back up" including all the tables and only to be done by the authorized personnel such as system administrator or the head of the company.  Ordinary users should not be allowed to perform this backup.  2.  "User data backup" for "undo" purpose and this should exclude the tables that contain the login, passwords and access control.

There is no way to restore the data on a fresh system using these "backups" because, just like incremental backups, they require a "base" backup to start from.

Whatever it is you are trying to achieve, this is not the way to do it.

Lines 505 to 513 in admin/db/maintenance_db.inc:

    // get auto_increment values and names of all tables
    $res = db_query("show table status");
    $all_tables = array();
    while($row = db_fetch($res))
    {
        if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
            ($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
            $all_tables[] = $row;
    }

is now replaced with:

    // get auto_increment values and names of all tables
    $res = db_query("show table status");
    $all_tables = array();
    $skip_tables_list = Array('users', 'security_roles', 'sql_trail');
    for ($i = 0; $i < count($skip_tables_list); $i++) {
        $skip_tables_list[$i] = $conn["tbpref"].$skip_tables_list[$i];
    }
    while($row = db_fetch($res))
    {
        if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
            ($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
            if (!(in_array($row['Name'], $skip_tables_list, true)))
                $all_tables[] = $row;
    }

The variable $skip_tables_list is populated with the names of tables to be skipped (without table prefixes).

The tables missing tables in the backup remain untouched in the database when restored.

The wiki has been updated and the code has been tested.

Thanks very much for the codes and Wiki'ed it.

I have added the codes provided and test run it, the few excluded tables still exist in the backup .sql file (please see the appended codes).  I inspected the few variables and arrays and I couldn't spot the issue.  I need your advise on this....

Another potential issue which is related to the restoration of this modified backup file.  As stated in the Wiki, the restore functions will first drop all the tables before being recreated and populated.

Wiki: "On restoration, these tables will first be dropped before being recreated and populated."

If the tables are excluded, unless the backup .sql totally ignore these excluded tables and they are not dropped during restoration.  (I hope this is the case).  Otherwise the Restoration of these excluded tables will be "emptied" and it will make the FA not functioning as the data are missing (for example, the users will not be able to login since the user table is empty).   

I'm sorry I could not contribute to the coding but I could only share my thoughts with regards to this issue.  Your help is very much appreciated.

Thank you!

KH

=====================
   $res = db_query("show table status");
    $all_tables = array();
    while($row = db_fetch($res))
    {
        if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
            ($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
            $all_tables[] = $row;
    }
   
//Skip tables during backup:  The codes below skipped the few tables that may cause security issues such as '0_users', 'seicurity_roles' etc.

$skip_tables_list = Array('users', 'security_roles', 'sql_trail');

    foreach ($skip_tables_list as $skip_table_name) {
        if (($key = array_search($conn["tbpref"].$skip_table_name, $all_tables)) !== false) {
            unset($all_tables[$key]);
        }
    }
// End Skip Files during backup   
   
        // get table structures
    foreach ($all_tables as $table)
    {
        $res1 = db_query("SHOW CREATE TABLE `" . $table['Name'] . "`");
        $tmp = db_fetch($res1);
        $table_sql[$table['Name']] = $tmp["Create Table"];
    }
===========================

$skip_tables_list = Array('users', 'security_roles', 'sql_trail');

foreach ($skip_tables_list as $skip_table_name) {
    if (($key = array_search($conn["tbpref"].$skip_table_name, $all_tables)) !== false) {
        unset($all_tables[$key]);
    }
}

This information has now been wiki-ed.

    $res = db_query("show table status");
    $all_tables = array();
    while($row = db_fetch($res))
    {
        if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
            ($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
            $all_tables[] = $row;
    }

gets the list of tables to be exported into the array variable $all_tables. Now all you need to do is to remove the table names you do not want from them.

To do so, after the above code insert the following:

if (($key = array_search($conn["tbpref"].'users', $all_tables)) !== false) {
    unset($all_tables[$key]);
}
if (($key = array_search($conn["tbpref"].'security_roles', $all_tables)) !== false) {
    unset($all_tables[$key]);
}

I would like to exclude a few tables such as '0_users' or `0_security_roles` when running the backup.  I attempt to make changes to admin/db/maintenance_db.inc to exclude these tables, but due to my limited knowledge in php, I was unable to put this function "--ignore-table" at the right place.

Could someone provide some guidance?

Thanks.

KH