changed NATURAL JOINs to INNERs to avoid conflicting keys

added drop database feature

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

diff --git a/admin/index.php b/admin/index.php
index b8eaf60ec351e731e4cbbd7719308ff8f67574e5..fb99d9bebe18efb685d2495ae7232fc43146006e 100755 (executable)
--- a/admin/index.php
+++ b/admin/index.php
@@ -1,8 +1,8 @@
 <?php
 
 require_once('../mitsql.cfg.php');
-echo get_include_path();
+require_once('mitsql.lib.php');
 
-phpinfo();
+if (!isAdmin()) redirect('main');
 
 ?>

diff --git a/global.done.php b/global.done.php
index 18d11a4df939740ab9475fba7fe5414667bd8ba4..2f652898aa419196cca4b32514c6325219551bd6 100644 (file)
--- a/global.done.php
+++ b/global.done.php
@@ -12,6 +12,7 @@ if (DEBUG) {
        echo '<pre>';
        print_r($_SESSION);
        print_r($timings);
+       isset($Login) && print_r($Login);
        isset($User) && print_r($User);
 }
 

diff --git a/lib/joe.lib.php b/lib/joe.lib.php
index 04c4dc19106ad7f2cb637d36435be7f3c8a67961..5ea684a686a632b4bd1c77dde718bbd60c1a5328 100755 (executable)
--- a/lib/joe.lib.php
+++ b/lib/joe.lib.php
@@ -108,13 +108,17 @@ function fetchRows($rs, $key = null) {
 function printErrors($err) { printList('err', $err); }
 function printMsgs($err) { printList('msg', $err); }
 
-function printList($class,$errArray) {
-    if (isset($errArray) && count($errArray)) {
-        echo '<div class="',$class,'"><ul>';
-        foreach($errArray as $err) {
-            echo '<li><p>',$err,'</p></li>';
+function printList($class,$err) {
+    if (is_array($err) && count($err)) {
+        echo '<div class="',$class,'">',(count($err)>1?'<ul>':'');
+        foreach($err as $e) {
+                       if (count($err)>1) {
+                               echo '<li><p>',$e,'</p></li>';
+                       } else {
+                               echo '<p>',$e,'</p>';
+                       }
         }
-        echo '</ul></div>';
+        echo (count($err)>1?'</ul>':''),'</div>';
     }
 }
 

diff --git a/lib/mitsql.lib.php b/lib/mitsql.lib.php
index b275c304b8991b0e7a7cae02ba1bc559be4b3cd4..a7dba99057cf9965655869ef65a65cc4cea3beac 100755 (executable)
--- a/lib/mitsql.lib.php
+++ b/lib/mitsql.lib.php
@@ -16,6 +16,8 @@ require_once('display.lib.php');
 if (isset($_SERVER['REQUEST_URI'])) {
     //$thisPath=pathinfo($_SERVER['REQUEST_URI']);
     //session_set_cookie_params(0, $thisPath['dirname']);
+       //$arr = explode('/', $_SERVER['SCRIPT_NAME']);
+    //session_set_cookie_params(0, '/'.$arr[1].'/');
     session_set_cookie_params(0, $BASE_URL);
     session_start();
     define('INTERACTIVE', 1);

diff --git a/lib/security.lib.php b/lib/security.lib.php
index c898f766d7cfb243e2472024a278b2979082e403..7557465526cf66a96010402dc94cdc699efa461e 100644 (file)
--- a/lib/security.lib.php
+++ b/lib/security.lib.php
@@ -3,21 +3,25 @@
 require_once('mitsql.lib.php');
 
 class Login {
-       var $u, $p;
+       var $id, $u, $p;
     var $info;
     function Login($u, $p=null) {
                if (empty($u)) return;
-               $this->u = $u;
+               $this->u = $u;
                $this->p = $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));
+               if (is_numeric($u)) {
+                       $this->id = $u;
+                       $opt = sprintf(" UserId = '%s'", mysql_escape_string($u));
+               } else {
+                       $opt = sprintf(" Username = '%s'", mysql_escape_string($u));
+                       $opt .= (is_null($p)?'':sprintf(" AND Password='%s'", mysql_escape_string(base64_encode($p))));
+               }
         $sql = sprintf("SELECT UserId, Username, Name, Email, UL, bEnabled
                         FROM User
                         WHERE %s", $opt);
         $r = fetchRows(DBSelect($sql),'UserId');
-        $this->info = count($r)?array_shift($r):$r;
-    }
+               $this->info = count($r)?array_shift($r):$r;
+       }
     function exists() {
         return count($this->info);
     }
@@ -52,7 +56,11 @@ class Login {
         $this->info = null;
     }
     function refresh() {
-        $this->Login($this->u,$this->p);
+               if (!empty($this->id)) {
+                       $this->Login($this->id);
+               } else {
+                       $this->Login($this->u,$this->p);
+               }
     }
     function update($name=null,$email=null) {
         if (!$this->exists()) return;
@@ -61,11 +69,11 @@ class Login {
                if ($email == $this->getEmail()) $email = null;
         is_null($name) || $arr['Name'] = $name;
         is_null($email) || $arr['Email'] = $email;
-       $upd = buildSQLSet($arr);
+               $upd = buildSQLSet($arr);
         $sql = sprintf("UPDATE User %s WHERE UserId = '%s'",
                         $upd, mysql_escape_string($this->getUserId()));
-       if (!empty($upd) && $upd != 'SET')
-               DBUpdate($sql);
+               if (!empty($upd) && $upd != 'SET')
+                       DBUpdate($sql);
                if (isset($arr['Name']))
                        $this->info['Name'] = $arr['Name'];
                if (isset($arr['Email']))
@@ -81,8 +89,8 @@ class User {
                $this->userId = $userId;
         $sql = sprintf("SELECT User.UserId, Username, Password, Name, Email, UL, bEnabled, nBytesSoft, nBytesHard, nBytes, nDatabases, nDatabasesHard
                         FROM User
-                                               NATURAL JOIN UserQuota
-                                               NATURAL JOIN UserStat
+                                               INNER JOIN UserQuota ON User.UserId = UserQuota.UserId
+                                               INNER JOIN UserStat ON User.UserId = UserStat.UserId
                         WHERE User.UserId = '%s'",
                         mysql_escape_string($userId));
         $r = fetchRows(DBSelect($sql),'UserId');
@@ -181,8 +189,8 @@ class User {
                        //                      LEFT JOIN DBQuota ON DBQuota.DatabaseId = DBOwner.DatabaseId
                        $sql = sprintf("SELECT *
                                                FROM DBOwner
-                                               NATURAL JOIN DB
-                                               NATURAL JOIN DBQuota
+                                               INNER JOIN DB ON DB.DatabaseId = DBOwner.DatabaseId
+                                               INNER JOIN DBQuota ON DBQuota.DatabaseId = DBOwner.DatabaseId
                                                WHERE DBOwner.UserId = '%s' AND DB.bEnabled=1",
                                                mysql_escape_string($this->getUserId()));
 //                     $r = fetchRows(DBSelect($sql),'DatabaseId');
@@ -191,10 +199,17 @@ class User {
                }
        }
        function addDB($name) {
+               if (in_array($name, $this->getDBList())) return false;
                if (!addDB($name, $this->getUserId())) return false;
                $this->setAccess($name);
                return true;
        }
+       function delDB($name) {
+               if (!in_array($name, array_keys($this->getDBList()))) return false;
+               if (!delDB($name)) return false;//, $this->getUserId())) return false;
+               $this->setAccess($name,false);
+               return true;
+       }
 }
 
 
@@ -206,6 +221,14 @@ function isLoggedIn($aLogin=null) {
     return !empty($aLogin) && is_a($aLogin, 'Login') && $aLogin->canLogin();
 }
 
+function isAdmin($aLogin=null) {
+    if (is_null($aLogin)) {
+        global $Login;
+        $aLogin = $Login;
+    }
+    return !empty($aLogin) && is_a($aLogin, 'Login') && $aLogin->getUL()>=100;
+}
+
 function isImpersonating() {
        return isSess('_UserId') && isSess('UserId');
 }
@@ -294,32 +317,61 @@ function addUser($sslCredentials) {
 }
 
 function addDB($dbname,$userid) {
-    global $_NEW_DB, $_NEW_DBQUOTA, $_NEW_DBOWNER;
+       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 = array_merge($newdb, $_NEW_DB);
        $arr['bEnabled'] = 1;
-    $sql = sprintf("INSERT INTO DB %s",
+       $sql = sprintf("INSERT IGNORE INTO DB %s",
                     buildSQLInsert($arr));
-    $DBId = DBInsert($sql);
+       $DBId = DBInsert($sql);
+       if (empty($DBId)) {
+               $sql = sprintf("SELECT DatabaseId FROM DB WHERE Name = '%s'",
+                                               mysql_escape_string($dbname));
+               $r = fetchRows(DBSelect($sql), 'DatabaseId');
+               if (count($r)) {
+                       $r = array_shift($r);
+                       $DBId = $r['DatabaseId'];
+               } else {
+                       return false;
+               }
+               $sql = sprintf("UPDATE DB %s WHERE DB.DatabaseId = '%s'",
+                                               buildSQLSet($arr),
+                                               $DBId);
+               DBUpdate($sql);
+               return $DBId;
+       } else {
+               $arr = $_NEW_DBQUOTA;
+               $arr['DatabaseId'] = $DBId;
+               $sql = sprintf("INSERT IGNORE INTO DBQuota %s",
+                                               buildSQLInsert($arr));
+               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 IGNORE INTO DBOwner %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;
+       }
+}
+
+function delDB($dbname) {
+       global $_NEW_DB, $_NEW_DBQUOTA, $_NEW_DBOWNER;
+
+       DBCreate(sprintf('DROP DATABASE `%s`', mysql_escape_string($dbname)));
+
+       $arr['bEnabled'] = 0;
+       $sql = sprintf("UPDATE DB %s WHERE DB.Name = '%s'",
+                                       buildSQLSet($arr),
+                                       $dbname);
+       DBUpdate($sql);
 
-       return $DBId;
+       return true;
 }
 
 ?>

diff --git a/main.php b/main.php
index d59a921cd0236792b559ece86a3c849446cc7f17..6d0e19b87a7d676a438865f32365760392cedc57 100644 (file)
--- a/main.php
+++ b/main.php
@@ -17,6 +17,18 @@ if (isPost()) {
                        $msg[] = 'Database `'.$dbname.'` created.';
                }
        }
+       if (isset($i_drop)) {
+               $dropdbs = array_keys($i_drop);
+               foreach($dropdbs as $dbname) {
+                       if ($User->delDB($dbname)) {
+                               $msg[] = 'Database `'.$dbname.'` dropped.';
+                       } else {
+                               $err[] = mysql_error();
+                       }
+               }
+               if (!count($err))
+                       $User->refresh();
+       }
 }
 
 $myDBs = $User->getDBList();

diff --git a/mitsql.cfg.php b/mitsql.cfg.php
index a534a8aeac1cf72e8b2dd2f584643822febceb11..f83bc9a34dcdc273d30478d1f2db3d6b84badb76 100755 (executable)
--- a/mitsql.cfg.php
+++ b/mitsql.cfg.php
@@ -5,6 +5,9 @@
        written for SIPB/MIT SQL service
 */
 
+$CWD = getcwd();
+chdir(dirname(__FILE__));
+
 file_exists('server.cfg.php') && require('server.cfg.php');
 defined('DEBUG') || define('DEBUG', 0);
 defined('DEVEL') || define('DEVEL', 0);
@@ -18,19 +21,21 @@ if (DEVEL) {
 
 define('DELIMETER', '+');
 defined('DBHOST') || define('DBHOST', 'sql.mit.edu');
+defined('BASE_URL') || define('BASE_URL', '/~sql/main');
 defined('ADMINUSER') || define('ADMINUSER', 'root');
 defined('ADMINPASS') || define('ADMINPASS', base64_decode('TXlCZWF0c1Bvc3RA'));
 defined('ADMINDB') || define('ADMINDB', 'mitsql');
 
 $BASE_PATH = dirname(__FILE__).'/';
-$BASE_URL = isset($_SERVER['SCRIPT_NAME'])?dirname($_SERVER['SCRIPT_NAME']).'/':'';
+$BASE_URL = BASE_URL . '/';
+//$BASE_URL = isset($_SERVER['SCRIPT_NAME'])?dirname($_SERVER['SCRIPT_NAME']).'/':'';
 
 set_time_limit(0);
 ignore_user_abort(1);
 import_request_variables('cgp', 'i_');
 DEVEL && ini_set('display_errors', 1);
 DEVEL && error_reporting(E_ALL);
-set_include_path(get_include_path() . PATH_SEPARATOR . $BASE_PATH . 'lib/');
+set_include_path(get_include_path() . PATH_SEPARATOR . $BASE_PATH . 'lib/' . PATH_SEPARATOR . $BASE_PATH);
 
 require_once('defaults.cfg.php');
 
@@ -38,4 +43,6 @@ $cxn = mysql_connect(DBHOST, ADMINUSER, ADMINPASS);
 mysql_select_db(ADMINDB,$cxn);
 if (mysql_error()) die(mysql_error());
 
+chdir($CWD);
+
 ?>

diff --git a/mitsql.css b/mitsql.css
index 82350606fc8518348989290bf3a5fe5d57b5f4f1..f3e4017be2ede9417c4450991dab9a5055eee1f0 100644 (file)
--- a/mitsql.css
+++ b/mitsql.css
@@ -86,10 +86,10 @@ div.msg {
        margin: 0px 20px 20px 20px;
        background-color: #ccc;
        border: 1px solid black;
-}
-div.msg {
-       font-variant: small-caps;
        font-weight: bold;
+       position: relative;
+}
+div.msg li {
        list-style-type: square;
 }
 div.err {

diff --git a/tpl/main.php b/tpl/main.php
index eb6a045d7c7633c39c4948078d8662892428cb36..be194997022dd7993053c9e584a821c9eb436138 100644 (file)
--- a/tpl/main.php
+++ b/tpl/main.php
@@ -2,20 +2,22 @@
 
 include 'head.php';
 
+if (isset($i_dropask)) {
+       $dropdbs = array_keys($i_dropask);
+       echo '<form method="post">';
+       foreach($dropdbs as $dbname) {
+               $msg[] = 'Are you sure you want to drop `'.$dbname.'`? <input style="position:absolute; right:20px;" type="submit" name="drop['.$dbname.']" value="Yes">';
+       }
+       echo '</form>';
+}
+
 ?>
 <h2>Databases</h2>
 
+<form method="post">
 <?php printErrors($err); ?>
 <?php printMsgs($msg); ?>
-
-<? if (!DEVEL && 0) { ?>
-<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>
 
 <table width="100%">
 <form method="post">
@@ -31,7 +33,7 @@ Thanks for signing up.  This interface is nearly ready.  We will e-mail you as s
                else $percentage = 0;
                echo printBar($percentage, $db['Name'], sprintSize($db['nBytes']));
                echo '</td><td>';
-               echo '<input type="submit" name="drop[',$db['Name'],']" value="drop">';
+               echo '<input type="submit" name="dropask[',$db['Name'],']" value="drop">';
                echo '</td></tr>';
        }
        if ($total>0)
@@ -48,7 +50,6 @@ Thanks for signing up.  This interface is nearly ready.  We will e-mail you as s
 <input type=submit value="Add"></p>
 </form>
 
-<? } ?>
 <?php
 
 include 'foot.php';