Issues in DataIntegrityTest.php (master) - Issues in master - sunnysideup/silverstripe-dataintegritytests - Measure and Improve Code Quality continuously with Scrutinizer

Issues (62)

Security Analysis not enabled

Cross-Site Scripting

Cross-Site Scripting enables an attacker to inject code into the response of a web-request that is viewed by other users. It can for example be used to bypass access controls, or even to take over other users' accounts.

File Exposure

File Exposure allows an attacker to gain access to local files that he should not be able to access. These files can for example include database credentials, or other configuration files.

File Manipulation

File Manipulation enables an attacker to write custom data to files. This potentially leads to injection of arbitrary code on the server.

Object Injection

Object Injection enables an attacker to inject an object into PHP code, and can lead to arbitrary code execution, file exposure, or file manipulation attacks.

Code Injection

Code Injection enables an attacker to execute arbitrary code on the server.

Response Splitting

Response Splitting can be used to send arbitrary responses.

File Inclusion

File Inclusion enables an attacker to inject custom files into PHP's file loading mechanism, either explicitly passed to include, or for example via PHP's auto-loading mechanism.

Command Injection

Command Injection enables an attacker to inject a shell command that is execute with the privileges of the web-server. This can be used to expose sensitive data, or gain access of your server.

SQL Injection

SQL Injection enables an attacker to execute arbitrary SQL code on your database server gaining access to user data, or manipulating user data.

XPath Injection

XPath Injection enables an attacker to modify the parts of XML document that are read. If that XML document is for example used for authentication, this can lead to further vulnerabilities similar to SQL Injection.

LDAP Injection

LDAP Injection enables an attacker to inject LDAP statements potentially granting permission to run unauthorized queries, or modify content inside the LDAP tree.

Header Injection

Other Vulnerability

This category comprises other attack vectors such as manipulating the PHP runtime, loading custom extensions, freezing the runtime, or similar.

Regex Injection

Regex Injection enables an attacker to execute arbitrary code in your PHP process.

XML Injection

XML Injection enables an attacker to read files on your local filesystem including configuration files, or can be abused to freeze your web-server process.

Variable Injection

Variable Injection enables an attacker to overwrite program variables with custom data, and can lead to further vulnerabilities.

Unfortunately, the security analysis is currently not available for your project. If you are a non-commercial open-source project, please contact support to gain access.

code/DataIntegrityTest.php (3 issues)

Labels

Severity

Informational 3

Upgrade to new PHP Analysis Engine

These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more

<?php


class DataIntegrityTest extends BuildTask
{


    /**
     * standard SS variable
     * @var String
     */
    protected $title = "Check Database Integrity";

    /**
     * standard SS variable
     * @var String
     */
    protected $description = "Go through all fields in the database and work out what fields are superfluous.";

    private static $warning = "are you sure - this step is irreversible! - MAKE SURE TO MAKE A BACKUP OF YOUR DATABASE BEFORE YOU CONFIRM THIS!";

    private static $test_array = array(
        "In SiteTree_Live but not in SiteTree" =>
        "SELECT SiteTree.ID, SiteTree.Title FROM SiteTree_Live RIGHT JOIN SiteTree ON SiteTree_Live.ID = SiteTree.ID WHERE SiteTree.ID IS NULL;",
        "ParentID does not exist in SiteTree" =>
            "SELECT SiteTree.ID, SiteTree.Title FROM SiteTree RIGHT JOIN SiteTree Parent ON SiteTree.ParentID = Parent.ID Where SiteTree.ID IS NULL and SiteTree.ParentID <> 0;",
        "ParentID does not exists in SiteTree_Live" =>
            "SELECT SiteTree_Live.ID, SiteTree_Live.Title FROM SiteTree_Live RIGHT JOIN SiteTree_Live Parent ON SiteTree_Live.ParentID = Parent.ID Where SiteTree_Live.ID IS NULL and SiteTree_Live.ParentID <> 0;",
    );

    private static $global_exceptions = array(
        "EditableFormField" => "Version",
        "EditableOption" => "Version",
        "OrderItem" => "Version"
    );

    /**
    *@param array = should be provided as follows: array("Member.UselessField1", "Member.UselessField2", "SiteTree.UselessField3")
    */
    private static $fields_to_delete = array();

    private static $allowed_actions = array(
        "obsoletefields" => "ADMIN",
        "deleteonefield" => "ADMIN",
        "deletemarkedfields" => "ADMIN",
        "deleteobsoletetables" => "ADMIN",
        "deleteallversions" => "ADMIN",
        "cleanupdb" => "ADMIN"
    );


    public function init()
    {
        //this checks security
        parent::init();
    }

    public function run($request)
    {
        ini_set('max_execution_time', 3000);
        if ($action = $request->getVar("do")) {
            $methodArray = explode("/", $action);
            $method = $methodArray[0];
            $allowedActions = Config::inst()->get("DataIntegrityTest", "allowed_actions");
            if (isset($allowedActions[$method])) {
                if ($method == "obsoletefields") {
                    $deletesafeones = $fixbrokendataobjects = $deleteall = false;
                    if (isset($_GET["deletesafeones"]) && $_GET["deletesafeones"]) {
                        $deletesafeones = true;
                    }
                    if (isset($_GET["fixbrokendataobjects"]) && $_GET["fixbrokendataobjects"]) {
                        $fixbrokendataobjects = true;
                    }
                    if (isset($_GET["deleteall"]) && $_GET["deleteall"]) {
                        $deleteall = true;
                    }
                    return $this->$method($deletesafeones, $fixbrokendataobjects, $deleteall);
                } else {
                    return $this->$method();
                }
            } else {
                user_error("could not find method: $method");
            }
        }
        $warning = Config::inst()->get("DataIntegrityTest", "warning");
        echo "<h2>Database Administration Helpers</h2>";
        echo "<p><a href=\"".$this->Link()."?do=obsoletefields\">Prepare a list of obsolete fields.</a></p>";
        echo "<p><a href=\"".$this->Link()."?do=obsoletefields&amp;deletesafeones=1\" onclick=\"return confirm('".$warning."');\">Prepare a list of obsolete fields and DELETE! obsolete fields without any data.</a></p>";
        echo "<p><a href=\"".$this->Link()."?do=obsoletefields&amp;fixbrokendataobjects=1\" onclick=\"return confirm('".$warning."');\">Fix broken dataobjects.</a></p>";
        echo "<p><a href=\"".$this->Link()."?do=obsoletefields&amp;deleteall=1\" onclick=\"return confirm('".$warning."');\">Delete all obsolete fields now!</a></p>";
        echo "<hr />";
        echo "<p><a href=\"".$this->Link()."?do=deletemarkedfields\" onclick=\"return confirm('".$warning."');\">Delete fields listed in _config.</a></p>";
        echo "<hr />";
        echo "<p><a href=\"".$this->Link()."?do=deleteobsoletetables\" onclick=\"return confirm('".$warning."');\">Delete all tables that are marked as obsolete</a></p>";
        echo "<hr />";
        echo "<p><a href=\"".$this->Link()."?do=deleteallversions\" onclick=\"return confirm('".$warning."');\">Delete all versioned data</a></p>";
        echo "<hr />";
        echo "<p><a href=\"".$this->Link()."?do=cleanupdb\" onclick=\"return confirm('".$warning."');\">Clean up Database (remove obsolete records)</a></p>";
        echo "<hr />";
        echo "<p><a href=\"/dev/tasks/DataIntegrityTestInnoDB/\" onclick=\"return confirm('".$warning."');\">Set all tables to innoDB</a></p>";
        echo "<p><a href=\"/dev/tasks/DataIntegrityTestUTF8/\" onclick=\"return confirm('".$warning."');\">Set all tables to utf-8</a></p>";
    }

    protected function Link()
    {
        return "/dev/tasks/DataIntegrityTest/";
    }

    protected function obsoletefields($deleteSafeOnes = false, $fixBrokenDataObject = false, $deleteAll = false)
    {
        increase_time_limit_to(600);
        $dataClasses = ClassInfo::subclassesFor('DataObject');
        $notCheckedArray = array();
        $canBeSafelyDeleted = array();
        //remove dataobject
        array_shift($dataClasses);
        $rows = DB::query("SHOW TABLES;");
        $actualTables = array();
        if ($rows) {
            foreach ($rows as $key => $item) {
                foreach ($item as $table) {
                    $actualTables[$table] = $table;
                }
            }
        }
        echo "<h1>Report of fields that may not be required.</h1>";
        echo "<p>NOTE: it may contain fields that are actually required (e.g. versioning or many-many relationships) and it may also leave out some obsolete fields.  Use as a guide only.</p>";
        foreach ($dataClasses as $dataClass) {
            // Check if class exists before trying to instantiate - this sidesteps any manifest weirdness
            if (class_exists($dataClass)) {
                $dataObject = $dataClass::create();
                if (!($dataObject instanceof TestOnly)) {
                    $requiredFields = $this->swapArray(DataObject::database_fields($dataObject->ClassName));
                    if (count($requiredFields)) {
                        foreach ($requiredFields as $field) {
                            if (!$dataObject->hasOwnTableDatabaseField($field)) {
                                DB::alteration_message("  **** $dataClass.$field DOES NOT EXIST BUT IT SHOULD BE THERE!", "deleted");
                            }
                        }
                        $actualFields = $this->swapArray(DB::fieldList($dataClass));
                        if ($actualFields) {
                            foreach ($actualFields as $actualField) {
                                if ($deleteAll) {
                                    $link = " !!!!!!!!!!! DELETED !!!!!!!!!";
                                } else {
                                    $warning = Config::inst()->get("DataIntegrityTest", "warning");
                                    $link = "<a href=\"".Director::absoluteBaseURL()."dev/tasks/DataIntegrityTest/?do=deleteonefield/".$dataClass."/".$actualField."/\" onclick=\"return confirm('".$warning."');\">delete field</a>";
                                }
                                if (!in_array($actualField, array("ID", "Version"))) {
                                    if (!in_array($actualField, $requiredFields)) {
                                        $distinctCount = DB::query("SELECT COUNT(DISTINCT \"$actualField\") FROM \"$dataClass\" WHERE \"$actualField\" IS NOT NULL AND \"$actualField\" <> '' AND \"$actualField\" <> '0';")->value();
                                        DB::alteration_message("<br /><br />\n\n$dataClass.$actualField $link - unique entries: $distinctCount", "deleted");
                                        if ($distinctCount) {
                                            $rows = DB::query("
                                                SELECT \"$actualField\" as N, COUNT(\"$actualField\") as C
                                                FROM \"$dataClass\"
                                                GROUP BY \"$actualField\"
                                                ORDER BY C DESC
                                                LIMIT 7");
                                            if ($rows) {
                                                foreach ($rows as $row) {
                                                    DB::alteration_message(" &nbsp; &nbsp; &nbsp; ".$row["C"].": ".$row["N"]);
                                                }
                                            }
                                        } else {
                                            if (!isset($canBeSafelyDeleted[$dataClass])) {
                                                $canBeSafelyDeleted[$dataClass] = array();
                                            }
                                            $canBeSafelyDeleted[$dataClass][$actualField] = "$dataClass.$actualField";
                                        }
                                        if ($deleteAll || ($deleteSafeOnes && $distinctCount == 0)) {
                                            $this->deleteField($dataClass, $actualField);
                                        }
                                    }
                                }
                                if ($actualField == "Version" && !in_array($actualField, $requiredFields)) {
                                    $versioningPresent = $dataObject->hasVersioning();
                                    if (!$versioningPresent) {
                                        DB::alteration_message("$dataClass.$actualField $link", "deleted");
                                        if ($deleteAll) {
                                            $this->deleteField($dataClass, $actualField);
                                        }
                                    }
                                }
                            }
                        }
                        $rawCount = DB::query("SELECT COUNT(\"ID\") FROM \"$dataClass\"")->value();
                        Versioned::set_reading_mode("Stage.Stage");
                        $realCount = 0;
                        $allSubClasses = array_unique(array($dataClass)+ClassInfo::subclassesFor($dataClass));
                        $objects = $dataClass::get()->filter(array("ClassName" =>  $allSubClasses));
                        if ($objects->count()) {
                            $realCount = $objects->count();
                        }
                        if ($rawCount != $realCount) {
                            echo "<hr />";
                            $sign = " > ";
                            if ($rawCount < $realCount) {
                                $sign = " < ";
                            }
                            DB::alteration_message("The DB Table Row Count does not seem to match the DataObject Count for <strong>$dataClass ($rawCount $sign $realCount)</strong>.  This could indicate an error as generally these numbers should match.", "deleted");
                            if ($fixBrokenDataObject) {
                                $objects = $dataClass::get()->where("LinkedTable.ID IS NULL")->leftJoin($dataClass, "$dataClass.ID = LinkedTable.ID", "LinkedTable");
                                if ($objects->count() > 500) {
                                    DB::alteration_message("It is recommended that you manually fix the difference in real vs object count in $dataClass. There are more than 500 records so it would take too long to do it now.", "deleted");
                                } else {
                                    DB::alteration_message("Now trying to recreate missing items... COUNT = ".$objects->count(), "created");
                                    foreach ($objects as $object) {
                                        if (DB::query("SELECT COUNT(\"ID\") FROM \"$dataClass\" WHERE \"ID\" = ".$object->ID.";")->value() != 1) {
                                            Config::inst()->update('DataObject', 'validation_enabled', false);
                                            $object->write(true, false, true, false);
                                            Config::inst()->update('DataObject', 'validation_enabled', true);
                                        }
                                    }
                                    $objectCount = $dataClass::get()->count();
                                    DB::alteration_message("Consider deleting superfluous records from table $dataClass .... COUNT =".($rawCount - $objectCount));
                                    $ancestors = ClassInfo::ancestry($dataClass, true);
                                    if ($ancestors && is_array($ancestors) && count($ancestors)) {
                                        foreach ($ancestors as $ancestor) {
                                            if ($ancestor != $dataClass) {
                                                echo "DELETE `$dataClass`.* FROM `$dataClass` LEFT JOIN `$ancestor` ON `$dataClass`.`ID` = `$ancestor`.`ID` WHERE `$ancestor`.`ID` IS NULL;";
                                                DB::query("DELETE `$dataClass`.* FROM `$dataClass` LEFT JOIN `$ancestor` ON `$dataClass`.`ID` = `$ancestor`.`ID` WHERE `$ancestor`.`ID` IS NULL;");
                                            }
                                        }
                                    }
                                }
                            }
                            echo "<hr />";
                        }
                        unset($actualTables[$dataClass]);
                    } else {
                        $db = DB::getConn();
                        if ($db->hasTable($dataClass)) {
                            DB::alteration_message("  **** The $dataClass table exists, but according to the data-scheme it should not be there ", "deleted");
                        } else {
                            $notCheckedArray[] = $dataClass;
                        }
                    }
                }
            }
        }

        if (count($canBeSafelyDeleted)) {
            DB::alteration_message("<h2>Can be safely deleted: </h2>");
            foreach ($canBeSafelyDeleted as $table => $fields) {
                DB::alteration_message($table.": ".implode(", ", $fields));
            }
        }

        if (count($notCheckedArray)) {
            echo "<h3>Did not check the following classes as no fields appear to be required and hence there is no database table.</h3>";
            foreach ($notCheckedArray as $table) {
                if (DB::query("SHOW TABLES LIKE '".$table."'")->value()) {

                    DB::alteration_message($table ." - NOTE: a table exists for this Class, this is an unexpected result", "deleted");
                } else {
                    DB::alteration_message($table, "created");
                }
            }
        }

        if (count($actualTables)) {
            echo "<h3>Other Tables in Database not directly linked to a Silverstripe DataObject:</h3>";
            foreach ($actualTables as $table) {
                $remove = true;
                if (class_exists($table)) {
                    $classExistsMessage = " a PHP class with this name exists.";
                    $obj = singleton($table);
                    //not sure why we have this.
                    if ($obj instanceof DataExtension) {
                        $remove = false;
                    } elseif (class_exists("Versioned") && $obj->hasExtension("Versioned")) {
                        $remove = false;
                    }
                } else {
                    $classExistsMessage = " NO PHP class with this name exists.";
                    if (substr($table, -5) == "_Live") {
                        $remove = false;
                    }
                    if (substr($table, -9) == "_versions") {
                        $remove = false;
                    }
                    //many 2 many tables...
                    if (strpos($table, "_")) {
                        $class = explode("_", $table);
                        $manyManyClass = substr($table, 0, strrpos($table, '_'));
                        $manyManyExtension = substr($table, strrpos($table, '_') + 1 - strlen($table));
                        if (class_exists($manyManyClass)) {
                            $manyManys = Config::inst()->get($manyManyClass, "many_many");
                            if (isset($manyManys[$manyManyExtension])) {
                                $remove = false;
                            }
                        }
                    }
                }
                if ($remove) {
                    if (substr($table, 0, strlen("_obsolete_")) != "_obsolete_") {
                        $rowCount = DB::query("SELECT COUNT(*) FROM $table")->value();
                        DB::alteration_message($table.", rows ".$rowCount);
                        $obsoleteTableName = "_obsolete_".$table;
                        if (!$this->tableExists($obsoleteTableName)) {
                            DB::alteration_message("We recommend deleting $table or making it obsolete by renaming it to ".$obsoleteTableName, "deleted");
                            if ($deleteAll) {
                                DB::getConn()->renameTable($table, $obsoleteTableName);
                            } else {
                                DB::alteration_message($table." - ".$classExistsMessage." It can be moved to _obsolete_".$table.".", "created");
                            }
                        } else {
                            DB::alteration_message("I'd recommend to move <strong>$table</strong> to <strong>".$obsoleteTableName."</strong>, but that table already exists", "deleted");
                        }
                    }
                }
            }
        }

        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }



    public function deletemarkedfields()
    {
        $fieldsToDelete = Config::inst()->get("DataIntegrityTest", "fields_to_delete");
        if (is_array($fieldsToDelete)) {
            if (count($fieldsToDelete)) {
                foreach ($fieldsToDelete as $key => $tableDotField) {
                    $tableFieldArray = explode(".", $tableDotField);
                    $this->deleteField($tableFieldArray[0], $tableFieldArray[1]);
                }
            } else {
                DB::alteration_message("there are no fields to delete", "created");
            }
        } else {
            user_error("you need to select these fields to be deleted first (DataIntegrityTest.fields_to_delete)");
        }
        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }

    public function deleteonefield()
    {
        $requestExploded = explode("/", $_GET["do"]);
        if (!isset($requestExploded[1])) {
            user_error("no table has been specified", E_USER_WARNING);
        }
        if (!isset($requestExploded[2])) {
            user_error("no field has been specified", E_USER_WARNING);
        }
        $table = $requestExploded[1];
        $field = $requestExploded[2];
        if ($this->deleteField($table, $field)) {
            DB::alteration_message("successfully deleted $field from $table now");
        } else {
            DB::alteration_message("COULD NOT delete $field from $table now", "deleted");
        }
        DB::alteration_message("<a href=\"".Director::absoluteURL("dev/tasks/DataIntegrityTest/?do=obsoletefields")."\">return to list of obsolete fields</a>", "created");
        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }

    private function cleanupdb()
    {
        $obj = new DatabaseAdmin();
        $obj->cleanup();
        DB::alteration_message("============= COMPLETED =================", "");
        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }

    private function deleteField($table, $field)
    {
        $fields = $this->swapArray(DB::fieldList($table));
        $globalExeceptions = Config::inst()->get("DataIntegrityTest", "global_exceptions");
        if (count($globalExeceptions)) {
            foreach ($globalExeceptions as $exceptionTable => $exceptionField) {
                if ($exceptionTable == $table && $exceptionField == $field) {
                    DB::alteration_message("tried to delete $table.$field but this is listed as a global exception and can not be deleted", "created");
                    return false;
                }
            }
        }
        if (!DB::query("SHOW TABLES LIKE '".$table."'")->value()) {

            DB::alteration_message("tried to delete $table.$field but TABLE does not exist", "deleted");
            return false;
        }
        if (!class_exists($table)) {
            DB::alteration_message("tried to delete $table.$field but CLASS does not exist", "deleted");
            return false;
        }
        if (!in_array($field, $fields)) {

            DB::alteration_message("tried to delete $table.$field but FIELD does not exist", "deleted");
            return false;
        } else {
            DB::alteration_message("Deleting $field in $table", "deleted");
            DB::query('ALTER TABLE "'.$table.'" DROP "'.$field.'";');
            $obj = singleton($table);
            //to do: make this more reliable - checking for versioning rather than SiteTree
            if ($obj instanceof SiteTree) {
                DB::query('ALTER TABLE "'.$table.'_Live" DROP "'.$field.'";');
                DB::alteration_message("Deleted $field in {$table}_Live", "deleted");
                DB::query('ALTER TABLE "'.$table.'_versions" DROP "'.$field.'";');
                DB::alteration_message("Deleted $field in {$table}_versions", "deleted");
            }
            return true;
        }
    }

    private function swapArray($array)
    {
        $newArray = array();
        if (is_array($array)) {
            foreach ($array as $key => $value) {
                $newArray[] = $key;
            }
        }
        return $newArray;
    }

    protected function hasVersioning($dataObject)
    {
        $versioningPresent = false;
        $array = $dataObject->stat('extensions');
        if (is_array($array) && count($array)) {
            if (in_array("Versioned('Stage', 'Live')", $array)) {
                $versioningPresent = true;
            }
        }
        if ($dataObject->stat('versioning')) {
            $versioningPresent = true;
        }
        return $versioningPresent;
    }


    private function deleteobsoletetables()
    {
        $tables = DB::query('SHOW tables');
        $unique = array();
        foreach ($tables as $table) {
            $table = array_pop($table);
            if (substr($table, 0, 10) == "_obsolete_") {
                DB::alteration_message("Removing table $table", "deleted");
                DB::query("DROP TABLE \"$table\" ");
            }
        }
        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }

    private function deleteallversions()
    {
        $tables = DB::query('SHOW tables');
        $unique = array();
        foreach ($tables as $table) {
            $table = array_pop($table);
            $endOfTable = substr($table, -9);
            if ($endOfTable == "_versions") {
                $className = substr($table, 0, strlen($table) - 9);
                if (class_exists($className)) {
                    $obj = DataObject::get_one($className);
                    if ($obj) {
                        if ($obj->hasExtension("Versioned")) {
                            DB::alteration_message("Removing all records from $table", "created");
                            DB::query("DELETE FROM \"$table\" ");
                        }
                    }
                } else {
                    DB::alteration_message("Could not find $className class... the $table may be obsolete", "deleted");
                }
            }
        }
        echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
    }

    private function tableExists($table)
    {
        $db = DB::getConn();
        return $db->hasTable($table);
    }
}


1		<?php
2
3
4		class DataIntegrityTest extends BuildTask
5		{
6
7
8		/**
9		* standard SS variable
10		* @var String
11		*/
12		protected $title = "Check Database Integrity";
13
14		/**
15		* standard SS variable
16		* @var String
17		*/
18		protected $description = "Go through all fields in the database and work out what fields are superfluous.";
19
20		private static $warning = "are you sure - this step is irreversible! - MAKE SURE TO MAKE A BACKUP OF YOUR DATABASE BEFORE YOU CONFIRM THIS!";
21
22		private static $test_array = array(
23		"In SiteTree_Live but not in SiteTree" =>
24		"SELECT SiteTree.ID, SiteTree.Title FROM SiteTree_Live RIGHT JOIN SiteTree ON SiteTree_Live.ID = SiteTree.ID WHERE SiteTree.ID IS NULL;",
25		"ParentID does not exist in SiteTree" =>
26		"SELECT SiteTree.ID, SiteTree.Title FROM SiteTree RIGHT JOIN SiteTree Parent ON SiteTree.ParentID = Parent.ID Where SiteTree.ID IS NULL and SiteTree.ParentID <> 0;",
27		"ParentID does not exists in SiteTree_Live" =>
28		"SELECT SiteTree_Live.ID, SiteTree_Live.Title FROM SiteTree_Live RIGHT JOIN SiteTree_Live Parent ON SiteTree_Live.ParentID = Parent.ID Where SiteTree_Live.ID IS NULL and SiteTree_Live.ParentID <> 0;",
29		);
30
31		private static $global_exceptions = array(
32		"EditableFormField" => "Version",
33		"EditableOption" => "Version",
34		"OrderItem" => "Version"
35		);
36
37		/**
38		*@param array = should be provided as follows: array("Member.UselessField1", "Member.UselessField2", "SiteTree.UselessField3")
39		*/
40		private static $fields_to_delete = array();
41
42		private static $allowed_actions = array(
43		"obsoletefields" => "ADMIN",
44		"deleteonefield" => "ADMIN",
45		"deletemarkedfields" => "ADMIN",
46		"deleteobsoletetables" => "ADMIN",
47		"deleteallversions" => "ADMIN",
48		"cleanupdb" => "ADMIN"
49		);
50
51
52		public function init()
53		{
54		//this checks security
55		parent::init();
56		}
57
58		public function run($request)
59		{
60		ini_set('max_execution_time', 3000);
61		if ($action = $request->getVar("do")) {
62		$methodArray = explode("/", $action);
63		$method = $methodArray[0];
64		$allowedActions = Config::inst()->get("DataIntegrityTest", "allowed_actions");
65		if (isset($allowedActions[$method])) {
66		if ($method == "obsoletefields") {
67		$deletesafeones = $fixbrokendataobjects = $deleteall = false;
68		if (isset($_GET["deletesafeones"]) && $_GET["deletesafeones"]) {
69		$deletesafeones = true;
70		}
71		if (isset($_GET["fixbrokendataobjects"]) && $_GET["fixbrokendataobjects"]) {
72		$fixbrokendataobjects = true;
73		}
74		if (isset($_GET["deleteall"]) && $_GET["deleteall"]) {
75		$deleteall = true;
76		}
77		return $this->$method($deletesafeones, $fixbrokendataobjects, $deleteall);
78		} else {
79		return $this->$method();
80		}
81		} else {
82		user_error("could not find method: $method");
83		}
84		}
85		$warning = Config::inst()->get("DataIntegrityTest", "warning");
86		echo "<h2>Database Administration Helpers</h2>";
87		echo "<p><a href=\"".$this->Link()."?do=obsoletefields\">Prepare a list of obsolete fields.</a></p>";
88		echo "<p><a href=\"".$this->Link()."?do=obsoletefields&deletesafeones=1\" onclick=\"return confirm('".$warning."');\">Prepare a list of obsolete fields and DELETE! obsolete fields without any data.</a></p>";
89		echo "<p><a href=\"".$this->Link()."?do=obsoletefields&fixbrokendataobjects=1\" onclick=\"return confirm('".$warning."');\">Fix broken dataobjects.</a></p>";
90		echo "<p><a href=\"".$this->Link()."?do=obsoletefields&deleteall=1\" onclick=\"return confirm('".$warning."');\">Delete all obsolete fields now!</a></p>";
91		echo "<hr />";
92		echo "<p><a href=\"".$this->Link()."?do=deletemarkedfields\" onclick=\"return confirm('".$warning."');\">Delete fields listed in _config.</a></p>";
93		echo "<hr />";
94		echo "<p><a href=\"".$this->Link()."?do=deleteobsoletetables\" onclick=\"return confirm('".$warning."');\">Delete all tables that are marked as obsolete</a></p>";
95		echo "<hr />";
96		echo "<p><a href=\"".$this->Link()."?do=deleteallversions\" onclick=\"return confirm('".$warning."');\">Delete all versioned data</a></p>";
97		echo "<hr />";
98		echo "<p><a href=\"".$this->Link()."?do=cleanupdb\" onclick=\"return confirm('".$warning."');\">Clean up Database (remove obsolete records)</a></p>";
99		echo "<hr />";
100		echo "<p><a href=\"/dev/tasks/DataIntegrityTestInnoDB/\" onclick=\"return confirm('".$warning."');\">Set all tables to innoDB</a></p>";
101		echo "<p><a href=\"/dev/tasks/DataIntegrityTestUTF8/\" onclick=\"return confirm('".$warning."');\">Set all tables to utf-8</a></p>";
102		}
103
104		protected function Link()
105		{
106		return "/dev/tasks/DataIntegrityTest/";
107		}
108
109		protected function obsoletefields($deleteSafeOnes = false, $fixBrokenDataObject = false, $deleteAll = false)
110		{
111		increase_time_limit_to(600);
112		$dataClasses = ClassInfo::subclassesFor('DataObject');
113		$notCheckedArray = array();
114		$canBeSafelyDeleted = array();
115		//remove dataobject
116		array_shift($dataClasses);
117		$rows = DB::query("SHOW TABLES;");
118		$actualTables = array();
119		if ($rows) {
120		foreach ($rows as $key => $item) {
121		foreach ($item as $table) {
122		$actualTables[$table] = $table;
123		}
124		}
125		}
126		echo "<h1>Report of fields that may not be required.</h1>";
127		echo "<p>NOTE: it may contain fields that are actually required (e.g. versioning or many-many relationships) and it may also leave out some obsolete fields. Use as a guide only.</p>";
128		foreach ($dataClasses as $dataClass) {
129		// Check if class exists before trying to instantiate - this sidesteps any manifest weirdness
130		if (class_exists($dataClass)) {
131		$dataObject = $dataClass::create();
132		if (!($dataObject instanceof TestOnly)) {
133		$requiredFields = $this->swapArray(DataObject::database_fields($dataObject->ClassName));
134		if (count($requiredFields)) {
135		foreach ($requiredFields as $field) {
136		if (!$dataObject->hasOwnTableDatabaseField($field)) {
137		DB::alteration_message(" **** $dataClass.$field DOES NOT EXIST BUT IT SHOULD BE THERE!", "deleted");
138		}
139		}
140		$actualFields = $this->swapArray(DB::fieldList($dataClass));
141		if ($actualFields) {
142		foreach ($actualFields as $actualField) {
143		if ($deleteAll) {
144		$link = " !!!!!!!!!!! DELETED !!!!!!!!!";
145		} else {
146		$warning = Config::inst()->get("DataIntegrityTest", "warning");
147		$link = "<a href=\"".Director::absoluteBaseURL()."dev/tasks/DataIntegrityTest/?do=deleteonefield/".$dataClass."/".$actualField."/\" onclick=\"return confirm('".$warning."');\">delete field</a>";
148		}
149		if (!in_array($actualField, array("ID", "Version"))) {
150		if (!in_array($actualField, $requiredFields)) {
151		$distinctCount = DB::query("SELECT COUNT(DISTINCT \"$actualField\") FROM \"$dataClass\" WHERE \"$actualField\" IS NOT NULL AND \"$actualField\" <> '' AND \"$actualField\" <> '0';")->value();
152		DB::alteration_message("<br /><br />\n\n$dataClass.$actualField $link - unique entries: $distinctCount", "deleted");
153		if ($distinctCount) {
154		$rows = DB::query("
155		SELECT \"$actualField\" as N, COUNT(\"$actualField\") as C
156		FROM \"$dataClass\"
157		GROUP BY \"$actualField\"
158		ORDER BY C DESC
159		LIMIT 7");
160		if ($rows) {
161		foreach ($rows as $row) {
162		DB::alteration_message("       ".$row["C"].": ".$row["N"]);
163		}
164		}
165		} else {
166		if (!isset($canBeSafelyDeleted[$dataClass])) {
167		$canBeSafelyDeleted[$dataClass] = array();
168		}
169		$canBeSafelyDeleted[$dataClass][$actualField] = "$dataClass.$actualField";
170		}
171		if ($deleteAll \|\| ($deleteSafeOnes && $distinctCount == 0)) {
172		$this->deleteField($dataClass, $actualField);
173		}
174		}
175		}
176		if ($actualField == "Version" && !in_array($actualField, $requiredFields)) {
177		$versioningPresent = $dataObject->hasVersioning();
178		if (!$versioningPresent) {
179		DB::alteration_message("$dataClass.$actualField $link", "deleted");
180		if ($deleteAll) {
181		$this->deleteField($dataClass, $actualField);
182		}
183		}
184		}
185		}
186		}
187		$rawCount = DB::query("SELECT COUNT(\"ID\") FROM \"$dataClass\"")->value();
188		Versioned::set_reading_mode("Stage.Stage");
189		$realCount = 0;
190		$allSubClasses = array_unique(array($dataClass)+ClassInfo::subclassesFor($dataClass));
191		$objects = $dataClass::get()->filter(array("ClassName" => $allSubClasses));
192		if ($objects->count()) {
193		$realCount = $objects->count();
194		}
195		if ($rawCount != $realCount) {
196		echo "<hr />";
197		$sign = " > ";
198		if ($rawCount < $realCount) {
199		$sign = " < ";
200		}
201		DB::alteration_message("The DB Table Row Count does not seem to match the DataObject Count for <strong>$dataClass ($rawCount $sign $realCount)</strong>. This could indicate an error as generally these numbers should match.", "deleted");
202		if ($fixBrokenDataObject) {
203		$objects = $dataClass::get()->where("LinkedTable.ID IS NULL")->leftJoin($dataClass, "$dataClass.ID = LinkedTable.ID", "LinkedTable");
204		if ($objects->count() > 500) {
205		DB::alteration_message("It is recommended that you manually fix the difference in real vs object count in $dataClass. There are more than 500 records so it would take too long to do it now.", "deleted");
206		} else {
207		DB::alteration_message("Now trying to recreate missing items... COUNT = ".$objects->count(), "created");
208		foreach ($objects as $object) {
209		if (DB::query("SELECT COUNT(\"ID\") FROM \"$dataClass\" WHERE \"ID\" = ".$object->ID.";")->value() != 1) {
210		Config::inst()->update('DataObject', 'validation_enabled', false);
211		$object->write(true, false, true, false);
212		Config::inst()->update('DataObject', 'validation_enabled', true);
213		}
214		}
215		$objectCount = $dataClass::get()->count();
216		DB::alteration_message("Consider deleting superfluous records from table $dataClass .... COUNT =".($rawCount - $objectCount));
217		$ancestors = ClassInfo::ancestry($dataClass, true);
218		if ($ancestors && is_array($ancestors) && count($ancestors)) {
219		foreach ($ancestors as $ancestor) {
220		if ($ancestor != $dataClass) {
221		echo "DELETE `$dataClass`.* FROM `$dataClass` LEFT JOIN `$ancestor` ON `$dataClass`.`ID` = `$ancestor`.`ID` WHERE `$ancestor`.`ID` IS NULL;";
222		DB::query("DELETE `$dataClass`.* FROM `$dataClass` LEFT JOIN `$ancestor` ON `$dataClass`.`ID` = `$ancestor`.`ID` WHERE `$ancestor`.`ID` IS NULL;");
223		}
224		}
225		}
226		}
227		}
228		echo "<hr />";
229		}
230		unset($actualTables[$dataClass]);
231		} else {
232		$db = DB::getConn();
233		if ($db->hasTable($dataClass)) {
234		DB::alteration_message(" **** The $dataClass table exists, but according to the data-scheme it should not be there ", "deleted");
235		} else {
236		$notCheckedArray[] = $dataClass;
237		}
238		}
239		}
240		}
241		}
242
243		if (count($canBeSafelyDeleted)) {
244		DB::alteration_message("<h2>Can be safely deleted: </h2>");
245		foreach ($canBeSafelyDeleted as $table => $fields) {
246		DB::alteration_message($table.": ".implode(", ", $fields));
247		}
248		}
249
250		if (count($notCheckedArray)) {
251		echo "<h3>Did not check the following classes as no fields appear to be required and hence there is no database table.</h3>";
252		foreach ($notCheckedArray as $table) {
253	View Code Duplication	if (DB::query("SHOW TABLES LIKE '".$table."'")->value()) {
		0 ignored issues – show Duplication introduced 2019-05-14 03:46 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
254		DB::alteration_message($table ." - NOTE: a table exists for this Class, this is an unexpected result", "deleted");
255		} else {
256		DB::alteration_message($table, "created");
257		}
258		}
259		}
260
261		if (count($actualTables)) {
262		echo "<h3>Other Tables in Database not directly linked to a Silverstripe DataObject:</h3>";
263		foreach ($actualTables as $table) {
264		$remove = true;
265		if (class_exists($table)) {
266		$classExistsMessage = " a PHP class with this name exists.";
267		$obj = singleton($table);
268		//not sure why we have this.
269		if ($obj instanceof DataExtension) {
270		$remove = false;
271		} elseif (class_exists("Versioned") && $obj->hasExtension("Versioned")) {
272		$remove = false;
273		}
274		} else {
275		$classExistsMessage = " NO PHP class with this name exists.";
276		if (substr($table, -5) == "_Live") {
277		$remove = false;
278		}
279		if (substr($table, -9) == "_versions") {
280		$remove = false;
281		}
282		//many 2 many tables...
283		if (strpos($table, "_")) {
284		$class = explode("_", $table);
285		$manyManyClass = substr($table, 0, strrpos($table, '_'));
286		$manyManyExtension = substr($table, strrpos($table, '_') + 1 - strlen($table));
287		if (class_exists($manyManyClass)) {
288		$manyManys = Config::inst()->get($manyManyClass, "many_many");
289		if (isset($manyManys[$manyManyExtension])) {
290		$remove = false;
291		}
292		}
293		}
294		}
295		if ($remove) {
296		if (substr($table, 0, strlen("_obsolete_")) != "_obsolete_") {
297		$rowCount = DB::query("SELECT COUNT(*) FROM $table")->value();
298		DB::alteration_message($table.", rows ".$rowCount);
299		$obsoleteTableName = "_obsolete_".$table;
300		if (!$this->tableExists($obsoleteTableName)) {
301		DB::alteration_message("We recommend deleting $table or making it obsolete by renaming it to ".$obsoleteTableName, "deleted");
302		if ($deleteAll) {
303		DB::getConn()->renameTable($table, $obsoleteTableName);
304		} else {
305		DB::alteration_message($table." - ".$classExistsMessage." It can be moved to _obsolete_".$table.".", "created");
306		}
307		} else {
308		DB::alteration_message("I'd recommend to move <strong>$table</strong> to <strong>".$obsoleteTableName."</strong>, but that table already exists", "deleted");
309		}
310		}
311		}
312		}
313		}
314
315		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
316		}
317
318
319
320		public function deletemarkedfields()
321		{
322		$fieldsToDelete = Config::inst()->get("DataIntegrityTest", "fields_to_delete");
323		if (is_array($fieldsToDelete)) {
324		if (count($fieldsToDelete)) {
325		foreach ($fieldsToDelete as $key => $tableDotField) {
326		$tableFieldArray = explode(".", $tableDotField);
327		$this->deleteField($tableFieldArray[0], $tableFieldArray[1]);
328		}
329		} else {
330		DB::alteration_message("there are no fields to delete", "created");
331		}
332		} else {
333		user_error("you need to select these fields to be deleted first (DataIntegrityTest.fields_to_delete)");
334		}
335		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
336		}
337
338		public function deleteonefield()
339		{
340		$requestExploded = explode("/", $_GET["do"]);
341		if (!isset($requestExploded[1])) {
342		user_error("no table has been specified", E_USER_WARNING);
343		}
344		if (!isset($requestExploded[2])) {
345		user_error("no field has been specified", E_USER_WARNING);
346		}
347		$table = $requestExploded[1];
348		$field = $requestExploded[2];
349		if ($this->deleteField($table, $field)) {
350		DB::alteration_message("successfully deleted $field from $table now");
351		} else {
352		DB::alteration_message("COULD NOT delete $field from $table now", "deleted");
353		}
354		DB::alteration_message("<a href=\"".Director::absoluteURL("dev/tasks/DataIntegrityTest/?do=obsoletefields")."\">return to list of obsolete fields</a>", "created");
355		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
356		}
357
358		private function cleanupdb()
359		{
360		$obj = new DatabaseAdmin();
361		$obj->cleanup();
362		DB::alteration_message("============= COMPLETED =================", "");
363		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
364		}
365
366		private function deleteField($table, $field)
367		{
368		$fields = $this->swapArray(DB::fieldList($table));
369		$globalExeceptions = Config::inst()->get("DataIntegrityTest", "global_exceptions");
370		if (count($globalExeceptions)) {
371		foreach ($globalExeceptions as $exceptionTable => $exceptionField) {
372		if ($exceptionTable == $table && $exceptionField == $field) {
373		DB::alteration_message("tried to delete $table.$field but this is listed as a global exception and can not be deleted", "created");
374		return false;
375		}
376		}
377		}
378	View Code Duplication	if (!DB::query("SHOW TABLES LIKE '".$table."'")->value()) {
		0 ignored issues – show Duplication introduced 2019-05-14 03:46 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
379		DB::alteration_message("tried to delete $table.$field but TABLE does not exist", "deleted");
380		return false;
381		}
382		if (!class_exists($table)) {
383		DB::alteration_message("tried to delete $table.$field but CLASS does not exist", "deleted");
384		return false;
385		}
386	View Code Duplication	if (!in_array($field, $fields)) {
		0 ignored issues – show Duplication introduced 2019-05-14 03:46 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
387		DB::alteration_message("tried to delete $table.$field but FIELD does not exist", "deleted");
388		return false;
389		} else {
390		DB::alteration_message("Deleting $field in $table", "deleted");
391		DB::query('ALTER TABLE "'.$table.'" DROP "'.$field.'";');
392		$obj = singleton($table);
393		//to do: make this more reliable - checking for versioning rather than SiteTree
394		if ($obj instanceof SiteTree) {
395		DB::query('ALTER TABLE "'.$table.'_Live" DROP "'.$field.'";');
396		DB::alteration_message("Deleted $field in {$table}_Live", "deleted");
397		DB::query('ALTER TABLE "'.$table.'_versions" DROP "'.$field.'";');
398		DB::alteration_message("Deleted $field in {$table}_versions", "deleted");
399		}
400		return true;
401		}
402		}
403
404		private function swapArray($array)
405		{
406		$newArray = array();
407		if (is_array($array)) {
408		foreach ($array as $key => $value) {
409		$newArray[] = $key;
410		}
411		}
412		return $newArray;
413		}
414
415		protected function hasVersioning($dataObject)
416		{
417		$versioningPresent = false;
418		$array = $dataObject->stat('extensions');
419		if (is_array($array) && count($array)) {
420		if (in_array("Versioned('Stage', 'Live')", $array)) {
421		$versioningPresent = true;
422		}
423		}
424		if ($dataObject->stat('versioning')) {
425		$versioningPresent = true;
426		}
427		return $versioningPresent;
428		}
429
430
431		private function deleteobsoletetables()
432		{
433		$tables = DB::query('SHOW tables');
434		$unique = array();
435		foreach ($tables as $table) {
436		$table = array_pop($table);
437		if (substr($table, 0, 10) == "_obsolete_") {
438		DB::alteration_message("Removing table $table", "deleted");
439		DB::query("DROP TABLE \"$table\" ");
440		}
441		}
442		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
443		}
444
445		private function deleteallversions()
446		{
447		$tables = DB::query('SHOW tables');
448		$unique = array();
449		foreach ($tables as $table) {
450		$table = array_pop($table);
451		$endOfTable = substr($table, -9);
452		if ($endOfTable == "_versions") {
453		$className = substr($table, 0, strlen($table) - 9);
454		if (class_exists($className)) {
455		$obj = DataObject::get_one($className);
456		if ($obj) {
457		if ($obj->hasExtension("Versioned")) {
458		DB::alteration_message("Removing all records from $table", "created");
459		DB::query("DELETE FROM \"$table\" ");
460		}
461		}
462		} else {
463		DB::alteration_message("Could not find $className class... the $table may be obsolete", "deleted");
464		}
465		}
466		}
467		echo "<a href=\"".Director::absoluteURL("/dev/tasks/DataIntegrityTest/")."\">back to main menu.</a>";
468		}
469
470		private function tableExists($table)
471		{
472		$db = DB::getConn();
473		return $db->hasTable($table);
474		}
475		}
476

sunnysideup / silverstripe-dataintegritytests

Issues (62)

Security Analysis not enabled

code/DataIntegrityTest.php (3 issues)

Labels

Severity

Introduced By

Upgrade to new PHP Analysis Engine

Duplication Side-by-Side

Filter issues like