Show PHP code in Drupal 7 database for auditing

audit_d7_database_code.drush.php

<?php

/**
 * (c) 2019 Jyri-Petteri Paloposki, jyri-petteri.paloposki@iki.fi
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.

 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * The script is meant to be run with drush php-script.
 */

if (!module_exists('php')) {
  echo 'Congratulations! Your site doesn\'t have the php module enabled, which means that this script won\'t find anything.
Note that it\'s still possible some contrib modules execute PHP code from the database without the php module.'. PHP_EOL;
  exit;
}

$q = 'SELECT format
FROM {filter}
WHERE name = \'php_code\'
 AND status = 1';
$res = db_query($q);
$php_formats = $res->fetchCol();
$php_formats_in = "'" . implode("', '", $php_formats) . "'";

if (!empty($php_formats)) {
  echo '### CHECKING FIELD VALUES FOR EMBEDDED PHP CODE ###' . PHP_EOL;
  $q = 'SELECT field_name
  FROM {field_config}
  WHERE type = \'text_with_summary\'';
  $res = db_query($q);
  if ($res) {
    $formatted_fields = $res->fetchCol();
    foreach ($formatted_fields as $field) {
      $q = 'SELECT entity_type, entity_id, ' . $field . '_value AS code
        FROM {field_data_' . $field . '}
	WHERE deleted != 0
	 AND ' . $field . '_format IN (' . $php_formats_in . ')';
      $res = db_query($q);
      foreach ($res as $row) {
        echo '# PHP in ' . $row->entity_type . ' entity ' . $row->entity_id . ':' . PHP_EOL . $row->code . PHP_EOL . PHP_EOL;
      }
    }
  }

  echo '### CHECKING CUSTOM BLOCK CONTENTS FOR EMBEDDED PHP CODE ###' . PHP_EOL;
  // Block contents.
  $q = 'SELECT bid, body
    FROM {block_custom}
    WHERE format IN (' . $php_formats_in . ')';
  $res = db_query($q);
  foreach ($res as $row) {
    echo '# PHP in custom block ' . $row->bid . ':' . PHP_EOL . $row->body . PHP_EOL . PHP_EOL;
  }
}

echo '### CHECKING BLOCK VISIBILITY SETTINGS FOR PHP CODE ###' . PHP_EOL;
// Block visibilities.
$q = 'SELECT bid, module, delta, pages
  FROM {block}
  WHERE visibility = 2';
$res = db_query($q);
foreach ($res as $row) {
  echo '# PHP in block #' . $row->bid . ' (' . $row->module . ':' . $row->delta . ') visibility settings:' . PHP_EOL . $row->pages . PHP_EOL . PHP_EOL;
}

if (module_exists('views')) {
  echo '### CHECKING VIEWS FOR PHP OUTPUT ###' . PHP_EOL;
  $q = 'SELECT vv.name, vd.id, vd.display_options
    FROM {views_display} vd
    JOIN {views_view} vv
     ON vd.vid = vv.vid
    WHERE vd.display_options LIKE \'%php_output%\'';
  $res = db_query($q);
  foreach ($res as $row) {
    echo '# PHP in Views display ' . $row->name . ':' . $row->id . ':' . PHP_EOL;
    $data = unserialize($row->display_options);
    foreach ($data['fields'] as $name => $field) {
      if (isset($field['php_output'])) {
        echo '# Field ' . $name . PHP_EOL . $field['php_output'] . PHP_EOL . PHP_EOL;
      }
    }
  }
}

if (module_exists('googleanalytics')) {
  echo '### CHECKING GOOGLE ANALYTICS MODULE FOR PHP CONDITION ###' . PHP_EOL;
  if (variable_get('googleanalytics_visibility_pages', NULL) == 2) {
    echo 'Google Analytics visibility PHP code:' . PHP_EOL . variable_get('googleanalytics_pages', '<Not defined after all?') . PHP_EOL . PHP_EOL;
  }
}

echo '### FINALLY, THESE ARE PROBABLY THE USERS ALLOWED TO MODIFY PHP CODE:' . PHP_EOL;
$php_permissions = array('use PHP for tracking visibility', 'use PHP for settings');
foreach ($php_formats as $format) {
  $php_permissions[] = 'use text format ' . $format;
}
$php_roles = array();
foreach ($php_permissions as $permission) {
  $php_roles = $php_roles + user_roles(FALSE, $permission);
}
$q = 'SELECT u.name
  FROM {users_roles} ur
  JOIN {users} u
   ON u.uid = ur.uid
    AND u.status = 1
  WHERE ur.rid IN (' . implode(', ', array_keys($php_roles)) . ')';
$res = db_query($q);
echo implode(PHP_EOL, $res->fetchCol()) . PHP_EOL . PHP_EOL;

echo 'Note: This script might not find all occurences of PHP code in your database depending on the contrib modules you have in use.
Please check the script and make sure it covers all your modules! The script is missing at least the i18n_select module.' . PHP_EOL;