From: Joe Presbrey <presbrey@mit.edu>
Date: Fri, 9 Sep 2005 04:21:16 +0000 (+0000)
Subject: changed NATURAL JOINs to INNERs to avoid conflicting keys
X-Git-Url: http://andersk.mit.edu/gitweb/sql-web.git/commitdiff_plain/e47be57bed672a6c24c368463808e9737fc32ad8

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 b8eaf60..fb99d9b 100755
--- 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 18d11a4..2f65289 100644
--- 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 04c4dc1..5ea684a 100755
--- 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 b275c30..a7dba99 100755
--- 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 c898f76..7557465 100644
--- 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 d59a921..6d0e19b 100644
--- 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 a534a8a..f83bc9a 100755
--- 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 8235060..f3e4017 100644
--- 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 eb6a045..be19499 100644
--- 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';