new templates

defaults fix
security fixes
adding databases?

git-svn-id: svn://presbrey.mit.edu/sql/mitsql@42 a142d4bd-2cfb-0310-9673-cb33a7e74f58

diff --git a/.htaccess b/.htaccess
index 16859fb10eaa1d7d2833455c905afd79d16db12a..c7f7094775ce2229aa36fec66319f915a7222b73 100644 (file)
--- a/.htaccess
+++ b/.htaccess
@@ -1,5 +1,5 @@
+php_value auto_append_file "global.done.php"
 #php_value auto_prepend_file "prepend.php"
-#php_value auto_append_file "global.done.php"
 #php_flag xdebug.remote_enable on
 #php_flag xdebug.remote_autostart on
 #php_value xdebug.remote_host "localhost"

diff --git a/contact.php b/contact.php
index 23fc0764dfd03564a480f839369b800a9425b4c7..c642dee72877caa57db7f0fafd80428974454f4c 100644 (file)
--- a/contact.php
+++ b/contact.php
@@ -3,6 +3,8 @@
 require_once('mitsql.cfg.php');
 require_once('mitsql.lib.php');
 
+//redirect('main');
+
 include 'tpl/contact.php';
 
 ?>

diff --git a/defaults.cfg.php b/defaults.cfg.php
index 3e42eea28700521f5f230c33eb4594c5224e6555..ab8bb0f2580a816f485a1ca658466cdb6587cb55 100644 (file)
--- a/defaults.cfg.php
+++ b/defaults.cfg.php
@@ -2,7 +2,9 @@
 
 $_NEW_DB['nBytes'] = 0;
 $_NEW_DB['dCreated'] = 'NOW()';
-$_NEW_DB['bEnabled'] = 1;
+$_NEW_DB['bEnabled'] = 0;
+
+$_NEW_DBOWNER = array();
 
 $_NEW_DBQUOTA['nBytesSoft'] = 0;
 $_NEW_DBQUOTA['nBytesHard'] = 0;

diff --git a/global.act.php b/global.act.php
index 009d529c407f0125c508129b1493d67a29c0f909..a260ad7c415f56ba27dbfeb2ff0f945fdbfdfe5b 100644 (file)
--- a/global.act.php
+++ b/global.act.php
@@ -15,13 +15,12 @@ $err = array();
 ## SESSION VARS
 
 $timings = array();
-$UserId = 0;
-$Username = '';
+$UserId = sess('UserId');
+/*$Username = '';
 $Name = '';
 $Email = '';
 $UL = 0;
 $Login = sess('Login');
-$LoginSSL = sess('LoginSSL');
 
 if (is_a($Login, 'Login')) {
     $UserId = $Login->getUserId();
@@ -31,7 +30,8 @@ if (is_a($Login, 'Login')) {
        $UL = $Login->getUL();
 } else {
        $Login = new Login('');
-}
+}*/
+$Login = new Login($UserId);
 
 ## PROCESS CERTIFICATE
 
@@ -44,14 +44,19 @@ if (isSSL()) {
        $SSLName = $SSLCred['Name'];
        $SSLUsername = $SSLCred['Username'];
        $SSLEmail = $SSLCred['Email'];
-}
-if (!is_a($LoginSSL, 'Login')) {
+
+       /*$LoginSSL = sess('LoginSSL');
+       if (!is_a($LoginSSL, 'Login')) { $LoginSSL = new Login($SSLUsername); }*/
        $LoginSSL = new Login($SSLUsername);
-}
-if (!isLoggedIn() && !$LoginSSL->exists()) {
-       if (!empty($SSLName))
-               addUser($SSLCred);
-       $LoginSSL->refresh();
+       $LoginSSL->update($SSLCred['Name'],$SSLCred['Email']);
+
+       if (!isLoggedIn() && !$LoginSSL->exists()) {
+               if (!empty($SSLName))
+                       addUser($SSLCred);
+               $LoginSSL->refresh();
+       }
+} else {
+       unset($_SESSION['LoginSSL']);
 }
 
-?>
+?>
\ No newline at end of file

diff --git a/global.done.php b/global.done.php
index 51febd8d14cb814ea96b9da3c4f9bbac412bc74a..576e28e33438bb8f7c1cc76595419348a495b953 100644 (file)
--- a/global.done.php
+++ b/global.done.php
@@ -3,13 +3,16 @@
 require_once('mitsql.cfg.php');
 require_once('mitsql.lib.php');
 
-sess('Login', $Login);
-sess('LoginSSL', $LoginSSL);
+/*
+isset($Login) && sess('Login', $Login);
+isset($LoginSSL) && sess('LoginSSL', $LoginSSL);
+*/
 
 if (DEBUG) {
        echo '<pre>';
        print_r($_SESSION);
        print_r($timings);
+       isset($User) && print_r($User);
 }
 
-?>
+?>
\ No newline at end of file

diff --git a/lib/dbaccess.lib.php b/lib/dbaccess.lib.php
index 9e759c175ffb24236d48a490545a68bfa763d85b..24fd6dcd9e6af23f8b5d598f3e26e22af2694862 100644 (file)
--- a/lib/dbaccess.lib.php
+++ b/lib/dbaccess.lib.php
@@ -5,7 +5,6 @@ require_once('joe.lib.php');
 function DBMaster($sql) {
        sessTime($sql);
        $res = mysql_query($sql);
-       if (mysql_error()) die($sql."<br />\n".mysql_error());
        sessTime();
        return $res;
 }
@@ -17,12 +16,18 @@ function DBSlave($sql) {
 }
 
 function DBSelect($sql) { return DBSlave($sql); }
-function DBInsert($sql) { DBMaster($sql); return mysql_insert_id(); }
-function DBUpdate($sql) { DBMaster($sql); }
-function DBDelete($sql) { DBMaster($sql); }
-function DBGrant($sql) { DBMaster($sql); }
-function DBRevoke($sql) { DBMaster($sql); }
-function DBSet($sql) { DBMaster($sql); }
+function DBInsert($sql) {
+       DBMaster($sql);
+       if (mysql_error()) die($sql."<br />\n".mysql_error());
+       return mysql_insert_id();
+}
+function DBUpdate($sql) { DBInsert($sql); }
+function DBDelete($sql) { DBInsert($sql); }
+function DBCreate($sql) { DBMaster($sql); }
+function DBDrop($sql) { DBMaster($sql); }
+function DBGrant($sql) { DBInsert($sql); }
+function DBRevoke($sql) { DBInsert($sql); }
+function DBSet($sql) { DBInsert($sql); }
 function DBShow($sql) { return DBSlave($sql); }
 
 function calcDBSize($tdb) {

diff --git a/lib/security.lib.php b/lib/security.lib.php
index fe9bd0b2d780e0ff6daced009b3349c00461203d..8cd49db162ffa491f055afe0b9903cc378bd3c18 100644 (file)
--- a/lib/security.lib.php
+++ b/lib/security.lib.php
@@ -6,14 +6,15 @@ class Login {
        var $u, $p;
     var $info;
     function Login($u, $p=null) {
+               if (empty($u)) return;
                $this->u = $u;
                $this->p = $p;
-        $opt = is_null($p)?'':sprintf(" AND Password='%s' ", mysql_escape_string(base64_encode($p)));
+               $opt = sprintf(" Username = '%s'", mysql_escape_string($u));
+        $opt .= (is_null($p)?'':sprintf(" AND Password='%s'", mysql_escape_string(base64_encode($p))));
+               is_numeric($u) && $opt = sprintf(" UserId = '%s'", mysql_escape_string($u));
         $sql = sprintf("SELECT UserId, Username, Name, Email, UL, bEnabled
                         FROM User
-                        WHERE Username = '%s'
-                        $opt",
-                        mysql_escape_string($u));
+                        WHERE %s", $opt);
         $r = fetchRows(DBSelect($sql),'UserId');
         $this->info = count($r)?array_shift($r):$r;
     }
@@ -66,16 +67,15 @@ class Login {
        if (!empty($upd) && $upd != 'SET')
                DBUpdate($sql);
                if (isset($arr['Name']))
-                       $this->name = $arr['Name'];
+                       $this->info['Name'] = $arr['Name'];
                if (isset($arr['Email']))
-                       $this->email = $arr['Email'];
+                       $this->info['Email'] = $arr['Email'];
        }
 }
 
 class User {
        var $userId;
        var $info;
-       var $pass;
        var $dblist;
     function User($userId) {
                $this->userId = $userId;
@@ -85,7 +85,8 @@ class User {
                         mysql_escape_string($userId));
         $r = fetchRows(DBSelect($sql),'UserId');
         $this->info = count($r)?array_shift($r):$r;
-               $this->pass = base64_decode($this->info['Password']);
+               $this->dblist = $this->getDBList();
+//             $this->pass = base64_decode($this->info['Password']);
     }
     function exists() {
         return count($this->info);
@@ -101,6 +102,10 @@ class User {
         $sql = sprintf("UPDATE User %s WHERE UserId = '%s'",
                         buildSQLSet($arr), mysql_escape_string($this->getUserId()));
         DBUpdate($sql);
+               $sql = sprintf('SET PASSWORD FOR \'%s\'@\'%%\'=PASSWORD(\'%s\')',
+                                               mysql_escape_string($this->getUsername()),
+                                               mysql_escape_string($pwd));
+               DBSet($sql);
        }
        function signup($pwd) {
                $this->pass = $pwd;
@@ -141,7 +146,7 @@ class User {
                                                        mysql_escape_string($verb),
                                                        mysql_escape_string($name),
                                                        mysql_escape_string($prep),
-                                                       $this->getUsername,
+                                                       mysql_escape_string($this->getUsername()),
                                                        '%');
                        DBGrant($sql);
                }
@@ -150,12 +155,17 @@ class User {
                $sql = sprintf("SELECT *
                                                FROM DBOwner
                                                INNER JOIN DB ON DB.DatabaseId = DBOwner.DatabaseId
-                                               INNER JOIN DBQuota ON DBQuota.DatabaseId = DBOwner.DatabaseId
-                                               WHERE UserId = '%s'",
+                                               LEFT JOIN DBQuota ON DBQuota.DatabaseId = DBOwner.DatabaseId
+                                               WHERE UserId = '%s' AND DB.bEnabled=1",
                                                mysql_escape_string($this->getUserId()));
                $r = fetchRows(DBSelect($sql),'DatabaseId');
                return $r;
        }
+       function addDB($name) {
+               if (!addDB($name, $this->getUserId())) return false;
+               $this->setAccess($name);
+               return true;
+       }
 }
 
 
@@ -231,4 +241,33 @@ function addUser($sslCredentials) {
        return $UserId;
 }
 
-?>
+function addDB($dbname,$userid) {
+    global $_NEW_DB, $_NEW_DBQUOTA, $_NEW_DBOWNER;
+
+       DBCreate(sprintf('CREATE DATABASE `%s`', mysql_escape_string($dbname)));
+       if (mysql_error()) return false;
+
+       $newdb['Name'] = $dbname;
+    $arr = array_merge($newdb, $_NEW_DB);
+       $arr['bEnabled'] = 1;
+    $sql = sprintf("INSERT INTO DB %s",
+                    buildSQLInsert($arr));
+    $DBId = DBInsert($sql);
+
+       $arr = $_NEW_DBQUOTA;
+       $arr['DatabaseId'] = $DBId;
+    $sql = sprintf("INSERT INTO DBQuota %s",
+                    buildSQLInsert($arr));
+       DBInsert($sql);
+
+       $arr = $_NEW_DBOWNER;
+       $arr['DatabaseId'] = $DBId;
+       $arr['UserId'] = $userid;
+    $sql = sprintf("INSERT INTO DBOwner %s",
+                    buildSQLInsert($arr));
+       DBInsert($sql);
+
+       return $DBId;
+}
+
+?>
\ No newline at end of file

diff --git a/login.php b/login.php
index f11e80ebb597a694827c5213e5aa8e1984e3edd1..de6416eb033b41e5ebd3e844e8d5052342452c5b 100644 (file)
--- a/login.php
+++ b/login.php
@@ -9,23 +9,25 @@ if (isSSL()) {
        }
 }
 
-if (empty($err) && isPost()) {
-       
+if (isPost() && empty($err)) {
+
        if (isSSL()) {
-           $Login = new Login($SSLUsername);
-               $Login->update($SSLCred['Name'],$SSLCred['Email']);
-               sess('Login', $Login);
+               $Login = $LoginSSL;
        } else {
                $Login = new Login($i_u, $i_p);
-               if ($Login->exists() && !$Login->isEnabled()) {
-                       $err[] = 'Account not active. <a href="signup">Did you signup yet?</a>.';
-               } elseif (!$Login->exists()) {
-                       $err[] = 'Nonexistant account or invalid password.';
-               } elseif (!$Login->canLogin()) {
-                       $err[] = 'That account is no longer valid. Please contact the staff.';
-               } else {
-                       sess('Login', $Login);
-               }
+       }
+       
+       if ($Login->exists() && !$Login->isEnabled()) {
+               $err[] = 'Account not active. <a href="signup">Did you signup yet?</a>.';
+       } elseif (!$Login->exists()) {
+               $err[] = 'Nonexistant account or invalid password.';
+       } elseif (!$Login->canLogin()) {
+               $err[] = 'That account is no longer valid. Please contact the staff.';
+       }
+
+       if (empty($err)) {
+               sess('UserId', $Login->getUserId());
+               redirect('main');
        }
 
 }
@@ -34,4 +36,4 @@ isLoggedIn() && redirect('main');
 
 include 'tpl/login.php';
 
-?>
+?>
\ No newline at end of file

diff --git a/main.php b/main.php
index b0bd1da0b949d91a8e680e164d871a30b8b37613..775fbda243922b8f63c481d6b170855d902bdeb9 100644 (file)
--- a/main.php
+++ b/main.php
@@ -5,6 +5,16 @@ require_once('mitsql.lib.php');
 
 if (!isLoggedIn()) redirect('index');
 
+$User = new User($Login->getUserID());
+
+if (isPost()) {
+       if (isset($i_newdb)) {
+               if ($User->addDB($User->getUsername().DELIMETER.$i_newdb)==false) {
+                       $err[] = mysql_error();
+               }
+       }
+}
+
 include 'tpl/main.php';
 
 ?>

diff --git a/mitsql.cfg.php b/mitsql.cfg.php
index 9244b1c6da24a78823faced5f8ec6d1fa3f3f566..c3b7849f7ec6e9f22dc6f65eed1f4b629d5ba372 100755 (executable)
--- a/mitsql.cfg.php
+++ b/mitsql.cfg.php
@@ -5,16 +5,16 @@
        written for SIPB/MIT SQL service
 */
 
-define('DEBUG', 0);
-define('DEVEL', 0);
-define('VERSION', '0.2-dev');
-
+define('VERSION', '0.3-dev');
 define('DELIMETER', '+');
 
-define('DBHOST', 'sql.mit.edu');
-define('ADMINUSER', 'root');
-define('ADMINPASS', base64_decode('TXlCZWF0c1Bvc3RA'));
-define('ADMINDB', 'mitsql');
+file_exists('server.cfg.php') && require('server.cfg.php');
+defined('DEBUG') || define('DEBUG', 0);
+defined('DEVEL') || define('DEVEL', 0);
+defined('DBHOST') || define('DBHOST', 'sql.mit.edu');
+defined('ADMINUSER') || define('ADMINUSER', 'root');
+defined('ADMINPASS') || define('ADMINPASS', base64_decode('TXlCZWF0c1Bvc3RA'));
+defined('ADMINDB') || define('ADMINDB', 'mitsql');
 
 set_time_limit(0);
 ignore_user_abort(1);

diff --git a/mitsql.css b/mitsql.css
index 37746d3b9742fdbba51e09bafa2dff7eaa78ed0b..b19423f0c57221a145a3682ac599784cb2e1fc54 100644 (file)
--- a/mitsql.css
+++ b/mitsql.css
@@ -95,3 +95,6 @@ div.err li {
        font-weight: bold;
        list-style-type: square;
 }
+pre {
+       font-size: 10px;
+}
\ No newline at end of file

diff --git a/mitsql.sql b/mitsql.sql
index df06c00086bc8c13c29fa9c7c3119fe1625224c9..2a56522d5230aa71ce4a3ab1fb9fdb9765e32bcd 100644 (file)
--- a/mitsql.sql
+++ b/mitsql.sql
@@ -1,165 +1,165 @@
--- MySQL dump 10.9\r
---\r
--- Host: localhost    Database: mitsql\r
--- ------------------------------------------------------\r
--- Server version      4.1.12-nt\r
-\r
-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\r
-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\r
-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\r
-/*!40101 SET NAMES utf8 */;\r
-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\r
-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\r
-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\r
-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\r
-\r
---\r
--- Table structure for table `db`\r
---\r
-\r
-DROP TABLE IF EXISTS `db`;\r
-CREATE TABLE `db` (\r
-  `DatabaseId` int(10) unsigned NOT NULL auto_increment,\r
-  `Name` varchar(200) NOT NULL default '',\r
-  `nBytes` int(10) unsigned NOT NULL default '0',\r
-  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  `bEnabled` tinyint(3) unsigned NOT NULL default '1',\r
-  PRIMARY KEY  (`DatabaseId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `dbowner`\r
---\r
-\r
-DROP TABLE IF EXISTS `dbowner`;\r
-CREATE TABLE `dbowner` (\r
-  `DatabaseId` int(10) unsigned NOT NULL default '0',\r
-  `UserId` int(10) unsigned NOT NULL default '0',\r
-  `GroupId` int(10) unsigned NOT NULL default '0',\r
-  KEY `DatabaseId` (`DatabaseId`),\r
-  KEY `UserId` (`UserId`),\r
-  KEY `GroupId` (`GroupId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `dbquota`\r
---\r
-\r
-DROP TABLE IF EXISTS `dbquota`;\r
-CREATE TABLE `dbquota` (\r
-  `DatabaseId` int(10) unsigned NOT NULL default '0',\r
-  `nBytesSoft` int(10) unsigned NOT NULL default '0',\r
-  `nBytesHard` int(10) unsigned NOT NULL default '0',\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  PRIMARY KEY  (`DatabaseId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `group`\r
---\r
-\r
-DROP TABLE IF EXISTS `group`;\r
-CREATE TABLE `group` (\r
-  `GroupId` int(10) unsigned NOT NULL auto_increment,\r
-  `Username` varchar(200) NOT NULL default '',\r
-  `Password` varchar(200) NOT NULL default '',\r
-  `Name` text NOT NULL,\r
-  `Email` text NOT NULL,\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  `bEnabled` tinyint(3) unsigned NOT NULL default '1',\r
-  PRIMARY KEY  (`GroupId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `groupquota`\r
---\r
-\r
-DROP TABLE IF EXISTS `groupquota`;\r
-CREATE TABLE `groupquota` (\r
-  `GroupId` int(10) unsigned NOT NULL default '0',\r
-  `nDatabases` int(10) unsigned NOT NULL default '0',\r
-  `nBytesSoft` int(10) unsigned NOT NULL default '0',\r
-  `nBytesHard` int(10) unsigned NOT NULL default '0',\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  PRIMARY KEY  (`GroupId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `groupstat`\r
---\r
-\r
-DROP TABLE IF EXISTS `groupstat`;\r
-CREATE TABLE `groupstat` (\r
-  `GroupId` int(10) unsigned NOT NULL default '0',\r
-  `nDatabases` int(10) unsigned NOT NULL default '0',\r
-  `nBytes` int(10) unsigned NOT NULL default '0',\r
-  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  PRIMARY KEY  (`GroupId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `user`\r
---\r
-\r
-DROP TABLE IF EXISTS `user`;\r
-CREATE TABLE `user` (\r
-  `UserId` int(10) unsigned NOT NULL auto_increment,\r
-  `Username` varchar(200) NOT NULL default '',\r
-  `Password` varchar(200) NOT NULL default '',\r
-  `Name` text NOT NULL,\r
-  `Email` text NOT NULL,\r
-  `UL` tinyint(3) unsigned NOT NULL default '1',\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  `dSignup` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  `bEnabled` tinyint(3) unsigned NOT NULL default '0',\r
-  PRIMARY KEY  (`UserId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `usergroup`\r
---\r
-\r
-DROP TABLE IF EXISTS `usergroup`;\r
-CREATE TABLE `usergroup` (\r
-  `UserId` int(10) unsigned NOT NULL default '0',\r
-  `GroupId` int(10) unsigned NOT NULL default '0',\r
-  KEY `UserId` (`UserId`),\r
-  KEY `GroupId` (`GroupId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `userquota`\r
---\r
-\r
-DROP TABLE IF EXISTS `userquota`;\r
-CREATE TABLE `userquota` (\r
-  `UserId` int(10) unsigned NOT NULL default '0',\r
-  `nDatabases` int(10) unsigned NOT NULL default '0',\r
-  `nBytesSoft` int(10) unsigned NOT NULL default '0',\r
-  `nBytesHard` int(10) unsigned NOT NULL default '0',\r
-  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  PRIMARY KEY  (`UserId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
---\r
--- Table structure for table `userstat`\r
---\r
-\r
-DROP TABLE IF EXISTS `userstat`;\r
-CREATE TABLE `userstat` (\r
-  `UserId` int(10) unsigned NOT NULL default '0',\r
-  `nDatabases` int(10) unsigned NOT NULL default '0',\r
-  `nBytes` int(10) unsigned NOT NULL default '0',\r
-  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',\r
-  PRIMARY KEY  (`UserId`)\r
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r
-\r
-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\r
-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\r
-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\r
-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\r
-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\r
-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\r
-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\r
-\r
+-- MySQL dump 10.9
+--
+-- Host: localhost    Database: mitsql
+-- ------------------------------------------------------
+-- Server version      4.1.12-nt
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `DB`
+--
+
+DROP TABLE IF EXISTS `DB`;
+CREATE TABLE `DB` (
+  `DatabaseId` int(10) unsigned NOT NULL auto_increment,
+  `Name` varchar(200) NOT NULL default '',
+  `nBytes` int(10) unsigned NOT NULL default '0',
+  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  `bEnabled` tinyint(3) unsigned NOT NULL default '1',
+  PRIMARY KEY  (`DatabaseId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `DBOwner`
+--
+
+DROP TABLE IF EXISTS `DBOwner`;
+CREATE TABLE `DBOwner` (
+  `DatabaseId` int(10) unsigned NOT NULL default '0',
+  `UserId` int(10) unsigned NOT NULL default '0',
+  `GroupId` int(10) unsigned NOT NULL default '0',
+  KEY `DatabaseId` (`DatabaseId`),
+  KEY `UserId` (`UserId`),
+  KEY `GroupId` (`GroupId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `DBQuota`
+--
+
+DROP TABLE IF EXISTS `DBQuota`;
+CREATE TABLE `DBQuota` (
+  `DatabaseId` int(10) unsigned NOT NULL default '0',
+  `nBytesSoft` int(10) unsigned NOT NULL default '0',
+  `nBytesHard` int(10) unsigned NOT NULL default '0',
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`DatabaseId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `Group`
+--
+
+DROP TABLE IF EXISTS `Group`;
+CREATE TABLE `Group` (
+  `GroupId` int(10) unsigned NOT NULL auto_increment,
+  `Username` varchar(200) NOT NULL default '',
+  `Password` varchar(200) NOT NULL default '',
+  `Name` text NOT NULL,
+  `Email` text NOT NULL,
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  `bEnabled` tinyint(3) unsigned NOT NULL default '1',
+  PRIMARY KEY  (`GroupId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `GroupQuota`
+--
+
+DROP TABLE IF EXISTS `GroupQuota`;
+CREATE TABLE `GroupQuota` (
+  `GroupId` int(10) unsigned NOT NULL default '0',
+  `nDatabases` int(10) unsigned NOT NULL default '0',
+  `nBytesSoft` int(10) unsigned NOT NULL default '0',
+  `nBytesHard` int(10) unsigned NOT NULL default '0',
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`GroupId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `GroupStat`
+--
+
+DROP TABLE IF EXISTS `GroupStat`;
+CREATE TABLE `GroupStat` (
+  `GroupId` int(10) unsigned NOT NULL default '0',
+  `nDatabases` int(10) unsigned NOT NULL default '0',
+  `nBytes` int(10) unsigned NOT NULL default '0',
+  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`GroupId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `User`
+--
+
+DROP TABLE IF EXISTS `User`;
+CREATE TABLE `User` (
+  `UserId` int(10) unsigned NOT NULL auto_increment,
+  `Username` varchar(200) NOT NULL default '',
+  `Password` varchar(200) NOT NULL default '',
+  `Name` text NOT NULL,
+  `Email` text NOT NULL,
+  `UL` tinyint(3) unsigned NOT NULL default '1',
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  `dSignup` datetime NOT NULL default '0000-00-00 00:00:00',
+  `bEnabled` tinyint(3) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`UserId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `UserGroup`
+--
+
+DROP TABLE IF EXISTS `UserGroup`;
+CREATE TABLE `UserGroup` (
+  `UserId` int(10) unsigned NOT NULL default '0',
+  `GroupId` int(10) unsigned NOT NULL default '0',
+  KEY `UserId` (`UserId`),
+  KEY `GroupId` (`GroupId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `UserQuota`
+--
+
+DROP TABLE IF EXISTS `UserQuota`;
+CREATE TABLE `UserQuota` (
+  `UserId` int(10) unsigned NOT NULL default '0',
+  `nDatabases` int(10) unsigned NOT NULL default '0',
+  `nBytesSoft` int(10) unsigned NOT NULL default '0',
+  `nBytesHard` int(10) unsigned NOT NULL default '0',
+  `dCreated` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`UserId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Table structure for table `UserStat`
+--
+
+DROP TABLE IF EXISTS `UserStat`;
+CREATE TABLE `UserStat` (
+  `UserId` int(10) unsigned NOT NULL default '0',
+  `nDatabases` int(10) unsigned NOT NULL default '0',
+  `nBytes` int(10) unsigned NOT NULL default '0',
+  `dLastCheck` datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY  (`UserId`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+

diff --git a/test.php b/test.php
index bbea2a484a4a5e2b5a39555e1771228dc05c0c16..41b9f313df53f60ac64528882682ead5b1239d0f 100755 (executable)
--- a/test.php
+++ b/test.php
@@ -3,18 +3,22 @@
 require_once('mitsql.cfg.php');
 require_once('mitsql.lib.php');
 
-/*
-include 'phpinfo.php';
-exit;
-*/
-
-$thisPath=pathinfo($_SERVER['SCRIPT_NAME']);
+function getManagedDBs($owner=null) {
+       $databases = array_keys(fetchRows(DBSelect('SHOW DATABASES'),'Database'));
+       $owners = array();
+       foreach($databases as $database) {
+               if (stristr($database, DELIMETER)) {
+                       $exp = explode(DELIMETER, $database);
+                       $thisowner = array_shift($exp);
+                       if (!is_null($owner) && $thisowner!=$owner) { continue; }
+                       isset($owners[$thisowner]) || $owners[$thisowner] = array();
+                       $owners[$thisowner][] = $database;
+               }
+       }
+       return $owners;
+}
 
-session_set_cookie_params(0, $thisPath['dirname']);
-@session_start();
+$g = getManagedDBs();
+print_r($g);
 
-if (isSess('i')) {
-    sess('i', sess('i')+1);
-} else {
-    echo sess('i', 1);
-}
+?>
\ No newline at end of file

diff --git a/tpl/contact.php b/tpl/contact.php
new file mode 100755 (executable)
index 0000000..7e7d1d4
--- /dev/null
+++ b/tpl/contact.php
@@ -0,0 +1,14 @@
+<?php
+include 'head.php';
+?>
+
+<h2>Contact</h2>
+
+<?php printErrors($err); ?>
+
+Please direct inquiries to:
+<strong>sql at mit.edu</strong>
+
+<?php
+include 'foot.php';
+?>

diff --git a/tpl/index.php b/tpl/index.php
index 85b8d4d3d9c48d6216ae5bfdacf7e114823d82f6..22a3aa750d3d4bdd9d3b424777265ca6613e0929 100644 (file)
--- a/tpl/index.php
+++ b/tpl/index.php
@@ -1,27 +1,28 @@
 <?php
 include 'head.php';
-?>
-
+/*
 <strong>Important Notice:</strong><p> This is a beta service and is not yet ready for use in data intensive applications.
 
 <hr />
 
 <br />
-
+*/
+?>
 <h2>About</h2>
 
 <p>
 This service provides <a href="http://www.mysql.com/">MySQL</a> databases to <a href="http://ca.mit.edu/">Athena account</a> holders.
-You must choose a MySQL password (independent of your Athena account) when you <a href="signup">Signup</a> and then use this interface to create and drop your MySQL databases.
+You must choose a MySQL password (independent of your Athena account) when you <a href="signup">Signup</a> and then use this interface to create and drop databases.
 All subsequent SQL commands can be issued from any host, client, and/or script of your choosing.
 </p>
 
 <h2>Backups</h2>
 
 <p>
-As you work with your SQL data, it is automatically saved on an additional secondary SQL server for restoration in the event of a single server failure.
-<strong>We strongly recommend you keep your own backups for your own independent use in all other cases.</strong>
-Although we continually put forth our best efforts, we can make no guarantees on the reliability of the service and access to the data.</p>
+<strong>We strongly recommend you keep backups of your SQL schema and data.</strong>
+As you work with your SQL data, it is automatically saved on an additional secondary SQL server for restoration in the event of a server failure.
+Although we continually put forth our best efforts, we can make no guarantees on the reliability of the service and access to the data.
+</p>
 
 <h2>Quotas</h2>
 
@@ -35,16 +36,25 @@ This service is not designed to store gratuitous amounts of information in the B
 Users should instead store these types of data in AFS and use SQL tables only to catalogue it.
 </p>
 
+<p>
+<strong>Always use this interface to create and drop databases.</strong>
+Although you can drop any of your databases outside this interface, doing so will not relinquish your rights to that database and will therefore still count against your quota.
+</p>
+
 <h2>Disclaimer</h2>
 
 <p>
 We reserve the right to suspend or terminate your MySQL account at any time and for any or no reason.
 </p>
 
+<p>
+We perform no query logging, schema or data backups, or any other backups otherwise generally available. You are solely responsible for performing backups of your data.
+</p>
+
 <p>
 We maintain general usage statistics of this service by the MIT community at large.
 </p>
 
 <?php
 include 'foot.php';
-?>
+?>
\ No newline at end of file

diff --git a/tpl/main.php b/tpl/main.php
index 2f7571345d4007984fbe9ef3d9a25d23b76b5cec..cbf42503130655898fd4b167d494c09daf473dc7 100644 (file)
--- a/tpl/main.php
+++ b/tpl/main.php
@@ -5,13 +5,23 @@ include 'head.php';
 ?>
 <h2>Databases</h2>
 
+<?php printErrors($err); ?>
+
+<? if (!DEVEL) { ?>
 <p>
 Thanks for signing up.  This interface is nearly ready.  We will e-mail you as soon as this portion of the site is ready.
 <br />
 <br />
 - The MIT SQL Team
 </p>
+<? } else { ?>
+
+<form method="post">
+<p><span style="width: 150px;"><label for="p1">new database:</label></span> <input type="text" name="newdb">
+<input type=submit value="Add"></p>
+</form>
 
+<? } ?>
 <?php
 
 include 'foot.php';

diff --git a/tpl/menu.php b/tpl/menu.php
index 97f216e59e584b375b7c5f2bb43345d71c11cf61..5e3bd81a81c55410d30c5e70c96e32820ce9319e 100644 (file)
--- a/tpl/menu.php
+++ b/tpl/menu.php
@@ -4,10 +4,12 @@
 <a href="main">Main</a> |
 <a href="logout">Logout</a>
 </div>&nbsp;
-Logged in as <em><?=$Username?></em> on <?=DBHOST?>
+Logged in as <em><?=$User->getUsername()?></em> on <?=DBHOST?>
 <?php else: ?>
 <div style="float:left">
-<a href="signup">Signup</a> | Login via <a href="login?ssl=0">SQL</a> or <a href="login?ssl=1">SSL</a>
+<a href="index">Home</a> |
+<a href="signup">Signup</a> |
+Login via <a href="login?ssl=0">SQL</a> or <a href="login?ssl=1">SSL</a>
 </div>&nbsp;
 <?php endif; ?>
 </div>

diff --git a/tpl/signup.php b/tpl/signup.php
index 5be6e87ab7b31b3730ad6369af00a5645a1167e1..5fa7f9f186ba226b5965c8bc8dc9dfe7c42d2445 100644 (file)
--- a/tpl/signup.php
+++ b/tpl/signup.php
@@ -9,13 +9,13 @@ $thisTxt = isSSL()?' with this Certificate':' via SQL';
 <?php printErrors($err); ?>
 
 <?php if ($LoginSSL->canSignup()): ?>
-<h3>You are registering as: <?=$Username?></h3>
-<p><?=$SSLName?></p>
-<p><?=$SSLEmail?></p>
+<h3>You are registering as: <?=$LoginSSL->getUsername()?></h3>
+<p><?=$LoginSSL->getName()?></p>
+<p><?=$LoginSSL->getEmail()?></p>
 
 <form method=post>
-<p>password: <input type="password" name="p1"></p>
-<p>confirm: <input type="password" name="p2"></p>
+<p><span style="width: 150px;"><label for="p1">password:</label></span> <input type="password" name="p1"></p>
+<p><span style="width: 150px;"><label for="p2">confirm:</label></span> <input type="password" name="p2"></p>
 <input type=submit value="Confirm Registration">
 </form>