PHP DB::prepared_query方法代码示例

 public function testSaveWithArrayValueSet()
 {
     $article = $this->objFromFixture('CheckboxSetFieldTest_Article', 'articlewithouttags');
     $articleWithTags = $this->objFromFixture('CheckboxSetFieldTest_Article', 'articlewithtags');
     $tag1 = $this->objFromFixture('CheckboxSetFieldTest_Tag', 'tag1');
     $tag2 = $this->objFromFixture('CheckboxSetFieldTest_Tag', 'tag2');
     /* Create a CheckboxSetField with 2 items selected.  Note that the array is a list of values */
     $field = new CheckboxSetField("Tags", "Test field", DataObject::get("CheckboxSetFieldTest_Tag")->map());
     $field->setValue(array($tag1->ID, $tag2->ID));
     /* Saving should work */
     $field->saveInto($article);
     $this->assertEquals(array($tag1->ID, $tag2->ID), DB::prepared_query("SELECT \"CheckboxSetFieldTest_TagID\"\n\t\t\t\tFROM \"CheckboxSetFieldTest_Article_Tags\"\n\t\t\t\tWHERE \"CheckboxSetFieldTest_Article_Tags\".\"CheckboxSetFieldTest_ArticleID\" = ?", array($article->ID))->column(), 'Data shold be saved into CheckboxSetField manymany relation table on the "right end"');
     $this->assertEquals(array($articleWithTags->ID, $article->ID), DB::query("SELECT \"CheckboxSetFieldTest_ArticleID\"\n\t\t\t\tFROM \"CheckboxSetFieldTest_Article_Tags\"\n\t\t\t\tWHERE \"CheckboxSetFieldTest_Article_Tags\".\"CheckboxSetFieldTest_TagID\" = {$tag1->ID}\n\t\t\t")->column(), 'Data shold be saved into CheckboxSetField manymany relation table on the "left end"');
 }

 public function testFilterByNull()
 {
     $list = DataObjectTest_Fan::get();
     // Force DataObjectTest_Fan/fan5::Email to empty string
     $fan5id = $this->idFromFixture('DataObjectTest_Fan', 'fan5');
     DB::prepared_query("UPDATE \"DataObjectTest_Fan\" SET \"Email\" = '' WHERE \"ID\" = ?", array($fan5id));
     // Filter by null email
     $nullEmails = $list->filter('Email', null);
     $this->assertDOSEquals(array(array('Name' => 'Stephen'), array('Name' => 'Mitch')), $nullEmails);
     // Filter by non-null
     $nonNullEmails = $list->filter('Email:not', null);
     $this->assertDOSEquals(array(array('Name' => 'Damian', 'Email' => 'damian@thefans.com'), array('Name' => 'Richard', 'Email' => 'richie@richers.com'), array('Name' => 'Hamish')), $nonNullEmails);
     // Filter by empty only
     $emptyOnly = $list->filter('Email', '');
     $this->assertDOSEquals(array(array('Name' => 'Hamish')), $emptyOnly);
     // Non-empty only. This should include null values, since ExactMatchFilter works around
     // the caveat that != '' also excludes null values in ANSI SQL-92 behaviour.
     $nonEmptyOnly = $list->filter('Email:not', '');
     $this->assertDOSEquals(array(array('Name' => 'Damian', 'Email' => 'damian@thefans.com'), array('Name' => 'Richard', 'Email' => 'richie@richers.com'), array('Name' => 'Stephen'), array('Name' => 'Mitch')), $nonEmptyOnly);
     // Filter by many including null, empty string, and non-empty
     $items1 = $list->filter('Email', array(null, '', 'damian@thefans.com'));
     $this->assertDOSEquals(array(array('Name' => 'Damian', 'Email' => 'damian@thefans.com'), array('Name' => 'Stephen'), array('Name' => 'Mitch'), array('Name' => 'Hamish')), $items1);
     // Filter exclusion of above list
     $items2 = $list->filter('Email:not', array(null, '', 'damian@thefans.com'));
     $this->assertDOSEquals(array(array('Name' => 'Richard', 'Email' => 'richie@richers.com')), $items2);
     // Filter by many including empty string and non-empty
     $items3 = $list->filter('Email', array('', 'damian@thefans.com'));
     $this->assertDOSEquals(array(array('Name' => 'Damian', 'Email' => 'damian@thefans.com'), array('Name' => 'Hamish')), $items3);
     // Filter by many including empty string and non-empty
     // This also relies no the workaround for null comparison as in the $nonEmptyOnly test
     $items4 = $list->filter('Email:not', array('', 'damian@thefans.com'));
     $this->assertDOSEquals(array(array('Name' => 'Richard', 'Email' => 'richie@richers.com'), array('Name' => 'Stephen'), array('Name' => 'Mitch')), $items4);
     // Filter by many including empty string and non-empty
     // The extra null check isn't necessary, but check that this doesn't fail
     $items5 = $list->filterAny(array('Email:not' => array('', 'damian@thefans.com'), 'Email' => null));
     $this->assertDOSEquals(array(array('Name' => 'Richard', 'Email' => 'richie@richers.com'), array('Name' => 'Stephen'), array('Name' => 'Mitch')), $items5);
     // Filter by null or empty values
     $items6 = $list->filter('Email', array(null, ''));
     $this->assertDOSEquals(array(array('Name' => 'Stephen'), array('Name' => 'Mitch'), array('Name' => 'Hamish')), $items6);
 }

//.........这里部分代码省略.........
             echo '<p><b>Creating database</b></p>';
         }
         // Load parameters from existing configuration
         global $databaseConfig;
         if (empty($databaseConfig) && empty($_REQUEST['db'])) {
             user_error("No database configuration available", E_USER_ERROR);
         }
         $parameters = !empty($databaseConfig) ? $databaseConfig : $_REQUEST['db'];
         // Check database name is given
         if (empty($parameters['database'])) {
             user_error("No database name given; please give a value for \$databaseConfig['database']", E_USER_ERROR);
         }
         $database = $parameters['database'];
         // Establish connection and create database in two steps
         unset($parameters['database']);
         DB::connect($parameters);
         DB::create_database($database);
     }
     // Build the database.  Most of the hard work is handled by DataObject
     $dataClasses = ClassInfo::subclassesFor('SilverStripe\\ORM\\DataObject');
     array_shift($dataClasses);
     if (!$quiet) {
         if (Director::is_cli()) {
             echo "\nCREATING DATABASE TABLES\n\n";
         } else {
             echo "\n<p><b>Creating database tables</b></p>\n\n";
         }
     }
     // Initiate schema update
     $dbSchema = DB::get_schema();
     $dbSchema->schemaUpdate(function () use($dataClasses, $testMode, $quiet) {
         foreach ($dataClasses as $dataClass) {
             // Check if class exists before trying to instantiate - this sidesteps any manifest weirdness
             if (!class_exists($dataClass)) {
                 continue;
             }
             // Check if this class should be excluded as per testing conventions
             $SNG = singleton($dataClass);
             if (!$testMode && $SNG instanceof TestOnly) {
                 continue;
             }
             // Log data
             if (!$quiet) {
                 if (Director::is_cli()) {
                     echo " * {$dataClass}\n";
                 } else {
                     echo "<li>{$dataClass}</li>\n";
                 }
             }
             // Instruct the class to apply its schema to the database
             $SNG->requireTable();
         }
     });
     ClassInfo::reset_db_cache();
     if ($populate) {
         if (!$quiet) {
             if (Director::is_cli()) {
                 echo "\nCREATING DATABASE RECORDS\n\n";
             } else {
                 echo "\n<p><b>Creating database records</b></p>\n\n";
             }
         }
         foreach ($dataClasses as $dataClass) {
             // Check if class exists before trying to instantiate - this sidesteps any manifest weirdness
             // Test_ indicates that it's the data class is part of testing system
             if (strpos($dataClass, 'Test_') === false && class_exists($dataClass)) {
                 if (!$quiet) {
                     if (Director::is_cli()) {
                         echo " * {$dataClass}\n";
                     } else {
                         echo "<li>{$dataClass}</li>\n";
                     }
                 }
                 singleton($dataClass)->requireDefaultRecords();
             }
         }
         // Remap obsolete class names
         $schema = DataObject::getSchema();
         foreach ($this->config()->classname_value_remapping as $oldClassName => $newClassName) {
             $badRecordCount = $newClassName::get()->filter(["ClassName" => $oldClassName])->count();
             if ($badRecordCount > 0) {
                 if (Director::is_cli()) {
                     echo " * Correcting {$badRecordCount} obsolete classname values for {$newClassName}\n";
                 } else {
                     echo "<li>Correcting {$badRecordCount} obsolete classname values for {$newClassName}</li>\n";
                 }
                 $table = $schema->baseDataTable($newClassName);
                 DB::prepared_query("UPDATE \"{$table}\" SET \"ClassName\" = ? WHERE \"ClassName\" = ?", [$newClassName, $oldClassName]);
             }
         }
     }
     touch(TEMP_FOLDER . '/database-last-generated-' . str_replace(array('\\', '/', ':'), '.', Director::baseFolder()));
     if (isset($_REQUEST['from_installer'])) {
         echo "OK";
     }
     if (!$quiet) {
         echo Director::is_cli() ? "\n Database build completed!\n\n" : "<p>Database build completed!</p>";
     }
     ClassInfo::reset_db_cache();
 }

 /**
  * Return the number of rows in this query if the limit were removed.  Useful in paged data sets.
  *
  * @param string $column
  * @return int
  */
 public function unlimitedRowCount($column = null)
 {
     // we can't clear the select if we're relying on its output by a HAVING clause
     if (count($this->having)) {
         $records = $this->execute();
         return $records->numRecords();
     }
     $clone = clone $this;
     $clone->limit = null;
     $clone->orderby = null;
     // Choose a default column
     if ($column == null) {
         if ($this->groupby) {
             // @todo Test case required here
             $countQuery = new SQLSelect();
             $countQuery->setSelect("count(*)");
             $countQuery->setFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct'));
             $sql = $countQuery->sql($parameters);
             // $parameters should be empty
             $result = DB::prepared_query($sql, $innerParameters);
             return (int) $result->value();
         } else {
             $clone->setSelect(array("count(*)"));
         }
     } else {
         $clone->setSelect(array("count({$column})"));
     }
     $clone->setGroupBy(array());
     return (int) $clone->execute()->value();
 }

 /**
  * Update the position and parent of a tree node.
  * Only saves the node if changes were made.
  *
  * Required data:
  * - 'ID': The moved node
  * - 'ParentID': New parent relation of the moved node (0 for root)
  * - 'SiblingIDs': Array of all sibling nodes to the moved node (incl. the node itself).
  *   In case of a 'ParentID' change, relates to the new siblings under the new parent.
  *
  * @param HTTPRequest $request
  * @return HTTPResponse JSON string with a
  * @throws HTTPResponse_Exception
  */
 public function savetreenode($request)
 {
     if (!SecurityToken::inst()->checkRequest($request)) {
         return $this->httpError(400);
     }
     if (!Permission::check('SITETREE_REORGANISE') && !Permission::check('ADMIN')) {
         $this->getResponse()->setStatusCode(403, _t('LeftAndMain.CANT_REORGANISE', "You do not have permission to rearange the site tree. Your change was not saved."));
         return;
     }
     $className = $this->stat('tree_class');
     $statusUpdates = array('modified' => array());
     $id = $request->requestVar('ID');
     $parentID = $request->requestVar('ParentID');
     if ($className == 'SilverStripe\\CMS\\Model\\SiteTree' && ($page = DataObject::get_by_id('Page', $id))) {
         $root = $page->getParentType();
         if (($parentID == '0' || $root == 'root') && !SiteConfig::current_site_config()->canCreateTopLevel()) {
             $this->getResponse()->setStatusCode(403, _t('LeftAndMain.CANT_REORGANISE', "You do not have permission to alter Top level pages. Your change was not saved."));
             return;
         }
     }
     $siblingIDs = $request->requestVar('SiblingIDs');
     $statusUpdates = array('modified' => array());
     if (!is_numeric($id) || !is_numeric($parentID)) {
         throw new InvalidArgumentException();
     }
     $node = DataObject::get_by_id($className, $id);
     if ($node && !$node->canEdit()) {
         return Security::permissionFailure($this);
     }
     if (!$node) {
         $this->getResponse()->setStatusCode(500, _t('LeftAndMain.PLEASESAVE', "Please Save Page: This page could not be updated because it hasn't been saved yet."));
         return;
     }
     // Update hierarchy (only if ParentID changed)
     if ($node->ParentID != $parentID) {
         $node->ParentID = (int) $parentID;
         $node->write();
         $statusUpdates['modified'][$node->ID] = array('TreeTitle' => $node->TreeTitle);
         // Update all dependent pages
         if (class_exists('SilverStripe\\CMS\\Model\\VirtualPage')) {
             $virtualPages = VirtualPage::get()->filter("CopyContentFromID", $node->ID);
             foreach ($virtualPages as $virtualPage) {
                 $statusUpdates['modified'][$virtualPage->ID] = array('TreeTitle' => $virtualPage->TreeTitle());
             }
         }
         $this->getResponse()->addHeader('X-Status', rawurlencode(_t('LeftAndMain.REORGANISATIONSUCCESSFUL', 'Reorganised the site tree successfully.')));
     }
     // Update sorting
     if (is_array($siblingIDs)) {
         $counter = 0;
         foreach ($siblingIDs as $id) {
             if ($id == $node->ID) {
                 $node->Sort = ++$counter;
                 $node->write();
                 $statusUpdates['modified'][$node->ID] = array('TreeTitle' => $node->TreeTitle);
             } else {
                 if (is_numeric($id)) {
                     // Nodes that weren't "actually moved" shouldn't be registered as
                     // having been edited; do a direct SQL update instead
                     ++$counter;
                     DB::prepared_query("UPDATE \"{$className}\" SET \"Sort\" = ? WHERE \"ID\" = ?", array($counter, $id));
                 }
             }
         }
         $this->getResponse()->addHeader('X-Status', rawurlencode(_t('LeftAndMain.REORGANISATIONSUCCESSFUL', 'Reorganised the site tree successfully.')));
     }
     return Convert::raw2json($statusUpdates);
 }

 /**
  * Remove invalid records from tables - that is, records that don't have
  * corresponding records in their parent class tables.
  */
 public function cleanup()
 {
     $baseClasses = [];
     foreach (ClassInfo::subclassesFor(DataObject::class) as $class) {
         if (get_parent_class($class) == DataObject::class) {
             $baseClasses[] = $class;
         }
     }
     $schema = DataObject::getSchema();
     foreach ($baseClasses as $baseClass) {
         // Get data classes
         $baseTable = $schema->baseDataTable($baseClass);
         $subclasses = ClassInfo::subclassesFor($baseClass);
         unset($subclasses[0]);
         foreach ($subclasses as $k => $subclass) {
             if (!DataObject::getSchema()->classHasTable($subclass)) {
                 unset($subclasses[$k]);
             }
         }
         if ($subclasses) {
             $records = DB::query("SELECT * FROM \"{$baseTable}\"");
             foreach ($subclasses as $subclass) {
                 $subclassTable = $schema->tableName($subclass);
                 $recordExists[$subclass] = DB::query("SELECT \"ID\" FROM \"{$subclassTable}\"")->keyedColumn();
             }
             foreach ($records as $record) {
                 foreach ($subclasses as $subclass) {
                     $subclassTable = $schema->tableName($subclass);
                     $id = $record['ID'];
                     if ($record['ClassName'] != $subclass && !is_subclass_of($record['ClassName'], $subclass) && isset($recordExists[$subclass][$id])) {
                         $sql = "DELETE FROM \"{$subclassTable}\" WHERE \"ID\" = ?";
                         echo "<li>{$sql} [{$id}]</li>";
                         DB::prepared_query($sql, [$id]);
                     }
                 }
             }
         }
     }
 }

    /**
     * Check if the user has permissions to run URL debug tools,
     * else redirect them to log in.
     */
    public static function require_developer_login()
    {
        if (Director::isDev()) {
            return;
        }
        if (isset($_SESSION['loggedInAs'])) {
            // We have to do some raw SQL here, because this method is called in Object::defineMethods().
            // This means we have to be careful about what objects we create, as we don't want Object::defineMethods()
            // being called again.
            // This basically calls Permission::checkMember($_SESSION['loggedInAs'], 'ADMIN');
            // @TODO - Rewrite safely using DataList::filter
            $memberID = $_SESSION['loggedInAs'];
            $permission = DB::prepared_query('
				SELECT "ID" FROM "Permission"
				INNER JOIN "Group_Members" ON "Permission"."GroupID" = "Group_Members"."GroupID"
				WHERE "Permission"."Code" = ?
				AND "Permission"."Type" = ?
				AND "Group_Members"."MemberID" = ?', array('ADMIN', Permission::GRANT_PERMISSION, $memberID))->value();
            if ($permission) {
                return;
            }
        }
        // This basically does the same as
        // Security::permissionFailure(null, "You need to login with developer access to make use of debugging tools.")
        // We have to do this because of how early this method is called in execution.
        $_SESSION['SilverStripe\\Security\\Security']['Message']['message'] = "You need to login with developer access to make use of debugging tools.";
        $_SESSION['SilverStripe\\Security\\Security']['Message']['type'] = 'warning';
        $_SESSION['BackURL'] = $_SERVER['REQUEST_URI'];
        header($_SERVER['SERVER_PROTOCOL'] . " 302 Found");
        header("Location: " . Director::baseURL() . Security::login_url());
        die;
    }

 /**
  * Check that the given member has the given permission.
  *
  * @param int|Member memberID The ID of the member to check. Leave blank for the current member.
  *  Alternatively you can use a member object.
  * @param string|array $code Code of the permission to check (case-sensitive)
  * @param string $arg Optional argument (e.g. a permissions for a specific page)
  * @param bool $strict Use "strict" checking (which means a permission
  *  will be granted if the key does not exist at all)?
  * @return int|bool The ID of the permission record if the permission
  *  exists; FALSE otherwise. If "strict" checking is
  *  disabled, TRUE will be returned if the permission does not exist at all.
  */
 public static function checkMember($member, $code, $arg = "any", $strict = true)
 {
     if (!$member) {
         $memberID = $member = Member::currentUserID();
     } else {
         $memberID = is_object($member) ? $member->ID : $member;
     }
     if (!$memberID) {
         return false;
     }
     // Turn the code into an array as we may need to add other permsissions to the set we check
     if (!is_array($code)) {
         $code = array($code);
     }
     // Check if admin should be treated as holding all permissions
     $adminImpliesAll = (bool) static::config()->admin_implies_all;
     if ($arg == 'any') {
         // Cache the permissions in memory
         if (!isset(self::$cache_permissions[$memberID])) {
             self::$cache_permissions[$memberID] = self::permissions_for_member($memberID);
         }
         foreach ($code as $permCode) {
             if ($permCode === 'CMS_ACCESS') {
                 foreach (self::$cache_permissions[$memberID] as $perm) {
                     //if they have admin rights OR they have an explicit access to the CMS then give permission
                     if ($adminImpliesAll && $perm == 'ADMIN' || substr($perm, 0, 11) === 'CMS_ACCESS_') {
                         return true;
                     }
                 }
             } elseif (substr($permCode, 0, 11) === 'CMS_ACCESS_' && !in_array('CMS_ACCESS_LeftAndMain', $code)) {
                 //cms_access_leftandmain means access to all CMS areas
                 $code[] = 'CMS_ACCESS_LeftAndMain';
             }
         }
         // if ADMIN has all privileges, then we need to push that code in
         if ($adminImpliesAll) {
             $code[] = "ADMIN";
         }
         // Multiple $code values - return true if at least one matches, ie, intersection exists
         return (bool) array_intersect($code, self::$cache_permissions[$memberID]);
     }
     // Code filters
     $codeParams = is_array($code) ? $code : array($code);
     $codeClause = DB::placeholders($codeParams);
     $adminParams = $adminImpliesAll ? array('ADMIN') : array();
     $adminClause = $adminImpliesAll ? ", ?" : '';
     // The following code should only be used if you're not using the "any" arg.  This is kind
     // of obselete functionality and could possibly be deprecated.
     $groupParams = self::groupList($memberID);
     if (empty($groupParams)) {
         return false;
     }
     $groupClause = DB::placeholders($groupParams);
     // Arg component
     $argClause = "";
     $argParams = array();
     switch ($arg) {
         case "any":
             break;
         case "all":
             $argClause = " AND \"Arg\" = ?";
             $argParams = array(-1);
             break;
         default:
             if (is_numeric($arg)) {
                 $argClause = "AND \"Arg\" IN (?, ?) ";
                 $argParams = array(-1, $arg);
             } else {
                 user_error("Permission::checkMember: bad arg '{$arg}'", E_USER_ERROR);
             }
     }
     // Raw SQL for efficiency
     $permission = DB::prepared_query("SELECT \"ID\"\n\t\t\tFROM \"Permission\"\n\t\t\tWHERE (\n\t\t\t\t\"Code\" IN ({$codeClause} {$adminClause})\n\t\t\t\tAND \"Type\" = ?\n\t\t\t\tAND \"GroupID\" IN ({$groupClause})\n\t\t\t\t{$argClause}\n\t\t\t)", array_merge($codeParams, $adminParams, array(self::GRANT_PERMISSION), $groupParams, $argParams))->value();
     if ($permission) {
         return $permission;
     }
     // Strict checking disabled?
     if (!static::config()->strict_checking || !$strict) {
         $hasPermission = DB::prepared_query("SELECT COUNT(*)\n\t\t\t\tFROM \"Permission\"\n\t\t\t\tWHERE (\n\t\t\t\t\t\"Code\" IN ({$codeClause}) AND\n\t\t\t\t\t\"Type\" = ?\n\t\t\t\t)", array_merge($codeParams, array(self::GRANT_PERMISSION)))->value();
         if (!$hasPermission) {
             return false;
         }
     }
     return false;
 }

 /**
  * Execute this query.
  *
  * @return Query
  */
 public function execute()
 {
     $sql = $this->sql($parameters);
     return DB::prepared_query($sql, $parameters);
 }

 public function encrypt($password, $salt = null, $member = null)
 {
     return DB::prepared_query("SELECT OLD_PASSWORD(?)", array($password))->value();
 }

 /**
  * @param array $params Request params (unsanitized)
  */
 public function __construct($params = null)
 {
     $this->ids = array();
     $this->expanded = array();
     $parents = array();
     $q = $this->getQuery($params);
     $res = $q->execute();
     if (!$res) {
         return;
     }
     // And keep a record of parents we don't need to get parents
     // of themselves, as well as IDs to mark
     foreach ($res as $row) {
         if ($row['ParentID']) {
             $parents[$row['ParentID']] = true;
         }
         $this->ids[$row['ID']] = true;
     }
     // We need to recurse up the tree,
     // finding ParentIDs for each ID until we run out of parents
     while (!empty($parents)) {
         $parentsClause = DB::placeholders($parents);
         $res = DB::prepared_query("SELECT \"ParentID\", \"ID\" FROM \"SiteTree\" WHERE \"ID\" in ({$parentsClause})", array_keys($parents));
         $parents = array();
         foreach ($res as $row) {
             if ($row['ParentID']) {
                 $parents[$row['ParentID']] = true;
             }
             $this->ids[$row['ID']] = true;
             $this->expanded[$row['ID']] = true;
         }
     }
 }

 /**
  * Ensures that the latest version of a record is the expected value
  *
  * @param DataObject $record
  * @param int $version
  */
 protected function assertRecordHasLatestVersion($record, $version)
 {
     foreach (ClassInfo::ancestry(get_class($record), true) as $table) {
         $versionForClass = DB::prepared_query($sql = "SELECT MAX(\"Version\") FROM \"{$table}_versions\" WHERE \"RecordID\" = ?", array($record->ID))->value();
         $this->assertEquals($version, $versionForClass, "That the table {$table} has the latest version {$version}");
     }
 }

 /**
  * Check if this record exists on the draft stage
  *
  * @return bool
  */
 public function isOnDraft()
 {
     $owner = $this->owner;
     if (!$owner->isInDB()) {
         return false;
     }
     $table = $this->baseTable();
     $result = DB::prepared_query("SELECT COUNT(*) FROM \"{$table}\" WHERE \"{$table}\".\"ID\" = ?", array($owner->ID));
     return (bool) $result->value();
 }