function getTableList() { global $TABLE_PREFIX, $APP; // get table names $mysqlTables = getMysqlTablesWithPrefix(); $schemaTables = getSchemaTables(); // create multi query $tables = array(); $tableRowCounts = array(); foreach ($schemaTables as $tableName) { $tableNameWithPrefix = getTableNameWithPrefix($tableName); if (in_array($tableNameWithPrefix, $mysqlTables)) { $rowCount = mysql_count($tableNameWithPrefix); } $localTableSchema = loadSchema($tableName); array_push($tables, array('tableName' => $tableName, 'menuName' => @$localTableSchema['menuName'], 'menuType' => @$localTableSchema['menuType'], 'menuOrder' => @$localTableSchema['menuOrder'], 'menuHidden' => @$localTableSchema['menuHidden'], 'tableHidden' => @$localTableSchema['tableHidden'], '_indent' => @$localTableSchema['_indent'], 'recordCount' => $rowCount)); } // sort table list uasort($tables, '_sortMenusByOrder'); // return $tables; }
function _upgradeToVersion1_10() { global $SETTINGS, $APP, $TABLE_PREFIX; if ($SETTINGS['programVersion'] >= '1.10') { return; } ### Update Access Levels _upgradeToVersion1_10_accessLevels(); // update mysql tables, schema, schema preset files $schemaDirs = array(DATA_DIR . '/schema', DATA_DIR . '/schemaPresets'); $fieldsToMaintainOrder = array('num', 'createdDate', 'createdByUserNum', 'updatedDate', 'updatedByUserNum'); foreach ($schemaDirs as $schemaDir) { foreach (getSchemaTables($schemaDir) as $tableName) { $schema = loadSchema($tableName, $schemaDir); $escapedTableName = mysql_escape(getTableNameWithPrefix($tableName)); $isPreset = $schemaDir == DATA_DIR . '/schemaPresets'; // skip tables if ($tableName == 'uploads') { continue; } if ($tableName == '_accesslist') { continue; } // add fields $schema['num']['order'] = "1"; $schema['createdDate'] = array('order' => '2', 'type' => 'none', 'label' => "Created", 'isSystemField' => '1'); $schema['createdByUserNum'] = array('order' => '3', 'type' => 'none', 'label' => "Created By", 'isSystemField' => '1'); $schema['updatedDate'] = array('order' => '4', 'type' => 'none', 'label' => "Last Updated", 'isSystemField' => '1'); $schema['updatedByUserNum'] = array('order' => '5', 'type' => 'none', 'label' => "Last Updated By", 'isSystemField' => '1'); // foreach (array_keys($schema) as $fieldname) { $fieldSchema =& $schema[$fieldname]; if (!is_array($fieldSchema)) { continue; } // fields are stored as arrays, other entries are table metadata, skip metadata if (!in_array($fieldname, $fieldsToMaintainOrder)) { $fieldSchema['order'] = @$fieldSchema['order'] + 6; } ### Change column type for checkbox fields if (@$fieldSchema['type'] == 'checkbox' && !$isPreset) { mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = 0 WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `{$fieldname}` tinyint(1) unsigned NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); } ### Change column type for datetime fields if (@$fieldSchema['type'] == 'date' && !$isPreset) { mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `{$fieldname}` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); } // Rename autoPublish fields if ($fieldname == 'autoPublishStartDate' && !@$schema['publishDate']) { $schema['publishDate'] = $fieldSchema; unset($schema[$fieldname]); if (!$isPreset) { mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `publishDate` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); } } if ($fieldname == 'autoPublishEndDate' && !@$schema['removeDate']) { $schema['removeDate'] = $fieldSchema; unset($schema[$fieldname]); if (!$isPreset) { mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = '0000-00-00 00:00:00' WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `removeDate` datetime NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); } } if ($fieldname == 'autoPublishNeverExpires' && !@$schema['neverRemove']) { $schema['neverRemove'] = $fieldSchema; unset($schema[$fieldname]); if (!$isPreset) { mysql_query("UPDATE `{$escapedTableName}` SET `{$fieldname}` = 0 WHERE `{$fieldname}` IS NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); mysql_query("ALTER TABLE `{$escapedTableName}` CHANGE COLUMN `{$fieldname}` `neverRemove` tinyint(1) unsigned NOT NULL") or die("Mysql error:\n\n" . htmlencode(mysql_error()) . " in " . __FILE__ . ", line " . __LINE__); } } } uasort($schema, '__sortSchemaFieldsByOrder'); // sort schema keys saveSchema($tableName, $schema, $schemaDir); } } // createMissingSchemaTablesAndFields(); // create missing fields clearAlertsAndNotices(); // don't show "created table/field" alerts saveAndRefresh('1.10'); // uncomment this after next update }
function mysql_insert($tableName, $colsToValues, $tempDisableMysqlStrictMode = false) { // $tableName = getTableNameWithPrefix($tableName); $set = mysql_getMysqlSetValues($colsToValues); $insert = "INSERT INTO `{$tableName}` SET {$set}"; // if ($tempDisableMysqlStrictMode) { mysqlStrictMode(false); } mysql_query($insert) or dieAsCaller("MySQL Error: " . mysql_error() . "\n"); $recordNum = mysql_insert_id(); if ($tempDisableMysqlStrictMode) { mysqlStrictMode(true); } return $recordNum; }
function incrementCounterField($tablename, $fieldname, $recordNumber) { global $VIEWER_NAME; // error checking if (!$tablename) { die(__FUNCTION__ . ": No 'tablename' value specified!"); } if (!$fieldname) { die(__FUNCTION__ . ": No 'fieldname' value specified!"); } if (!$recordNumber) { die(__FUNCTION__ . ": No 'recordNumber' value specified!"); } // update counter $escapedTableName = mysql_escape(getTableNameWithPrefix($tablename)); $query = "UPDATE `{$escapedTableName}` SET `{$fieldname}` = IFNULL(`{$fieldname}`,0) + 1"; $query .= " WHERE `num` = '" . mysql_escape($recordNumber) . "'"; $result = @mysql_query($query); if (!$result) { die(__FUNCTION__ . " MySQL Error: " . htmlencode(mysql_error()) . "\n"); } if (!mysql_affected_rows()) { die(__FUNCTION__ . ": Couldn't find record '" . htmlencode($recordNumber) . "'!"); } }
require_once "lib/menus/database/editTable_functions.php"; $tableDetails = getTableDetails(); $errors = getTableDetailErrors($schema); if ($errors) { alert($errors); } showHeader(); ?> <form method="post" action="?" autocomplete="off"> <input type="submit" style="width: 0px; height: 0px; position: absolute; border: none; padding: 0px" /> <!-- bugfix: hitting enter in textfield submits first submit button on form --> <input type="hidden" name="menu" value="database" /> <input type="hidden" name="_defaultAction" value="editTable" /> <input type="hidden" name="tableName" id="tableName" value="<?php echo htmlencode(getTableNameWithPrefix($_REQUEST['tableName'])); ?> " /> <input type="hidden" name="menuOrder" value="<?php echo htmlencode(@$schema['menuOrder']); ?> " /> <?php echo security_getHiddenCsrfTokenField(); ?> <div class="content-box list-tables"> <div class="content-box-header"> <h3> <?php
function getRecord($options) { global $VIEWER_NAME, $TABLE_PREFIX; $VIEWER_NAME = "Page Viewer ({$options['tableName']})"; // error checking $requiredOptions = array('tableName'); $validOptions = array('tableName', 'recordNum', 'where', 'titleField', 'orderBy'); $errors = _getOptionErrors($requiredOptions, $validOptions, $options); if ($errors) { die("{$VIEWER_NAME} errors<br/>\n{$errors}"); } // set defaults $schema = loadSchema($options['tableName']); if (!@$options['recordNum']) { $options['recordNum'] = getLastNumberInUrl(); } if (@$schema['menuType'] == 'single') { $options['recordNum'] = "1"; } // always load record 1 for single menus // get where condition $whereConditions = ''; $escapedRecordNum = mysql_escape((int) $options['recordNum']); if ($options['where']) { $whereConditions = $options['where']; } elseif ($options['recordNum']) { $whereConditions = "num = '{$escapedRecordNum}'"; } // get record $fullTableName = getTableNameWithPrefix($options['tableName']); $escapedTableName = mysql_escape($fullTableName); $where = _addWhereConditionsForSpecialFields($schema, $whereConditions, $options); $orderBy = @$options['orderBy'] ? "ORDER BY {$options['orderBy']}" : ''; $query = "SELECT * FROM `{$escapedTableName}` {$where} {$orderBy} LIMIT 0, 1"; $result = mysql_query($query) or die("{$VIEWER_NAME}: MySQL Error: " . htmlencode(mysql_error()) . "\n"); $record = mysql_fetch_assoc($result); // add _link field if ($record) { $filenameValue = getFilenameFieldValue($record, @$options['titleField']); $record['_link'] = _getLink($_SERVER['SCRIPT_NAME'], $filenameValue, $record['num'], @$options['useSeoUrls']); } // define upload fields if ($record) { foreach ($schema as $fieldname => $fieldSchema) { if (!is_array($fieldSchema)) { continue; } // not a field definition, table metadata field if (@$fieldSchema['type'] != 'upload') { continue; } // skip all but upload fields $record[$fieldname] = "Use getUploads() function to list uploads (See code generator).\n"; } } // return $record; }
function saveTableDetails() { global $TABLE_PREFIX, $schema, $APP, $tableName, $tableNameWithPrefix; $oldSchemaFilepath = DATA_DIR . '/schema/' . getTableNameWithoutPrefix($_REQUEST['tableName']) . ".ini.php"; $newSchemaFilepath = DATA_DIR . '/schema/' . getTableNameWithoutPrefix($_REQUEST['newTableName']) . ".ini.php"; // security_dieUnlessPostForm(); security_dieUnlessInternalReferer(); security_dieOnInvalidCsrfToken(); // disableInDemoMode('', 'database/listTables.php'); // error checking $errors = ''; if ($_REQUEST['newTableName'] == '') { $errors .= "You must specify a tablename!<br/>\n"; } if (preg_match("/dragSortOrder/", @$_REQUEST['listPageFields']) || preg_match("/dragSortOrder/", $_REQUEST['listPageOrder'])) { if (!preg_match("/^dragSortOrder/", @$_REQUEST['listPageFields'])) { $errors .= "If used, dragSortOrder must be the first field in 'ListPage Fields'!<br/>\n"; } if (!preg_match("/^dragSortOrder/", $_REQUEST['listPageOrder'])) { $errors .= "If used, dragSortOrder must be the first field in 'Order By'!<br/>\n"; } } if (@$_REQUEST['tableName'] && !$schema) { $errors .= "Error updating schema file. Please wait a few seconds and try again.<br/>\n"; } if (!is_writable(DATA_DIR . '/schema/')) { $errors .= "Schema dir '/data/schema/' isn't writable. Please update permissions.<br/>\n"; } elseif (!is_writable($oldSchemaFilepath)) { $errors .= "Schema file '/data/schema/" . basename($oldSchemaFilepath) . "' isn't writable. Please update permissions.<br/>\n"; } // v2.53 - require urls to start with scheme:// or / (to ensure links are valid when moving between sites) $fieldNamesToLabels = array(); $fieldNamesToLabels['_listPage'] = 'List Page Url'; $fieldNamesToLabels['_detailPage'] = 'Detail Page Url'; $fieldNamesToLabels['_previewPage'] = 'Preview Page Url'; foreach ($fieldNamesToLabels as $name => $label) { $startsWithHttpOrSlash = preg_match("|^(\\w+:/)?/|", @$_REQUEST[$name]); if (@$_REQUEST[$name] && !$startsWithHttpOrSlash) { $errors .= t("{$label} must start with /") . "<br/>\n"; } } // if ($errors) { alert($errors); return; } // force add table prefix (if not specified) $_REQUEST['newTableName'] = getTableNameWithPrefix($_REQUEST['newTableName']); ### rename table if ($_REQUEST['tableName'] != $_REQUEST['newTableName']) { $error = getTablenameErrors($_REQUEST['newTableName']); if ($error) { alert($error); return; } // rename mysql table $result = mysql_query("RENAME TABLE `" . mysql_escape($_REQUEST['tableName']) . "`\n TO `" . mysql_escape($_REQUEST['newTableName']) . "`") or die("Error renaming MySQL table:\n\n" . htmlencode(mysql_error()) . "\n"); // rename schema file rename_winsafe($oldSchemaFilepath, $newSchemaFilepath) or die("Error renaming schema file!"); // update uploads table with new table name $where = array('tableName' => getTableNameWithoutPrefix($_REQUEST['tableName'])); // old tableName $colsToValues = array('tableName' => getTableNameWithoutPrefix($_REQUEST['newTableName'])); // new tableName $result = mysql_update('uploads', null, $where, $colsToValues); // update tableName form field $_REQUEST['tableName'] = $_REQUEST['newTableName']; // update globals with new tablename $tableName = $_REQUEST['tableName']; // sic $tableNameWithPrefix = $_REQUEST['tableName']; } ### update schema fields $schema['menuName'] = $_REQUEST['menuName']; $schema['_indent'] = @$_REQUEST['_indent']; $schema['menuType'] = $_REQUEST['menuType']; $schema['menuOrder'] = $_REQUEST['menuOrder']; if ($_REQUEST['menuType'] != 'link') { $schema['menuHidden'] = $_REQUEST['menuHidden']; $schema['listPageFields'] = @$_REQUEST['listPageFields']; $schema['listPageOrder'] = $_REQUEST['listPageOrder']; $schema['listPageSearchFields'] = $_REQUEST['listPageSearchFields']; $schema['_perPageDefault'] = @$_REQUEST['_perPageDefault']; $schema['_maxRecords'] = $_REQUEST['_maxRecords']; $schema['_maxRecordsPerUser'] = $_REQUEST['_maxRecordsPerUser']; $schema['_disableAdd'] = $_REQUEST['_disableAdd']; $schema['_disableView'] = $_REQUEST['_disableView']; $schema['_disableModify'] = $_REQUEST['_disableModify']; $schema['_disableErase'] = $_REQUEST['_disableErase']; $schema['_disablePreview'] = $_REQUEST['_disablePreview']; $schema['_filenameFields'] = @$_REQUEST['_filenameFields']; $schema['_listPage'] = @$_REQUEST['_listPage']; $schema['_detailPage'] = $_REQUEST['_detailPage']; $schema['_previewPage'] = $_REQUEST['_previewPage']; $schema['_hideRecordsFromDisabledAccounts'] = $_REQUEST['_hideRecordsFromDisabledAccounts']; $schema['_requiredPlugins'] = @$_REQUEST['_requiredPlugins']; } if ($_REQUEST['menuType'] == 'link') { $schema['_url'] = $_REQUEST['_url']; $schema['_linkTarget'] = @$_REQUEST['_linkTarget']; $schema['_linkMessage'] = @$_REQUEST['_linkMessage']; $schema['_iframeHeight'] = @$_REQUEST['_iframeHeight']; unset($schema['_targetBlank']); // unset old schema value (if it exists) } if ($_REQUEST['menuType'] == 'category') { $schema['_maxDepth'] = $_REQUEST['_maxDepth']; } saveSchema($_REQUEST['tableName'], $schema); // notice("Table details for '" . htmlencode($schema['menuName']) . "' have been saved."); }
function backupDatabase($filenameOrPath = '', $selectedTable = '') { global $TABLE_PREFIX; $prefixPlaceholder = '#TABLE_PREFIX#_'; set_time_limit(60 * 5); // v2.51 - allow up to 5 minutes to backup/restore database session_write_close(); // v2.51 - End the current session and store session data so locked session data doesn't prevent concurrent access to CMS by user while backup in progress // error checking if ($selectedTable != '') { $schemaTables = getSchemaTables(); if (preg_match("/[^\\w\\d\\-\\.]/", $selectedTable)) { die(__FUNCTION__ . " : \$selectedTable contains invalid chars! " . htmlencode($selectedTable)); } if (!in_array($selectedTable, $schemaTables)) { die("Unknown table selected '" . htmlencode($selectedTable) . "'!"); } } // open backup file $hostname = preg_replace('/[^\\w\\d\\-\\.]/', '', @$_SERVER['HTTP_HOST']); if (!$filenameOrPath) { $filenameOrPath = "{$hostname}-v{$GLOBALS['APP']['version']}-" . date('Ymd-His'); if ($selectedTable) { $filenameOrPath .= "-{$selectedTable}"; } $filenameOrPath .= ".sql.php"; } $outputFilepath = isAbsPath($filenameOrPath) ? $filenameOrPath : DATA_DIR . "/backups/{$filenameOrPath}"; // v2.60 if only filename provided, use /data/backup/ as the basedir $fp = @fopen($outputFilepath, 'x'); if (!$fp) { // file already exists - avoid race condition session_start(); return false; } // create no execute php header fwrite($fp, "-- <?php die('This is not a program file.'); exit; ?>\n\n"); # prevent file from being executed // get tablenames to backup if ($selectedTable) { $tablenames = array(getTableNameWithPrefix($selectedTable)); } else { $skippedTables = array('_cron_log', '_error_log', '_outgoing_mail', '_nlb_log'); // don't backup these table names $skippedTables = applyFilters('backupDatabase_skippedTables', $skippedTables); // let users skip tables via plugins $skippedTables = array_map('getTableNameWithPrefix', $skippedTables); // add table_prefix to all table names (if needed) $allTables = getMysqlTablesWithPrefix(); $tablenames = array_diff($allTables, $skippedTables); // remove skipped tables from list } // backup database foreach ($tablenames as $unescapedTablename) { $escapedTablename = mysql_escape($unescapedTablename); $tablenameWithFakePrefix = $prefixPlaceholder . getTableNameWithoutPrefix($escapedTablename); // create table fwrite($fp, "\n--\n"); fwrite($fp, "-- Table structure for table `{$tablenameWithFakePrefix}`\n"); fwrite($fp, "--\n\n"); fwrite($fp, "DROP TABLE IF EXISTS `{$tablenameWithFakePrefix}`;\n\n"); $result = mysql_query("SHOW CREATE TABLE `{$escapedTablename}`"); list(, $createStatement) = mysql_fetch_row($result) or die("MySQL Error: " . htmlencode(mysql_error())); $createStatement = str_replace("TABLE `{$TABLE_PREFIX}", "TABLE `{$prefixPlaceholder}", $createStatement); fwrite($fp, "{$createStatement};\n\n"); if (is_resource($result)) { mysql_free_result($result); } // create rows fwrite($fp, "\n--\n"); fwrite($fp, "-- Dumping data for table `{$tablenameWithFakePrefix}`\n"); fwrite($fp, "--\n\n"); $result = mysql_query("SELECT * FROM `{$escapedTablename}`") or die("MySQL Error: " . htmlencode(mysql_error())); while ($row = mysql_fetch_row($result)) { $values = ''; foreach ($row as $value) { if (is_null($value)) { $values .= 'NULL,'; } else { $values .= '"' . mysql_real_escape_string($value) . '",'; } } $values = chop($values, ','); // remove trailing comma fwrite($fp, "INSERT INTO `{$tablenameWithFakePrefix}` VALUES({$values});\n"); } if (is_resource($result)) { mysql_free_result($result); } } // fwrite($fp, "\n"); $result = fwrite($fp, "-- Dump completed on " . date('Y-m-d H:i:s O') . "\n\n"); if ($result === false) { die(__FUNCTION__ . ": Error writing backup file! {$php_errormsg}"); } fclose($fp) || die(__FUNCTION__ . ": Error closing backup file! {$php_errormsg}"); // @session_start(); // hide error: E_WARNING: session_start(): Cannot send session cache limiter - headers already sent return $outputFilepath; }