Exemple #1
0
/**
 * Output a sql page for database.
 *
 */
function db_admin_sql()
{
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $sql = $_POST['sql'];
    } else {
        $sql = db_sql('table_list');
    }
    list($micro, $second) = explode(' ', microtime());
    $time_start = $micro + $second;
    $resource = db_query($sql);
    list($micro, $second) = explode(' ', microtime());
    $time_end = $micro + $second;
    $_view['time'] = ceil(($time_end - $time_start) * 10000) / 10000;
    $_view['sql'] = $sql;
    if ($sql === db_sql('table_list')) {
        $head = '';
        $body = '';
        $results = db_result($resource);
        $head .= '<tr>';
        $head .= '<th>name</th>';
        if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
            $head .= '<th>engine</th>';
            $head .= '<th>rows</th>';
            $head .= '<th>collation</th>';
            $head .= '<th>comment</th>';
        }
        $head .= '<th>create</th>';
        $head .= '<th>columns</th>';
        if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
            $head .= '<th>alter</th>';
        }
        $head .= '<th>drop</th>';
        $head .= '<th>insert</th>';
        $head .= '<th>delete</th>';
        $head .= '<th>select</th>';
        $head .= '</tr>';
        foreach ($results as $result) {
            $table = array_shift($result);
            if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                $create = 'SHOW CREATE TABLE';
                $define = 'SHOW COLUMNS';
            } elseif (DATABASE_TYPE === 'pdo_pgsql' || DATABASE_TYPE === 'pgsql') {
                $create = 'create';
                $define = 'columns';
            } elseif (DATABASE_TYPE === 'pdo_sqlite' || DATABASE_TYPE === 'pdo_sqlite2' || DATABASE_TYPE === 'sqlite') {
                $create = 'SELECT sql';
                $define = 'PRAGMA TABLE_INFO';
            }
            $create_sql = db_sql('table_create', $table);
            $define_sql = db_sql('table_define', $table);
            $create_sql = preg_replace('/"/', '&quot;', $create_sql);
            $define_sql = preg_replace('/"/', '&quot;', $define_sql);
            $define_resource = db_query($define_sql);
            $define_results = db_result($define_resource);
            $insert_keys = array();
            $insert_values = array();
            foreach ($define_results as $define_result) {
                if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                    $insert_keys[] = $define_result['Field'];
                    $insert_values[] = $define_result['Null'] === 'YES' ? 'NULL' : '\\\'\\\'';
                } elseif (DATABASE_TYPE === 'pdo_pgsql' || DATABASE_TYPE === 'pgsql') {
                    $insert_keys[] = $define_result['column_name'];
                    $insert_values[] = $define_result['is_nullable'] === 'YES' ? 'NULL' : '\\\'\\\'';
                } elseif (DATABASE_TYPE === 'pdo_sqlite' || DATABASE_TYPE === 'pdo_sqlite2' || DATABASE_TYPE === 'sqlite') {
                    $insert_keys[] = $define_result['name'];
                    $insert_values[] = $define_result['notnull'] === 0 ? 'NULL' : '\\\'\\\'';
                }
            }
            $body .= '<tr>';
            $body .= '<td><span style="font-family:monospace;">' . $table . '</span></td>';
            if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                $body .= '<td><span style="font-family:monospace;">' . $result['Engine'] . '</span></td>';
                $body .= '<td><span style="font-family:monospace;">' . $result['Rows'] . '</span></td>';
                $body .= '<td><span style="font-family:monospace;">' . $result['Collation'] . '</span></td>';
                $body .= '<td><span style="font-family:monospace;">' . $result['Comment'] . '</span></td>';
            }
            $body .= '<td><a href="javascript:insertSQL(\'' . str_replace('\'', '\\\'', $create_sql) . '\');">' . $create . '</a></td>';
            $body .= '<td><a href="javascript:insertSQL(\'' . str_replace('\'', '\\\'', $define_sql) . '\');">' . $define . '</a></td>';
            if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                $body .= '<td><a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' COMMENT \\\'\\\';\');">ALTER TABLE</a></td>';
            }
            $body .= '<td><a href="javascript:insertSQL(\'DROP TABLE ' . $table . ';\');">DROP TABLE</a></td>';
            $body .= '<td><a href="javascript:insertSQL(\'INSERT INTO ' . $table . '(' . implode(',', $insert_keys) . ') VALUES(' . implode(',', $insert_values) . ');\');">INSERT</a></td>';
            $body .= '<td><a href="javascript:insertSQL(\'DELETE FROM ' . $table . ';\');">DELETE</a></td>';
            $body .= '<td><a href="javascript:insertSQL(\'SELECT * FROM ' . $table . ' LIMIT 100;\');">SELECT</a></td>';
            $body .= '</tr>';
        }
        $_view['result'] = '<table summary="result">' . $head . $body . '</table>';
        $_view['count'] = db_count($resource);
    } elseif (regexp_match('^(SELECT|SHOW|EXPLAIN|DESC|PRAGMA)', $sql)) {
        $head = '';
        $body = '';
        $flag = false;
        if ($regexp = regexp_match('^SELECT \\* FROM ([_a-zA-Z0-9\\-]+)', $sql)) {
            $table = $regexp[1];
            $link = true;
        } elseif ($regexp = regexp_match('^' . db_sql('table_define', '([_a-zA-Z0-9\\-]+)'), $sql)) {
            $table = $regexp[1];
            $link = false;
        } else {
            $table = null;
            $link = false;
        }
        $results = db_result($resource);
        foreach ($results as $result) {
            $first_key = null;
            $first_value = null;
            $body .= '<tr>';
            foreach ($result as $key => $value) {
                if ($first_key === null) {
                    $first_key = $key;
                    $first_value = $value;
                }
                if (is_string($key)) {
                    if ($value === null) {
                        $value_sql = 'NULL';
                        $value_html = '<em>NULL</em>';
                    } else {
                        $value_sql = str_replace('\\', '\\\\\\\\', $value);
                        $value_sql = str_replace("\n", '\\n', $value_sql);
                        $value_sql = str_replace('"', '&quot;', $value_sql);
                        if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                            $value_sql = str_replace('\'', '\\\\\\\'', $value_sql);
                        } else {
                            $value_sql = str_replace('\'', '\\\'\\\'', $value_sql);
                        }
                        $value_sql = '\\\'' . $value_sql . '\\\'';
                        $value_html = h($value, true);
                    }
                    if ($link === false) {
                        $value = $value_html;
                    } else {
                        $value = '<a href="javascript:insertSQL(\'UPDATE ' . $table . ' SET ' . $key . ' = ' . $value_sql . ' WHERE ' . $first_key . ' = \\\'' . $first_value . '\\\';\');">' . truncate($value_html, 100) . '</a>';
                    }
                    $body .= '<td><span style="font-family:monospace;">' . $value . '</span></td>';
                    if ($flag === false) {
                        $head .= '<th>' . h($key, true) . '</th>';
                    }
                }
            }
            if (DATABASE_TYPE === 'pdo_mysql' || DATABASE_TYPE === 'mysql') {
                if (regexp_match('^' . db_sql('table_define', '([_a-zA-Z0-9\\-]+)'), $sql)) {
                    $add_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' ADD field INT(1) NOT NULL COMMENT \\\'\\\' AFTER ' . $result['Field'] . ';\');">ADD</a>';
                    $change_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' CHANGE ' . $result['Field'] . ' ' . $result['Field'] . ' INT(1) NOT NULL COMMENT \\\'\\\';\');">CHANGE</a>';
                    $drop_value = '<a href="javascript:insertSQL(\'ALTER TABLE ' . $table . ' DROP ' . $result['Field'] . ';\');">DROP</a>';
                    $body .= '<td><span style="font-family:monospace;">' . $add_value . ' ' . $change_value . ' ' . $drop_value . '</span></td>';
                    if ($flag === false) {
                        $head .= '<th>alter</th>';
                    }
                }
            }
            $body .= '</tr>';
            $flag = true;
        }
        $_view['result'] = '<table summary="result"><tr>' . $head . '</tr>' . $body . '</table>';
        $_view['count'] = db_count($resource);
    } else {
        $_view['result'] = '<p>OK</p>';
        $_view['count'] = db_affected_count($resource);
    }
    echo "<!DOCTYPE html>\n";
    echo "<html>\n";
    echo "<head>\n";
    echo "<meta charset=\"" . t(MAIN_CHARSET, true) . "\" />\n";
    echo "<title>DB</title>\n";
    style();
    echo "<script>\n";
    echo "function insertSQL(sql)\n";
    echo "{\n";
    echo "    document.getElementById('exec_form').sql.value = sql;\n";
    echo "}";
    echo "</script>\n";
    echo "</head>\n";
    echo "<body>\n";
    echo "<h1><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin\">DB</a></h1>\n";
    echo "<h2>Menu</h2>\n";
    echo "<ul>\n";
    echo "<li>SQL</li>\n";
    echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&amp;_work=import\">Import</a></li>\n";
    echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&amp;_work=export\">Export</a></li>\n";
    if (file_exists(DATABASE_BACKUP_PATH)) {
        echo "<li><a href=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin&amp;_work=backup\">Backup</a></li>\n";
    }
    echo "</ul>\n";
    echo "<h2>SQL</h2>\n";
    echo "<form action=\"" . t(MAIN_FILE, true) . "/?_mode=db_admin\" method=\"post\" id=\"exec_form\">\n";
    echo "<fieldset>\n";
    echo "<legend>execute</legend>\n";
    echo "<dl>\n";
    echo "<dt>SQL</dt>\n";
    echo "<dd><textarea name=\"sql\" cols=\"50\" rows=\"5\">" . t($_view['sql'], true) . "</textarea></dd>\n";
    echo "</dl>\n";
    echo "<p><input type=\"submit\" value=\"execute\" /></p>\n";
    echo "</fieldset>\n";
    echo "</form>\n";
    if ($_view['result']) {
        echo "<h2>Result</h2>\n";
        echo $_view['result'];
    }
    echo "<pre><code>Rows: " . $_view['count'] . " rows.\n";
    echo "Time: " . $_view['time'] . " sec.</code></pre>\n";
    echo "</body>\n";
    echo "</html>\n";
    return;
}
Exemple #2
0
    if (!$resource) {
        error('指定されたユーザが見つかりません。');
    }
    // ユーザを取得
    $users = select_users(array('select' => 'email', 'where' => array('id = :id', array('id' => $_SESSION['auth']['user']['id']))));
    // メール送信内容を作成
    $_view['url'] = $GLOBALS['config']['http_url'] . MAIN_FILE . '/user/activate?email=' . rawurlencode($users[0]['email']) . '&token=' . $token;
    $to = $users[0]['email'];
    $subject = $GLOBALS['config']['mail_subjects']['user/activate'];
    $message = view('mail/user/activate.php', true);
    $headers = $GLOBALS['config']['mail_headers'];
    // メールを送信
    if (service_mail_send($to, $subject, $message, $headers) === false) {
        error('メールを送信できません。');
    }
    // トランザクションを終了
    db_commit();
    // リダイレクト
    redirect('/user/home?ok=send');
} else {
    // ユーザを編集
    $resource = update_users(array('set' => array('email_activated' => 1, 'token' => null, 'token_code' => null, 'token_expire' => null), 'where' => array('email = :email AND token = :token', array('email' => $_GET['email'], 'token' => $_GET['token']))));
    if (!$resource) {
        error('データを編集できません。');
    }
    if (db_affected_count($resource) == 0) {
        error('メールアドレスを確認できません。');
    }
    // リダイレクト
    redirect('/user/home?ok=activate');
}