Monitoring Drupal for Insecure Settings

30 November -0001

The Drupal content management system (CMS) is a wonderful for maintaining multiple, user driven and owned websites. From a security context, however, Drupal can present a challenge. Much of Drupal's power comes from its high degree of customization and the fact that users need nothing more than a web browser to maintain a website. Drupal is also described as "community plumbing," a driving principle that seeks to include the input of website visitors as contributors. These factors combined make Drupal a perfect target for enterprising attackers who wish to post malicious content, spam, and other undesirable material to your websites. Fortunately Drupal includes several technical safeguards to prevent your websites from being compromised, but much of Drupal customizable power, if utilized incorrectly, can actually assist attackers in hijacking your sites.

There are several "best practice" type configuration changes that you should enforce in your Drupal environment. This becomes problematic, however, when you turn over the administration of a Drupal site to actual users. Drupal allows technical staff to offload much of the burden of traditional "webmaster" type duties to actual users. This can be an incredible time saver, but it can also empower uneducated users to make changes detrimental to overall site, and server, security.

Some wise configurations to make in your Drupal setup include:

  1. Limiting the ability of anonymous users to create accounts. Account creation is the single greatest target of spammers and Google link jackers (people who will try and post links to their own sites from your own simply to promote their search engine rank). Allowing anonymous users to create accounts with no oversight or confirmation encourages the creation of bogus accounts by automated programs, otherwise known as bots. Attackers will look to create accounts in your Drupal site because by default Drupal distinguishes between two types of users: authenticated and unauthenticated users. If an unauthenticated (anonymous) user can create an account they can effectively elevate their own privileges.
  2. Disallow anonymous content posting. You don't want anonymous users to be able to post content for reasons closely linked to #1 above. Without any stopgaps, bots will quickly find your site and flood it with crap content in an effort to parasitically host links to their own sites on yours. Anonymous users shouldn't be able to create content (including content or uploads) without some sort of check or verification.
  3. Delete the PHP input type. Drupal, by default provides a number of different formats that users can utilize when entering data into web forms for things like content creation and comments. The three default formats are full HTML, filtered HTML and PHP. Filtered HTML should be used whenever possible. Filtered HTML will strip out any malicious HTML (such as javascript) to keep your site safe from cross site scripting (xss) and cross site request forgery (xsrf) attacks. The PHP input type is hardly ever used and therefor should be deleted entirely. With PHP input types users can write PHP directly into their content for the server to evaluate. This privilege is exactly what attackers search for, and if you aren't using this capability it's presence presents a much greater danger than help in your Drupal installation.
  4. Disable use of PHP. Similar to number 3 above Drupal offers certain users the ability to input PHP code into web forms and then evaluates (or runs) that code as it presents content. This is a powerful, and rarely used, feature. Attackers, however, can make extremely effective use of PHP to compromise your web server. In general, because PHP is hardly ever used by Drupal administrators or content creators, privileges to use PHP should be disabled. If there is some reason to use PHP inputs then you can enable permissions until the PHP is crafted, then disable them again.
  5. Limit upload file types. Drupal allows users to define the types of files that can be uploaded to the server. Attackers will try to upload PHP code to execute on your server, or perhaps executable malware or other dynamic content. By disallowing scripting languages or executables from being uploaded you cut off this avenue of attack.
  6. 6. Restrict error reporting. Occasionally Drupal code will result in errors. In these cases, by default, the complete debugging text about the error will be logged in Drupal's internal error log, and displayed on the screen for users. Except in development environments where programmers are the users, this information is completely useless to end users. Furthermore, the information contained in debugging messages could leak critical configuration details to attackers (known as an information disclosure vulnerability). Because Drupal system administrators can view error messages in the internal logs there is no reason to display errors to the screen. Turn off screen reporting of errors whenever possible to close this disclosure vulnerability.

All of these are common sense security measures and you can easily tell your users about them and set up your Drupal sites with these checks in place by default. The problem emerges when end users get control of the site. There is no feasible technical way to prevent them from reversing the safeguards you may have put in place. For this reason, an automated checking script comes in handy.

It is possible to write a PHP program that will review all of the databases in a MySQL database server, check each one to discover if it is a Drupal database, determine if it is a Drupal 5 or a Drupal 6 installation, and then check to make sure the installation meets the above security recommendations. The following script requires that the database privileges assigned can read all of the databases in the MySQL installation. This can easily be accomplished with the command:

mysql> GRANT SELECT ON *.* to 'username'@'localhost' identified by 'password';
mysql> flush privileges;

Note that this will create an account that can read the contents of all the databases on your installation, so take care when creating this account and keep in mind that the credentials to access the account are listed in the following PHP script.

The most effective way to schedule the script is to use native task scheduling, such as cron. Running the script daily via cron and having the results e-mailed to a systems administrator is a good deployment strategy. The script could be modified so as to log to syslog or other mechanisms to integrate with log monitoring systems you may already have installed. Note the the script scans all databases and should be able to identify dangerous permissions set in Drupal 5 and Drupal 6. If you have the PHP CLI you can run this script at the command line, otherwise you can place it in a web accessible directory and call it via a web browser.

<?php
/**
 * drupaleyes.php
 * Check for dangerous permissions in Drupal databases.
 * Thanks to Warren Petrofsky for the name DrupalEyes!
 * @author Justin C. Klein Keane <justin@madirish.net>
 * Last modified November 12, 2009
 */

$check = new DrupalEyes();

Class DrupalEyes {
    private $conn;
    
    private $drupal5_dbs;
    
    private $drupal6_dbs;
    
    private $allowed_upload_extensions;
    
    public function __construct() {
        $this->conn = mysql_connect('localhost', 'your_user_account', 'password')
            or die(mysql_error());
        mysql_select_db('information_schema') or die(mysql_error());
        $this->get_drupal_dbs();
        $this->check_unsafe_permissions();
        $this->check_input_formats();
        $this->check_allowed_uploads();
        $this->check_account_creation();
        $this->check_error_display();
    }    
    
    public function __destruct() {
        mysql_close($this->conn);    
    }
    
    /**
     * Anonymous users should not be allowed to create accounts.
     * If they are allowed they should *definitely* be required to
     * verify themselves via e-mail to prevent spambots.  For
     * settings see ?q=admin/user/settings
     */
    private function check_account_creation() {
        
        /**
         * Drupal 5 check
         */
        if (is_array($this->drupal5_dbs)) {
            foreach ($this->drupal5_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name = 'user_email_verification'";
                $drupal5result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5result)) {
                    $values = 0;
                    while ($drupal5check = mysql_fetch_object($drupal5result)) {
                        if ($drupal5check->value == 'b:1;') {
                            //default configuration
                                print "+++Drupal 6 database $database allows anonymous account creation with e-mail verification!\n\n";
                                    
                        }
                        $sql = "select value from $database.variable where name = 'user_register'";
                        $ret = mysql_query($sql) or die(mysql_error());
                        if (is_resource($ret)) {
                            $verify = mysql_fetch_object($ret);
                            if (isset($verify->value) && $verify->value == 's:1:"1";' && $drupal5check->value == 'i:1;') {
                                //anon account but e-mail required to verify
                                print "+++Drupal 5 database $database allows anonymous account creation with e-mail verification!\n\n";
                            }    
                            elseif (isset($verify->value) && $verify->value == 's:1:"1";' && $drupal5check->value == 'i:0;') {
                                //anon account and no e-mail verify!
                                print "+++Drupal 5 database $database allows anonymous account creation WITHOUT verification!\n\n";
                            }    
                        }
                        $values++;
                    }
                    if ($values == 0) { //default configuration
                            //anon account but e-mail required to verify
                            print "+++Drupal 5 database $database allows anonymous account creation with e-mail verification!\n\n";
                    }
                }
            }
        }
        
        /**
         * Drupal 6 check
         */
        if (is_array($this->drupal6_dbs)) {
            foreach ($this->drupal6_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name = 'user_email_verification'";
                $drupal6result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6result)) {
                    while ($drupal6check = mysql_fetch_object($drupal6result)) {
                        if ($drupal6check->value == 'b:1;') {
                            //default configuration
                                print "+++Drupal 6 database $database allows anonymous account creation with e-mail verification!\n\n";
                                    
                        }
                        $sql = "select value from $database.variable where name = 'user_register'";
                        $ret = mysql_query($sql) or die(mysql_error());
                        if (is_resource($ret)) {
                            $verify = mysql_fetch_object($ret);
                            if (isset($verify->value) && $verify->value == 's:1:"1";' && $drupal6check->value == 'i:1;') {
                                //anon account but e-mail required to verify
                                print "+++Drupal 6 database $database allows anonymous account creation with e-mail verification!\n\n";
                            }    
                            elseif (isset($verify->value) && $verify->value == 's:1:"1";' && $drupal6check->value == 'i:0;') {
                                //anon account and no e-mail verify!
                                print "+++Drupal 6 database $database allows anonymous account creation WITHOUT verification!\n\n";
                            }    
                        }
                    }
                }
            }
        }
    }
    
    /**
     * In order for file uploads to be functioning the upload module
     * must be enabled.  For settings see ?q=admin/settings/uploads
     */
    private function check_allowed_uploads() {
        $this->allowed_upload_extensions = explode(' ', "jpg jpeg gif png txt doc xls pdf ppt pps odt ods odp rtf zip csv swf");
        /**
         * Drupal 5 check
         */
        if (is_array($this->drupal5_dbs)) {
            foreach ($this->drupal5_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name like '%upload_extension%'";
                $drupal5result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5result)) {
                    while ($drupal5check = mysql_fetch_object($drupal5result)) {
                        $extensions = substr($drupal5check->value, strpos($drupal5check->value,'"')+1);
                        $extensions = substr($extensions, 0, strpos($extensions,'"'));
                        $extensions = explode(' ', $extensions);
                        foreach ($extensions as $ext) {
                            if (! in_array($ext, $this->allowed_upload_extensions)) {
                                print "+++Drupal 5 database $database seems to allow unsafe file types in upload!\n";
                                print "  Allowed extension " . $ext . "\n\n";
                            }
                        }
                    }
                }
            }
        }
        /**
         * Drupal 6 check
         */
        if (is_array($this->drupal6_dbs)) {
            foreach ($this->drupal6_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name like '%upload_extension%'";
                $drupal6result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6result)) {
                    while ($drupal6check = mysql_fetch_object($drupal6result)) {
                        $extensions = substr($drupal6check->value, strpos($drupal6check->value,'"')+1);
                        $extensions = substr($extensions, 0, strpos($extensions,'"'));
                        $extensions = explode(' ', $extensions);
                        foreach ($extensions as $ext) {
                            if (! in_array($ext, $this->allowed_upload_extensions)) {
                                print "+++Drupal 6 database $database seems to allow unsafe file types in upload!\n";
                                print "  Allowed extension " . $ext . "\n\n";
                            }
                        }
                    }
                }
            }
        }
    }
    
    /**
     * Displaying SQL errors on the screen get be an information
     * disclosure vulnerability.  We don't want this behaviour in
     * production.  Check ?q=admin/settings/error-reporting
     */
    private function check_error_display() {
        /**
         * Drupal 5 check
         */
        if (is_array($this->drupal5_dbs)) {
            foreach ($this->drupal5_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name = 'error_level'";
                $drupal5result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5result)) {
                    $ret = mysql_fetch_object($drupal5result);
                    if (! isset($ret->value) || $ret->value == 's:1:"1";') //unset value is the default that reports to the screen
                        print "+++Drupal 5 database $database has error reporting set to screen display!\n\n";
                }
            }
        }
        /**
         * Drupal 6 check
         */
        if (is_array($this->drupal6_dbs)) {
            foreach ($this->drupal6_dbs as $database) {
                $extensions = '';
                $sql = "select value from $database.variable where name = 'error_level'";
                $drupal6result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6result)) {
                    $ret = mysql_fetch_object($drupal6result);
                    if (! isset($ret->value) || $ret->value == 's:1:"1";') //unset value is the default that reports to the screen
                        print "+++Drupal 6 database $database has error reporting set to screen display!\n\n";
                }
            }
        }
    }
    
    /**
     * In Drupal 5 input format types are stored in the 'filters'
     * table.  Any filter with a delta of 1 is a PHP evaluation
     * enabled filter.  For settings see ?q=admin/settings/filters
     */
    private function check_input_formats() {
        /**
         * Drupal 5 check
         */
        if (is_array($this->drupal5_dbs)) {
            foreach ($this->drupal5_dbs as $database) {
                $sql = "select ff.name from $database.filter_formats ff, $database.filters f where f.delta = 1 and ff.format = f.format;";
                $drupal5result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5result)) {
                    $inputs = '';
                    while ($drupal5check = mysql_fetch_object($drupal5result)) {
                        $inputs .= $drupal5check->name . ", ";
                    }
                    if ($inputs != '') {
                        $inputs = substr($inputs, 0, strlen($inputs)-2);
                        print "+++Drupal 5 database $database seems to have PHP input types enabled!\n";
                        print "  Input types " . $inputs . " allow PHP.\n\n";
                    }
                }
                
            }
        }
        
        /**
         * Drupal 6 check
         */
        if (is_array($this->drupal6_dbs)) {
            foreach ($this->drupal6_dbs as $database) {
                $sql = "select count(name) as count from $database.system where type='module' and name='php' and status='1'";
                $drupal6result = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6result)) {
                    while ($ret = mysql_fetch_object($drupal6result)) {
                        if ($ret->count > 0) {
                            print "+++Drupal 6 database $database seems to have PHP input types enabled!\n\n";
                        }
                    }
                }
                
            }
        }
    } // end check_input_formats()
    
    /**
     * Permissions are set at ?q=admin/user/permissions
     */
    private function check_unsafe_permissions() {
        /**
         * Drupal 5 check for bad access permissions
         */
        if (is_array($this->drupal5_dbs)) {
            foreach ($this->drupal5_dbs as $database) {
                $sql = 'select instr(substr(value, instr(value, \'"update"\'), ' .
                    '(instr(value, \'"per_node"\')-instr(value, \'"update"\'))), \'i:1;\') as check_result ' .
                    'from ' . $database . '.variable where name=\'content_access_settings\'';
                $drupal5AccessQueryResult = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5AccessQueryResult)) {
                    while ($drupal5check = mysql_fetch_object($drupal5AccessQueryResult)) {
                        if ($drupal5check->check_result > 0) {
                            print "+++Drupal 5 database $database seems to have a permissions problem!\n";
                            print " Found dangerous content_access_settings, anonymous user can create or update content.\n\n";    
                        }
                    }
                }
                $sql = "select distinct(perm) as check_result from $database.permission " .
                    "where rid=1 and " .
                    "(perm like '%edit%' or perm like '%delete%' or perm like '%create%' or perm like '%administer%')";
                $drupal5AccessQueryResult = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5AccessQueryResult)) {
                    while ($drupal5check = mysql_fetch_object($drupal5AccessQueryResult)) {
                        if ($drupal5check->check_result > 0) {
                            print "+++Drupal 5 database $database seems to have a permissions problem!\n";
                            print "  Found dangerous permissions for anonymous users:  " . $drupal5check->check_result . "\n\n";        
                        }
                    }
                }
                $sql = "select distinct(p.perm) as check_result, r.name " .
                        " from $database.permission p, $database.role r " .
                    "where r.rid = p.rid and p.perm like '% PHP %'";
                $drupal5AccessQueryResult = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal5AccessQueryResult)) {
                    while ($drupal5check = mysql_fetch_object($drupal5AccessQueryResult)) {
                        if ($drupal5check->check_result > 0) {
                            print "+++Drupal 5 database $database seems to have a permissions problem!\n";
                            print "  Found dangerous PHP permissions in role " . $drupal5check->name . ":  " . $drupal5check->check_result . "\n\n";        
                        }
                    }
                }
            }
        }
        /**
         * Drupal 6 check for bad access permissions
         */
        if (is_array($this->drupal6_dbs)) {
            foreach($this->drupal6_dbs as $database) {
                $sql = "select distinct(perm) as check_result from $database.permission " .
                    "where rid=1 and (perm like '%edit%' or perm like '%delete%' or perm like '%create%' or perm like '%administer%')";
                $drupal6AccessQueryResult = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6AccessQueryResult)) {
                    while ($drupal6check = mysql_fetch_object($drupal6AccessQueryResult)) {
                        print "+++Drupal 6 database $database seems to have a permissions problem!\n";    
                        print "  Found dangerous permissions for anonymous users:  " . $drupal6check->check_result . "\n\n";    
                    }
                }
                $sql = "select distinct(p.perm) as check_result, r.name " .
                        " from $database.permission p, $database.role r " .
                    "where r.rid = p.rid and p.perm like '% PHP %'";
                $drupal6AccessQueryResult = mysql_query($sql) or die(mysql_error());
                if (is_resource($drupal6AccessQueryResult)) {
                    while ($drupal6check = mysql_fetch_object($drupal6AccessQueryResult)) {
                        print "+++Drupal 6 database $database seems to have a permissions problem!\n";    
                        print $sql . "\n";
                        print "  Found dangerous PHP permissions in role " . $drupal6check->name . ":  " . $drupal6check->check_result . "\n\n";    
                    }
                }
            }    
        }
    } // end check_unsafe_permissions()
    
    private function get_drupal_dbs() {
        
        $sql = "SELECT schema_name FROM " .
                "information_schema.schemata WHERE " .
                "schema_name NOT IN ('information_schema','test','mysql')";
        $retval = mysql_query($sql) or die(mysql_error());
        
        while ($row = mysql_fetch_object($retval)) {
            $database = mysql_real_escape_string($row->schema_name);
            /**
             * This query should identify Drupal databases
             */
            $sql = "SELECT COUNT(table_name) as tableCount
                FROM information_schema.tables
                  WHERE table_schema='$database'
                    and (
                      /** Check for some common Drupal table names **/
                      table_name='blocks'
                      or table_name='node'
                      or table_name='node_revisions'
                      or table_name='variable'
                    )";
            $tableCountResult = mysql_query($sql);
            while ($result = mysql_fetch_object($tableCountResult)) {
                if ($result->tableCount == 4) {
                    //we have a drupal database, now get the version
                    $sql = "select count(column_name) as count " .
                            "from information_schema.columns " .
                            "where table_name = 'filters' and column_name = 'fid' and table_schema = '$database'";
                    $val = mysql_query($sql);
                    while ($ret = mysql_fetch_object($val)) {
                        if ($ret->count > 0) $this->drupal6_dbs[] = $database;
                        else $this->drupal5_dbs[] = $database;
                    }
                }
            }
        }
    } // end get_drupal_bds()
}

?>