From: Joe Presbrey Date: Thu, 2 Nov 2006 18:08:56 +0000 (+0000) Subject: move SQL to use external joe libraries X-Git-Url: http://andersk.mit.edu/gitweb/sql-web.git/commitdiff_plain/da4cf7c99c1129ee9dafe10c02fa787ed94ed0aa?hp=02ae379fba958da7830563fb33a89f30a49eadc6 move SQL to use external joe libraries git-svn-id: svn://presbrey.mit.edu/sql/web/dev@120 a142d4bd-2cfb-0310-9673-cb33a7e74f58 --- diff --git a/.htaccess.dev b/.htaccess.dev new file mode 100644 index 0000000..a438060 --- /dev/null +++ b/.htaccess.dev @@ -0,0 +1,71 @@ +#php_value auto_append_file "global.done.php" +#php_value auto_prepend_file "prepend.php" +#php_flag xdebug.remote_enable on +#php_flag xdebug.remote_autostart on +#php_value xdebug.remote_host "localhost" +#php_value xdebug.remote_port "9000" + +RewriteEngine On +RewriteBase /~sql/dev/ +#RewriteBase http://sql.mit.edu/ +#RewriteBase / + +#RewriteOptions MaxRedirects=1 + +#RewriteCond %{REQUEST_FILENAME}.php -f +#RewriteRule ^.* do/%{REQUEST_FILENAME} +#RewriteCond %{REQUEST_URI} !^/~administrator/mitsql/go/.+ + +##RewriteCond %{REQUEST_URI} +##RewriteCond %{REQUEST_FILENAME} !-s +#RewriteRule ^do/.+$ - [L] + +#RewriteCond %{IS_SUBREQ} !="true" +#RewriteRule ^.*$ do/index [R,L] + +##RewriteCond %{REQUEST_URI} ^.*/do/.+$ +##RewriteRule ^do/.+$ - [C] +#RewriteRule ^do/(.+)$ $1.php [PT,L] + +#RewriteRule ^do/(.+)$ $1.php [L] + +## attempt 2 +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] +# +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule \.php$ - [F] +# +##RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +# attempt 3 + +#RewriteRule \.php$ - [F,C] + +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] + +#RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +#RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP + +## REVISION 4 + +RewriteCond %{REQUEST_URI} ^.*/do/(.+)$ +RewriteRule ^do/(.+)$ $1.php [QSA] + +RewriteCond %{REQUEST_FILENAME} !-f +RewriteRule .* do/index [R,L,QSA] + +# now redundant after following conditions +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule .* do/index [R,L,QSA] + +RewriteCond %{THE_REQUEST} !^(GET|POST)\ /.+do/.+\ HTTP +RewriteCond %{REQUEST_FILENAME} !\.html$ +RewriteCond %{REQUEST_FILENAME} !\.css$ +RewriteCond %{REQUEST_FILENAME} !\.jpg$ +RewriteRule .* do/index [R,L,QSA] diff --git a/.htaccess.main b/.htaccess.main new file mode 100755 index 0000000..cddad44 --- /dev/null +++ b/.htaccess.main @@ -0,0 +1,71 @@ +#php_value auto_append_file "global.done.php" +#php_value auto_prepend_file "prepend.php" +#php_flag xdebug.remote_enable on +#php_flag xdebug.remote_autostart on +#php_value xdebug.remote_host "localhost" +#php_value xdebug.remote_port "9000" + +RewriteEngine On +RewriteBase /~sql/main/ +#RewriteBase http://sql.mit.edu/ +#RewriteBase / + +#RewriteOptions MaxRedirects=1 + +#RewriteCond %{REQUEST_FILENAME}.php -f +#RewriteRule ^.* do/%{REQUEST_FILENAME} +#RewriteCond %{REQUEST_URI} !^/~administrator/mitsql/go/.+ + +##RewriteCond %{REQUEST_URI} +##RewriteCond %{REQUEST_FILENAME} !-s +#RewriteRule ^do/.+$ - [L] + +#RewriteCond %{IS_SUBREQ} !="true" +#RewriteRule ^.*$ do/index [R,L] + +##RewriteCond %{REQUEST_URI} ^.*/do/.+$ +##RewriteRule ^do/.+$ - [C] +#RewriteRule ^do/(.+)$ $1.php [PT,L] + +#RewriteRule ^do/(.+)$ $1.php [L] + +## attempt 2 +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] +# +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule \.php$ - [F] +# +##RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +# attempt 3 + +#RewriteRule \.php$ - [F,C] + +#RewriteCond %{REQUEST_URI} !^.*/do/.+$ +#RewriteRule ^.*$ do/index [R,L] + +#RewriteCond do/%{REQUEST_FILENAME}.php -f +#RewriteRule ^do/(.+)$ $1.php [L] + +#RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP + +## REVISION 4 + +RewriteCond %{REQUEST_URI} ^.*/do/(.+)$ +RewriteRule ^do/(.+)$ $1.php [L,QSA] + +RewriteCond %{REQUEST_FILENAME} !-f +RewriteRule .* do/index [R,L,QSA] + +# now redundant after following conditions +#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP +#RewriteRule .* do/index [R,L,QSA] + +RewriteCond %{THE_REQUEST} !^(GET|POST)\ /.+do/.+\ HTTP +RewriteCond %{REQUEST_FILENAME} !\.html$ +RewriteCond %{REQUEST_FILENAME} !\.css$ +RewriteCond %{REQUEST_FILENAME} !\.jpg$ +RewriteRule .* do/index [R,L,QSA] diff --git a/admin/main.php b/admin/main.php new file mode 100755 index 0000000..c4bea86 --- /dev/null +++ b/admin/main.php @@ -0,0 +1,121 @@ +0) $sortSQL = 'ASC'; else $sortSQL = 'DESC'; + +$i_pagesize = mysql_escape_string($i_pagesize); +$i_page = mysql_escape_string($i_page); +$i_search = mysql_escape_string($i_search); +$i_sortby = mysql_escape_string($i_sortby); + +$sql_search = empty($i_search)?'':" AND Username LIKE '%$i_search%' "; + +$columns = array('','Username','Name','nBytes','nBytesHard','nBytesOver','dSignup'); + +$userCount = 0; +$sqlc = "SELECT COUNT(*) as c FROM User WHERE bEnabled=1 $sql_search"; +$r = fetchRows(DBSelect($sqlc)); +$r = array_shift($r); +count($r) && $userCount = array_shift($r); + +$sql = "SELECT User.UserId,Username,Name,dSignup,dLastCheck,nBytes,nBytesSoft,nBytesHard, + IF(nBytes>nBytesHard,nBytes-nBytesHard,0) as nBytesOver + FROM User + NATURAL JOIN UserStat + NATURAL JOIN UserQuota + WHERE bEnabled=1 + $sql_search + ORDER BY $i_sortby $sortSQL"; + +$sql .= ' LIMIT '.($i_page-1)*$i_pagesize.','.$i_pagesize; +$pageArray = range(1,ceil($userCount/$i_pagesize)); +$pageCount = count($pageArray); + +$users = fetchRows(DBSelect($sql),'UserId'); + +include 'tpl/head.php'; + +?> + +',$userCount,' users | '; +if ($i_page > 1) $tPagePrev = ' Prev'; else $tPagePrev = 'First'; +if ($i_page < $pageCount) $tPageNext = ' Next'; else $tPageNext = 'Last'; +if (!empty($tPageNext) && empty($tPagePrev)) echo($tPageNext); +elseif (empty($tPageNext) && !empty($tPagePrev)) echo($tPagePrev); +else echo "$tPagePrev | $tPageNext"; + +/* +echo ' | Skip to: '; +foreach($pageArray as $availablePage) { + echo ' '.$availablePage.''; +} +*/ + +echo ' | Page '.$i_page.' of '.$pageCount.' by '.$i_sortby; +?> + + +'; +echo ''; +foreach($columns as $column) { + if (!empty($column)) { + $qsa = array('sortby'=>$column); + if ($column==$i_sortby) + $qsa['sortorder']=abs($i_sortorder-1); + echo ''.$column.''; + } else echo ''; +} +echo ''; +foreach($users as $id=>$user) { + echo '', + $user['UserId'], + '', + '', + $user['Username'], + '', + $user['Name'], + '', + sprintSize($user['nBytes']), + '', + sprintSize($user['nBytesHard']), + '', + sprintSize($user['nBytesOver']), + '', + $user['dSignup'], +// '', +// $user['dLastCheck'], + ''; +} +echo ''; + +include 'tpl/foot.php'; + +?> diff --git a/admin/php.ini b/admin/php.ini new file mode 120000 index 0000000..9fc31db --- /dev/null +++ b/admin/php.ini @@ -0,0 +1 @@ +../php.ini \ No newline at end of file diff --git a/batch/batch.inc.php b/batch/batch.inc.php new file mode 100644 index 0000000..8b65e29 --- /dev/null +++ b/batch/batch.inc.php @@ -0,0 +1,9 @@ + diff --git a/batch/create_db.php b/batch/create_db.php new file mode 100755 index 0000000..0f994d9 --- /dev/null +++ b/batch/create_db.php @@ -0,0 +1,25 @@ +canLogin()) die('-1'); + +$User = new User($Login->getUserID()); +$myUsername = $User->getUsername(); + +if (substr($i_d,0,strlen($myUsername)+1) == $myUsername.DELIMETER) { + $i_d = explode(DELIMETER, $i_d); + array_shift($i_d); + $i_d = implode(DELIMETER, $i_d); +} + +list($msg1, $err1) = proc::newdb($User, $i_d); +if (!empty($err1)) die('-3,'.implode(' ',$err1)); +if (empty($err1)) die('0,'.$i_d); + +?> diff --git a/batch/signup.php b/batch/signup.php new file mode 100755 index 0000000..65a81a8 --- /dev/null +++ b/batch/signup.php @@ -0,0 +1,35 @@ +#!/usr/bin/php +=4) { + $myName = explode(',', $hesinfo[4]); + $myName = array_shift($myName); +} else { + $myName = $myUsername; +} +$myEmail = $myUsername.'@mit.edu'; +$myPassword = substr(md5(uniqid()),0,8); + +if ($myUID<1000) exit('bad UID'); + +$Login = new Login($myUsername); +if (!$Login->exists() && !empty($myUsername)) { + addUser(array('Name'=>$myName,'Username'=>$myUsername,'Email'=>$myEmail)); + $Login->refresh(); +} +if ($Login->canSignup()) { + $User = new User($Login->getUserId()); + $User->signup($myPassword); + die($myPassword); +} + +?> diff --git a/batch/status.php b/batch/status.php new file mode 100755 index 0000000..072f064 --- /dev/null +++ b/batch/status.php @@ -0,0 +1,14 @@ +canLogin()) die('1'); + +die('0'); + +?> diff --git a/bin/root_install_signup.sh b/bin/root_install_signup.sh new file mode 100644 index 0000000..84d250a --- /dev/null +++ b/bin/root_install_signup.sh @@ -0,0 +1,5 @@ +#!/bin/bash + +gcc signup.c -o /home/tools/bin/sql-signup +chown sql /home/tools/bin/sql-signup +chmod 4701 /home/tools/bin/sql-signup diff --git a/bin/signup.c b/bin/signup.c new file mode 100644 index 0000000..fe41149 --- /dev/null +++ b/bin/signup.c @@ -0,0 +1,6 @@ +#include + +int main() { + system("/usr/bin/php /home/sql/web_scripts/main/batch/signup.php"); + return 0; +} diff --git a/bin/updateMain.sh b/bin/updateMain.sh new file mode 100755 index 0000000..cb5d738 --- /dev/null +++ b/bin/updateMain.sh @@ -0,0 +1,6 @@ +#!/bin/bash + +pushd ~/web_scripts/main/ >/dev/null +svn up +sh ../perm.sh +popd >/dev/null diff --git a/contact.php b/contact.php new file mode 100644 index 0000000..d0e68af --- /dev/null +++ b/contact.php @@ -0,0 +1,15 @@ +getUserID()); +} + +include 'tpl/contact.php'; + +?> diff --git a/contrib/.forceauth b/contrib/.forceauth new file mode 100755 index 0000000..25dc570 --- /dev/null +++ b/contrib/.forceauth @@ -0,0 +1 @@ +Test User|test@mit.edu diff --git a/contrib/deleteuser.sql b/contrib/deleteuser.sql new file mode 100644 index 0000000..2447df9 --- /dev/null +++ b/contrib/deleteuser.sql @@ -0,0 +1,11 @@ + +SET @NAME=185; + +USE mitsql; +DELETE FROM DBQuota WHERE DatabaseId IN (SELECT DatabaseId FROM DBOwner WHERE UserId = @NAME); +DELETE FROM DB WHERE DatabaseId IN (SELECT DatabaseId FROM DBOwner WHERE UserId = @NAME); +DELETE FROM DBOwner WHERE UserId = @NAME; +DELETE FROM UserGroup WHERE UserId = @NAME; +DELETE FROM UserQuota WHERE UserId = @NAME; +DELETE FROM UserStat WHERE UserId = @NAME; +DELETE FROM `User` WHERE UserId = @NAME; diff --git a/contrib/mitsql.sql b/contrib/mitsql.sql new file mode 100644 index 0000000..2a56522 --- /dev/null +++ b/contrib/mitsql.sql @@ -0,0 +1,165 @@ +-- 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/contrib/reset.sql b/contrib/reset.sql new file mode 100644 index 0000000..84c5573 --- /dev/null +++ b/contrib/reset.sql @@ -0,0 +1,10 @@ +TRUNCATE TABLE `DB`; +TRUNCATE TABLE `DBOwner`; +TRUNCATE TABLE `DBQuota`; +TRUNCATE TABLE `Group`; +TRUNCATE TABLE `GroupQuota`; +TRUNCATE TABLE `GroupStat`; +TRUNCATE TABLE `User`; +TRUNCATE TABLE `UserGroup`; +TRUNCATE TABLE `UserQuota`; +TRUNCATE TABLE `UserStat`; diff --git a/contrib/server.cfg.php b/contrib/server.cfg.php new file mode 100755 index 0000000..00ac13c --- /dev/null +++ b/contrib/server.cfg.php @@ -0,0 +1,34 @@ +$networkAddress, + 'broadcastAddress'=>$broadcastAddress + ); +} + +$allowIP = array( + '204.9.221.18', + '18.237.0.77', + '18.237.0.90', + '18.237.0.225', + '18.237.0.227', + '18.237.0.228', + '18.239.1.6' + ); + +$IP = $_SERVER['REMOTE_ADDR']; + +if (!empty($IP) && !in_array($IP, $allowIP)) { +// header('Location: http://scripts.mit.edu/~sql/'); + header(''); + exit('403 Access denied'); +} diff --git a/cron/checkProcesses.php b/cron/checkProcesses.php new file mode 100755 index 0000000..a98a865 --- /dev/null +++ b/cron/checkProcesses.php @@ -0,0 +1,62 @@ +MAX_PROC_TIME && $row['Command']!='Sleep') { + $bad[] = $row; + $baddbs[] = $row['db']; + $badusers[] = $row['User']; + } +} +mysql_free_result($result); + +foreach($bad as $badproc) { + $mailtos = $mailnames = array(); + $baddb = mysql_escape_string($badproc['db']); + $badtime = $badproc['Time']; + $badquery = $badproc['Info']; + if (empty($baddb)) continue; + $sql = "SELECT User.UserId,User.Name,User.Email + FROM `User` + NATURAL JOIN DBOwner + NATURAL JOIN DB + WHERE DB.Name = '$baddb' + AND User.UL < 10"; + $r = fetchRows(DBSelect($sql),'UserId'); + foreach($r as $addy) { + $mailtos[] = $addy['Email']; + $mailnames[] = $addy['Name']; + } + if (empty($mailtos)) continue; + $mailto = implode(', ',$mailtos); + $mailname = implode(', ',$mailnames); + $mailsubj = "[sql] Slow Query on $baddb"; + $mailbody = "Dear $mailname: + +A slow query was found on your database: $baddb +It took $badtime seconds and has been aborted. + +We do not allow inefficient SQL queries to run this long. Your query has +been appended to this message for your records. Please optimize your +queries to avoid having your queries killed in the future. If you have any +questions, please contact sql@mit.edu. + +This SQL Service is available at sql.mit.edu. + +--------------------------------------------------------------------------- + +$badquery"; + + mysql_query('KILL '.$badproc['Id']); + mail($mailto,$mailsubj,$mailbody,"From: SQL Service \r\nBcc: sql@mit.edu\r\n"); +} + +?> diff --git a/cron/checkQuotas.php b/cron/checkQuotas.php new file mode 100644 index 0000000..e372390 --- /dev/null +++ b/cron/checkQuotas.php @@ -0,0 +1,10 @@ + diff --git a/defaults.cfg.php b/defaults.cfg.php new file mode 100644 index 0000000..10dceb3 --- /dev/null +++ b/defaults.cfg.php @@ -0,0 +1,29 @@ + diff --git a/error.php b/error.php new file mode 100755 index 0000000..6446c24 --- /dev/null +++ b/error.php @@ -0,0 +1,11 @@ + diff --git a/global.act.php b/global.act.php new file mode 100644 index 0000000..724ab54 --- /dev/null +++ b/global.act.php @@ -0,0 +1,64 @@ +update($SSLCred['Name'],$SSLCred['Email']); + + if (!isLoggedIn() && !$LoginSSL->exists()) { + if (!empty($SSLName)) + addUser($SSLCred); + $LoginSSL->refresh(); + } + } else { + unset($_SESSION['LoginSSL']); + } + + /* + if (isPost() || isset($i_refresh)) { + if (!empty($UserId)) { + checkQuotas($UserId); + } + isset($i_refresh) && redirect('main?r'); + } + */ + +} // isOnline() + +?> diff --git a/global.done.php b/global.done.php new file mode 100644 index 0000000..fcd343d --- /dev/null +++ b/global.done.php @@ -0,0 +1,22 @@ +'; + print_r($_SESSION); + print_r($timings); + isset($Login) && print_r($Login); + isset($User) && print_r($User); +} + +?> diff --git a/index.php b/index.php new file mode 100755 index 0000000..e825968 --- /dev/null +++ b/index.php @@ -0,0 +1,20 @@ +canSignup() && redirect('signup'); +//redirect('login'); + +include 'tpl/index.php'; + +?> diff --git a/lib/dbaccess.lib.php b/lib/dbaccess.lib.php new file mode 100644 index 0000000..150aeab --- /dev/null +++ b/lib/dbaccess.lib.php @@ -0,0 +1,96 @@ +\n".mysql_error(),E_USER_ERROR); + return $res; +} + +function DBSelect($sql) { return DBSlave($sql); } +function DBInsert($sql) { + DBMaster($sql); + if (mysql_error()) trigger_error($sql."
\n".mysql_error(),E_USER_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) { + $sql_result = "SHOW DATABASES LIKE '".mysql_escape_string($tdb)."'"; + $result = DBShow($sql_result); + if (!mysql_num_rows($result)) return null; + + $sql_result = "SHOW TABLE STATUS FROM `" .mysql_escape_string($tdb)."`"; + $result = DBShow($sql_result); + + if($result) { + $size = 0; + while ($data = mysql_fetch_array($result)) { + $size += $data["Data_length"] + $data["Index_length"]; + } + mysql_free_result($result); + return $size; + } + else { + return null; + } +} + +function checkQuotas($userId=null) { + if (empty($userId)) { + $sql = 'SELECT DatabaseId,Name FROM DB WHERE bEnabled=1'; + } else { + $sql = sprintf("SELECT DB.DatabaseId,Name FROM DB INNER JOIN DBOwner ON DB.DatabaseId = DBOwner.DatabaseId WHERE bEnabled=1 AND UserId = '%s'", mysql_escape_string($userId)); + } + $databases = fetchRows(DBSelect($sql),'Name'); + foreach($databases as $db) { + $DBId = $db['DatabaseId']; + $arr['dLastCheck'] = 'NOW()'; + $arr['nBytes'] = calcDBSize($db['Name']); + $sql = sprintf("UPDATE DB %s WHERE DatabaseId = '%s'", + buildSQLSet($arr), + mysql_escape_string($DBId)); + DBUpdate($sql); + } + $sql = "UPDATE UserStat SET nBytes = ( + SELECT SUM(nBytes) + FROM DB + INNER JOIN DBOwner ON DBOwner.DatabaseId = DB.DatabaseId + WHERE DBOwner.UserId = UserStat.UserId + AND DB.bEnabled=1 + GROUP BY UserId + ), dLastCheck = NOW()"; + if (!empty($userId)) $sql .= sprintf(" WHERE UserId = '%s'", mysql_escape_string($userId)); + DBUpdate($sql); + $sql = "UPDATE UserStat SET nDatabases = ( + SELECT COUNT(*) + FROM DB + INNER JOIN DBOwner ON DBOwner.DatabaseId = DB.DatabaseId + WHERE DBOwner.UserId = UserStat.UserId + AND DB.bEnabled=1 + GROUP BY UserId + ), dLastCheck = NOW()"; + if (!empty($userId)) $sql .= sprintf(" WHERE UserId = '%s'", mysql_escape_string($userId)); + DBUpdate($sql); +} + +?> diff --git a/lib/display.lib.php b/lib/display.lib.php new file mode 100644 index 0000000..e33721d --- /dev/null +++ b/lib/display.lib.php @@ -0,0 +1,76 @@ +1) { + $percent = $percent/100; + } + } elseif (!is_integer($percent) && $percent<2) { + $percent = $percent*100; + } elseif ($percent == 1) { + $percent = 100; + } */ + $per1 = $per2 = floor($percent*100); + if ($per1>100) { + $per1 = 100; + $per2 = 0; + } else { + $per2 = 100 - $per2; + } + $per1 .= "%"; + $per2 .= "%"; +/* return ' + + + +
 '.$txt1.' 
 
 
'.$txt2.' '.$per1.'
';*/ + return ' + + + + +
+ + + + +
'.$txt1.'
'.$txt2.'  '.$per1.'
'; +} + +function chars_encode($string) { + $chars = array(); + $ent = null; + $chars = preg_split('//', $string, -1, PREG_SPLIT_NO_EMPTY); + for ($i = 0; $i < count($chars); $i++) + $ent[$i] = '&#' . ord($chars[$i]) . ';'; + if (sizeof($ent) < 1) return ''; + return implode('',$ent); +} + +?> diff --git a/lib/errorhandler.lib.php b/lib/errorhandler.lib.php new file mode 100644 index 0000000..647e40a --- /dev/null +++ b/lib/errorhandler.lib.php @@ -0,0 +1,105 @@ +
'.$error_msg.'
'; + } else { + $error_msg .= print_r(get_included_files(),1)."\n"; + $error_msg .= print_r($errcontext,1); + error_log($error_msg, 1, $email_to); + if ($error_halt) { + while(ob_get_level()) { ob_end_clean(); } + require_once('security.lib.php'); + redirect('error'); + exit -1; + } + } +} + +function ErrorBacktrace($debug_backtrace) { + array_shift($debug_backtrace); + $output = ''; + foreach ($debug_backtrace as $bt) { + $args = ''; + foreach ($bt['args'] as $a) { + if (!empty($args)) { + $args .= ', '; + } + switch (gettype($a)) { + case 'integer': + case 'double': + $args .= $a; + break; + case 'string': + $a = htmlspecialchars(substr($a, 0, 64)).((strlen($a) > 64) ? '...' : ''); + $args .= "\"$a\""; + break; + case 'array': + //$args .= 'Array('.count($a).')'; + $args .= print_r($a,1); + break; + case 'object': + $args .= 'Object('.get_class($a).')'; + break; + case 'resource': + $args .= 'Resource('.strstr($a, '#').')'; + break; + case 'boolean': + $args .= $a ? 'True' : 'False'; + break; + case 'NULL': + $args .= 'Null'; + break; + default: + $args .= 'Unknown'; + } + } + empty($bt['class']) && $bt['class'] = ''; + empty($bt['type']) && $bt['type'] = ''; + empty($bt['function']) && $bt['function'] = ''; + $output .= "\n"; + $output .= "file: {$bt['line']} - {$bt['file']}\n"; + $output .= "call: {$bt['class']}{$bt['type']}{$bt['function']}($args)\n"; + } + return $output; +} + +set_error_handler('ErrorHandler'); + +?> diff --git a/lib/joe.lib.php b/lib/joe.lib.php new file mode 100755 index 0000000..d36cef1 --- /dev/null +++ b/lib/joe.lib.php @@ -0,0 +1,211 @@ + 0) { + $arr = array(); + if (is_null($key)) { + while ($r = mysql_fetch_assoc($rs)) { + $arr[] = $r; + } + } elseif (is_numeric($key)) { + while ($r = mysql_fetch_row($rs)) { + $arr[$r[$key]] = $r; + } + } else { + while ($r = mysql_fetch_assoc($rs)) { + $arr[$r[$key]] = $r; + } + } + mysql_free_result($rs); + return $arr; + } else { + mysql_free_result($rs); + return array(); + } +} + +function printErrors($err) { printList('err', $err); } +function printMsgs($err) { printList('msg', $err); } + +function printList($class,$err) { + if (is_array($err) && count($err)) { + echo '
',(count($err)>1?'
    ':''); + foreach($err as $e) { + if (count($err)>1) { + echo '

  • ',$e,'

    • '; + } else { + echo '

    ',$e,'

    '; + } + } + echo (count($err)>1?'
':''),'
'; + } +} + +function buildSQLSet($fields, $values=null) { + $ex = array('NOW()','NULL'); + $sql = 'SET'; + $c = 0; + if (!is_null($values)) { + foreach($fields as $field) { + if ($c++) $sql .= ','; + $sql .= " `$field`='".mysql_escape_string(array_shift($values))."'"; + } + } else { + foreach($fields as $field=>$value) { + if ($c++) $sql .= ','; + if (in_array($value,$ex)) { + $sql .= " `$field`= $value"; + } else { + $sql .= " `$field`='".mysql_escape_string($value)."'"; + } + } + } + return $sql; +} + +function buildSQLInsert($array, $table=null) { + $ex = array('NOW()','NULL'); + $sql = '('; + $c = 0; + foreach($array as $field=>$value) { + if ($c++) $sql .= ','; + $sql .= " `$field` "; + } + $sql .= ') VALUES ('; + $c = 0; + foreach($array as $field=>$value) { + $v = mysql_escape_string($value); + if ($c++) $sql .= ','; + if (in_array($v, $ex)) + $sql .= " $v "; + else + $sql .= " '$v' "; + } + $sql .= ')'; + return (is_null($table)?$sql:('INSERT INTO `'.$table.'` '.$table)); +} + +function build_str($query_array) { + $query_string = array(); + foreach ($query_array as $k => $v) { + $new = $k; + if (strlen($v)) + $new .= '='.$v; + $query_string[] = $new; + } + return join('&', $query_string); +} + +function newQS($key, $val=null) { + /* + parse_str($_SERVER['QUERY_STRING'], $arr); + $arr[$key] = $val; + return '?'.build_str($arr); + */ + return newQSA(array($key=>$val)); +} + +function newQSA($array=array()) { + parse_str($_SERVER['QUERY_STRING'], $arr); + $s = count($arr); + foreach($array as $key=>$val) { + $arr[$key] = $val; + if (is_null($val)) + unset($arr[$key]); + } + return (count($arr)||$s)?'?'.build_str($arr):''; +} + +function formQSA($array=array()) { + if (!count($array)) $array = $_SERVER['QUERY_STRING']; + parse_str($array, $arr); + $text = ''; + foreach($arr as $key=>$val) { + $text .= sprintf('', $key, $val); + } + return $text; +} + +?> diff --git a/lib/mitsql.lib.php b/lib/mitsql.lib.php new file mode 100755 index 0000000..519e2d2 --- /dev/null +++ b/lib/mitsql.lib.php @@ -0,0 +1,38 @@ + diff --git a/lib/proc.lib.php b/lib/proc.lib.php new file mode 100644 index 0000000..adf8252 --- /dev/null +++ b/lib/proc.lib.php @@ -0,0 +1,49 @@ +getUsername(); + $uname = str_replace('.','',$uname); + $dbname = $uname.DELIMETER.$i_newdb; + if ($User->isOverQuota() && !isAdmin()) { + $err1[] = 'You are over your quota. You may not add more databases.'; + } elseif (count($User->getDBList())>MAXDBS && !isAdmin()) { + $err1[] = 'You have too many databases. You may not add more databases.'; + } elseif (empty($i_newdb)) { + $err1[] = 'Your database name may not be empty.'; + } elseif (!$User->addDB($dbname)) { + if (mysql_error()) { + $err1[] = mysql_error(); + } else { + $err1[] = 'Database already exists.'; + } + } else { + $msg1[] = 'Database `'.$dbname.'` created.'; + } + $i_newdb = $dbname; + return array($msg1, $err1); + } + static function drop(&$User, $i_drop) { + $msg1 = $err1 = array(); + $dropdbs = array_keys($i_drop); + foreach($dropdbs as $dbname) { + if ($User->delDB($dbname)) { + $msg1[] = 'Database `'.$dbname.'` dropped.'; + } else { + $err1[] = mysql_error(); + } + } + return array($msg1, $err1); + } +} + +?> diff --git a/lib/security.lib.php b/lib/security.lib.php new file mode 100644 index 0000000..0b7c384 --- /dev/null +++ b/lib/security.lib.php @@ -0,0 +1,400 @@ +u = $u; + $this->p = $p; + if (is_numeric($u)) { + $this->id = $u; + $opt = sprintf(" Username = '%s' OR UserId = '%s'", mysql_escape_string($u), 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; + } + function exists() { + return count($this->info); + } + function isValid() { + return $this->getUL()>0; + } + function isEnabled() { + return $this->exists() && $this->info['bEnabled']==1; + } + function canLogin() { + return $this->isEnabled() && $this->isValid(); + } + function canSignup() { + return !$this->isEnabled() && $this->isValid(); + } + function getUserId() { + return $this->exists()?$this->info['UserId']:''; + } + function getUsername() { + return $this->exists()?$this->info['Username']:''; + } + function getName() { + return $this->exists()?$this->info['Name']:''; + } + function getEmail() { + return $this->exists()?$this->info['Email']:''; + } + function getUL() { + return $this->exists()?$this->info['UL']:''; + } + function expire() { + $this->info = null; + } + function refresh() { + 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; + $arr = array(); + if ($name == $this->getName()) $name = null; + if ($email == $this->getEmail()) $email = null; + is_null($name) || $arr['Name'] = $name; + is_null($email) || $arr['Email'] = $email; + $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 (isset($arr['Name'])) + $this->info['Name'] = $arr['Name']; + if (isset($arr['Email'])) + $this->info['Email'] = $arr['Email']; + } +} + +class User { + private $userId; + private $info; + private $dblist; + function User($userId) { + $this->userId = $userId; + $sql = sprintf("SELECT User.UserId, Username, Name, Email, UL, bEnabled, nBytesSoft, nBytesHard, nBytes, nDatabases, nDatabasesHard, IF(nBytes>nBytesHard,1,0) AS bOverQuota + FROM User + 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'); + $this->info = count($r)?array_shift($r):$r; + $this->dblist = $this->getDBList(); + } + function refresh() { + unset($this->dblist); + $this->User($this->userId); + /* + $sql = sprintf("SELECT UserId, Username, Name, Email, UL, bEnabled + FROM User + WHERE UserId = '%s'", + mysql_escape_string($this->userId)); + $r = fetchRows(DBSelect($sql),'UserId'); + $this->info = count($r)?array_shift($r):$r; + unset($this->dblist); + $this->getDBList(); + */ + } + function exists() { + return count($this->info); + } + function getUserId() { + return $this->exists()?$this->info['UserId']:''; + } + function getUsername() { + return $this->exists()?$this->info['Username']:''; + } + function isOverQuota() { + return $this->exists()?($this->info['bOverQuota']>0?true:false):''; + } + function getBytes() { + if($this->exists()) { + $arr['nBytes'] = $this->info['nBytes']; + $arr['nBytesSoft'] = $this->info['nBytesSoft']; + $arr['nBytesHard'] = $this->info['nBytesHard']; + return $arr; + } + } + function setPassword($pwd) { + $arr['Password'] = base64_encode($pwd); + $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; + $arr['Password'] = base64_encode($pwd); + $arr['bEnabled'] = 1; + $arr['dSignup'] = 'NOW()'; + $sql = sprintf("UPDATE User %s WHERE UserId = '%s'", + buildSQLSet($arr), mysql_escape_string($this->getUserId())); + DBUpdate($sql); + + $this->setUsage(); + $this->setAccess(); + } + function setUsage($yes=true) { + $verb = $yes?'GRANT':'REVOKE'; + $prep = $yes?'TO':'FROM'; + $suffix = $yes?sprintf("IDENTIFIED BY '%s'",mysql_escape_string($this->pass)):''; + $sql = sprintf("%s USAGE ON * . * %s '%s'@'%s' %s", + mysql_escape_string($verb), + mysql_escape_string($prep), + mysql_escape_string($this->getUsername()), + '%', + $suffix); + DBGrant($sql); + } + function setAccess($db=null,$yes=true) { + $verb = $yes?'GRANT':'REVOKE'; + $prep = $yes?'TO':'FROM'; + if (is_null($db)) { + $dbs = $this->getDBList(); + } else { + $dbs[] = array('Name'=>$db); + } + foreach($dbs as $db) { + $name = $db['Name']; + $sql = sprintf("%s ALL PRIVILEGES ON `%s` . * %s '%s'@'%s'", + mysql_escape_string($verb), + mysql_escape_string($name), + mysql_escape_string($prep), + mysql_escape_string($this->getUsername()), + '%'); + DBGrant($sql); + } + } + function getDBList() { + if (isset($this->dblist)) { + return $this->dblist; + } else { + // LEFT JOIN DBQuota ON DBQuota.DatabaseId = DBOwner.DatabaseId + $sql = sprintf("SELECT * + FROM DBOwner + 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'); + $r = fetchRows(DBSelect($sql),'Name'); + ksort($r); + return $r; + } + } + function addDB($name) { + if (in_array($name, array_keys($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; + } +} + + +function isLoggedIn($aLogin=null) { + if (is_null($aLogin)) { + global $Login; + $aLogin = $Login; + } + return !empty($aLogin) && ($aLogin instanceof Login) && $aLogin->canLogin(); +} + +function isAdmin($aLogin=null) { + if (is_null($aLogin)) { + global $Login; + $aLogin = $Login; + } + return !empty($aLogin) && ($aLogin instanceof Login) && $aLogin->getUL()>=100; +} + +function isImpersonating() { + return isSess('_UserId') && isSess('UserId'); +} + +function isOffline() { + return (defined('OFFLINE') && OFFLINE); +} + +function isOnline() { + return !isOffline(); +} + +function impersonate($userId=null) { + $wasImpersonating = isImpersonating(); + if ($wasImpersonating) { + if (is_null($userId) || empty($userId)) { + sess('UserId',sess('_UserId')); + sess('_UserId',''); + } elseif ($userId>0) { + sess('UserId',$userId); + } else { + return false; + } + } elseif (isLoggedIn()) { + sess('_UserId',sess('UserId')); + sess('UserId',$userId); + return true; + } else { + return false; + } +} + +function isSSL() { + return isset($_SERVER['SERVER_PORT'])?($_SERVER['SERVER_PORT'] == 443):false; +} + +function getSSLCert() { + if (DEVEL && file_exists('.forceauth')) { + $fu = explode('|',file_get_contents('.forceauth')); + $name = trim($fu[0]); + $email = trim($fu[1]); + } else { + $name = isset($_SERVER['SSL_CLIENT_S_DN_CN'])?$_SERVER['SSL_CLIENT_S_DN_CN']:null; + $email = isset($_SERVER['SSL_CLIENT_S_DN_Email'])?$_SERVER['SSL_CLIENT_S_DN_Email']:null; + } + if (!is_null($email)) { + $user = explode('@',$email); + $user = $user[0]; + return array('Username'=>$user, 'Name'=>$name, 'Email'=>$email); + } else { + return null; + } +} + +## 302 REDIRECTS + +function redirect($target=null,$secure=null) { + $base = (is_null($target)||substr($target,0,1)=='?')?$_SERVER['REDIRECT_URL']:(dirname($_SERVER['REDIRECT_URL']).'/'); + redirectFull(is_null($target)?$base:($base.$target),$secure); +} +function redirectStart() { + redirectFull(BASE_URL,null); +} +function redirectFull($target,$secure) { + //redirect2((((isSSL()&&is_null($secure))||$secure==true)?'https://':'http://').$_SERVER['SERVER_NAME'].$target); + redirect2((((isSSL()&&is_null($secure))||$secure==true)?'https://scripts-cert.mit.edu':'http://scripts.mit.edu').$target); +} +function redirect2($target) { + header('Location: '.$target); + exit; +} +function flipSSL() { + //return (isSSL()?'http://':'https://').$_SERVER['SERVER_NAME'].$_SERVER['REDIRECT_URL']; + return (isSSL()?'http://scripts.mit.edu':'https://scripts-cert.mit.edu').$_SERVER['REDIRECT_URL']; +} + +## USER SCRIPTS + +function addUser($sslCredentials) { + global $_NEW_USER, $_NEW_USERQUOTA, $_NEW_USERSTAT; + + $arr = array_merge($sslCredentials, $_NEW_USER); + $sql = sprintf("INSERT INTO User %s", + buildSQLInsert($arr)); + $UserId = DBInsert($sql); + + $arr = $_NEW_USERQUOTA; + $arr['UserId'] = $UserId; + $sql = sprintf("INSERT INTO UserQuota %s", + buildSQLInsert($arr)); + DBInsert($sql); + + $arr = $_NEW_USERSTAT; + $arr['UserId'] = $UserId; + $sql = sprintf("INSERT INTO UserStat %s", + buildSQLInsert($arr)); + DBInsert($sql); + + 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 IGNORE INTO DB %s", + buildSQLInsert($arr)); + $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); + } + + DBDelete(sprintf("DELETE FROM DBOwner WHERE DatabaseId = '%s'", mysql_escape_string($DBId))); + DBDelete(sprintf("DELETE FROM DBQuota WHERE DatabaseId = '%s'", mysql_escape_string($DBId))); + + $arr = $_NEW_DBQUOTA; + $arr['DatabaseId'] = $DBId; + $sql = sprintf("INSERT IGNORE 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); + + 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 true; +} + +?> diff --git a/login.php b/login.php new file mode 100644 index 0000000..4858893 --- /dev/null +++ b/login.php @@ -0,0 +1,42 @@ +exists() && !$Login->isEnabled()) { + $err[] = 'Account not active. Did you signup yet?.'; + } elseif (!$Login->exists()) { + $err[] = 'Nonexistant account or invalid password.'; + } elseif (!$Login->canLogin()) { + $err[] = 'That account is no longer valid. Please contact the staff (sql@mit.edu).'; + } + + if (empty($err)) { + sess('UserId', $Login->getUserId()); + redirect('main?refresh'); + } + +} + +isLoggedIn() && redirect('main'); + +include 'tpl/login.php'; + +?> diff --git a/logout.php b/logout.php new file mode 100644 index 0000000..1b8b5d5 --- /dev/null +++ b/logout.php @@ -0,0 +1,11 @@ +getUserID()); + +if (isPost()) { + if (isset($i_newdb)) { + list($msg1, $err1) = proc::newdb($User, $i_newdb); + } + if (isset($i_drop)) { + list($msg1, $err1) = proc::drop($User, $i_drop); + } +} + +if (!count($err1)) {//&& !isset($i_r)) + checkQuotas($Login->getUserID()); + $User->refresh(); +} + +$myDBs = $User->getDBList(); + +include 'tpl/main.php'; + +?> diff --git a/mitsql.cfg.php b/mitsql.cfg.php new file mode 100755 index 0000000..6a5acfd --- /dev/null +++ b/mitsql.cfg.php @@ -0,0 +1,61 @@ + diff --git a/mitsql.css b/mitsql.css new file mode 100644 index 0000000..979deee --- /dev/null +++ b/mitsql.css @@ -0,0 +1,57 @@ +#content_wide form { + margin: 0; + display: inline; +} +#content_wide p { + font-size: 10pt; + text-indent: 25px; +} +#content_wide h2 { + margin-top: 0; +} +#content_wide input { + font-size: 9pt; + border: 1px solid black; +} +#content_wide table,tr,td { + padding: 0; +} +td p { + padding-right: 10px; + padding-left: 10px; + border-left: 1px solid black; +} +div.msg { + padding: 0px 20px 0px 0px; + background-color: #ccc; + border: 1px solid black; + font-weight: bold; + position: relative; +} +div.msg li { + list-style-type: square; +} +div.err { + color: maroon; + padding: 0px 20px 0px 0px; + background-color: #ccc; + border: 1px dashed black; + font-variant: small-caps; + font-weight: bold; +} +div.err li { + list-style-type: square; +} +pre { + font-size: 10px; +} +table.bargraph { + border: 1px solid #ccc; +} +table.bargraph td.bar { + height: 18px; + background-color: #888; +} +table.bargraph td.fill { + background-color: #bbb; +} diff --git a/offline.php b/offline.php new file mode 100755 index 0000000..96e9641 --- /dev/null +++ b/offline.php @@ -0,0 +1,13 @@ + diff --git a/php.ini b/php.ini new file mode 100755 index 0000000..62a9be2 --- /dev/null +++ b/php.ini @@ -0,0 +1,4 @@ +extension = mysql.so +auto_append_file = "global.done.php" +session.save_path = "/afs/athena.mit.edu/contrib/sql/web_tmp" +output_buffering = 4096 diff --git a/setup.php b/setup.php new file mode 100755 index 0000000..70e4e1e --- /dev/null +++ b/setup.php @@ -0,0 +1,30 @@ +getUserID()); + +if (isPost()) { + if (isset($i_chgpw)) { + $p1 = (isset($i_p[1])?$i_p[1]:''); + $p2 = (isset($i_p[2])?$i_p[2]:''); + if (empty($p1)) { + $err[] = 'You may not have a blank password.'; + } elseif ($p1 != $p2) { + $err[] = 'Your confirmation password does not match.'; + } else { + $User->setPassword($p1); + $msg[] = 'Your password was changed.'; + } + } +} + +include 'tpl/setup.php'; + +?> diff --git a/signup.php b/signup.php new file mode 100644 index 0000000..96aba8c --- /dev/null +++ b/signup.php @@ -0,0 +1,40 @@ +canSignup()) { + if (empty($i_p1)) { + $err[] = 'Your password may not be blank.'; + } elseif ($i_p1 != $i_p2) { + $err[] = 'Your confirmation password does not match.'; + } else { + $u = new User($LoginSSL->getUserId()); + $u->signup($i_p1); + $LoginSSL->refresh(); + redirect('login', true); + } + } elseif (isPost()) { + $err[] = 'You may not signup.'; + } + } +} + +include 'tpl/signup.php'; + +?> diff --git a/test.php b/test.php new file mode 100755 index 0000000..2c6210a --- /dev/null +++ b/test.php @@ -0,0 +1,49 @@ +isEnabled(); +echo $l->isValid(); +print_r($GLOBALS); +*/ + +//phpinfo(); + +//checkQuotas(); + +printMsgs(array('this is a test msg: test 0')); +printMsgs(array('this is a test msg: test 1', 'this is a test msg: test 2')); + +printErrors(array('this is a test error: test 0')); +printErrors(array('this is a test error: test 1', 'this is a test error: test 2')); + +include 'tpl/foot.php'; +?> diff --git a/tpl/contact.php b/tpl/contact.php new file mode 100755 index 0000000..3ab17f3 --- /dev/null +++ b/tpl/contact.php @@ -0,0 +1,18 @@ + + +

Contact/Help

+ + + +

+We welcome all questions, comments, and suggestions.
+

+

+Please direct inquiries to sql at mit.edu +

+ + diff --git a/tpl/error.php b/tpl/error.php new file mode 100755 index 0000000..873de8e --- /dev/null +++ b/tpl/error.php @@ -0,0 +1,20 @@ + + +

Page Error

+ +

+The page you were trying to view experienced an error while loading. +An email reporting this error has been sent to the Support Team. +

+

+Please accept our apologies for this inconvenience. +

+

+Return Home. +

+ + diff --git a/tpl/foot.php b/tpl/foot.php new file mode 100644 index 0000000..4f46d85 --- /dev/null +++ b/tpl/foot.php @@ -0,0 +1,21 @@ + + + + + + + +
 
+ + + + + + + + + diff --git a/tpl/head.php b/tpl/head.php new file mode 100644 index 0000000..bffc1ed --- /dev/null +++ b/tpl/head.php @@ -0,0 +1,38 @@ + + + + + + MIT SIPB MySQL Service for Athena + + + + + +
+
+
+
+

sql.mit.edu

+

MIT SIPB MySQL Service for Athena
+ email:

+
+
+ +
+
+
diff --git a/tpl/index.php b/tpl/index.php new file mode 100644 index 0000000..9b940f1 --- /dev/null +++ b/tpl/index.php @@ -0,0 +1,26 @@ + + +

+This service provides MySQL databases to MIT certificate holders. +You must choose a MySQL password (which should be different from your Athena account password) when you sign up, 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 choice; +simply connect to the MySQL server at sql.mit.edu using your username and your new MySQL password. +You may find it convenient to run scripts using the web script service or +shortjobs service. +

+ +

+All uses of this service must comply with the MITnet rules of use. +

+ +

+Although this service has been designed with reliability in mind, the SIPB MySQL service should not be used to host critical applications that cannot tolerate downtime. +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. +We maintain general usage statistics of this service by the MIT community at large. +

+ + diff --git a/tpl/login.php b/tpl/login.php new file mode 100644 index 0000000..a9ce2b5 --- /dev/null +++ b/tpl/login.php @@ -0,0 +1,40 @@ + + +

Login

+ + + + + +
+

Please enter your MySQL login information:

+

username:

+

password:

+ + + + +

You are identified as:

+

+

+ + + +canLogin()): ?> + + +
+ + + +

Sign up to use this service.

+ + + + diff --git a/tpl/main.php b/tpl/main.php new file mode 100644 index 0000000..d3744eb --- /dev/null +++ b/tpl/main.php @@ -0,0 +1,68 @@ +'; + foreach($dropdbs as $dbname) { + $msg1[] = 'Are you sure you want to drop `'.$dbname.'`? '; + } + echo ''; +} + +?> +

Databases

+ +
+ + +
+ + + +getBytes(); + $usage = $bytes['nBytes']; + $total = $bytes['nBytesHard']; + if (!count($myDBs)) { + echo ''; + } else + foreach($myDBs as $db) { + echo ''; + } + if ($total>0) { + $percentage = $usage/$total; + } else { + $percentage = 0; + } + echo ''; + echo ''; +?> + +
You have no databases. Add one below.
'; + if ($total>0) + $percentage = $db['nBytes']/$total; + else $percentage = 0; + echo printBar($percentage, $db['Name'], str_replace(' ', ' ', sprintSize($db['nBytes']))); + echo ''; + echo ''; + echo '
'; + echo '
'; + echo '
'; + echo printBar($percentage, 'TOTAL USED', str_replace(' ', ' ', sprintSize($usage).' of '.sprintSize($total))); + echo '
+ +
+

+

+
+ +

Manage Data

+

One interface we recommend for managing SQL data is phpMyAdmin. Feel free to use it after you've created your databases.

+ + diff --git a/tpl/menu.php b/tpl/menu.php new file mode 100644 index 0000000..b96bb46 --- /dev/null +++ b/tpl/menu.php @@ -0,0 +1,36 @@ +
+ +
diff --git a/tpl/offline.php b/tpl/offline.php new file mode 100755 index 0000000..c330bc7 --- /dev/null +++ b/tpl/offline.php @@ -0,0 +1,16 @@ + + +

System Maintenance

+ +

+The MySQL Service interface is temporarily offline while we perform system maintenance. +Please check back with us in a few minutes for interface access recommencement. +We apologize for the delay in service and inconvenience. +In the meantime, feel free to Contact Us. +

+ + diff --git a/tpl/setup.php b/tpl/setup.php new file mode 100755 index 0000000..284a69a --- /dev/null +++ b/tpl/setup.php @@ -0,0 +1,25 @@ + + +

Change MySQL Password

+ +Your MySQL password should be different from your Athena account password.
+
+ + + + +
+ +*/?> + + + +
old password:
new password:
repeat new password:

+
+ + diff --git a/tpl/signup.php b/tpl/signup.php new file mode 100644 index 0000000..b980a4a --- /dev/null +++ b/tpl/signup.php @@ -0,0 +1,34 @@ + + +

Signup

+ + + +canSignup()): ?> +

You are registering as: getUsername()?>

+

getName()?>

+

getEmail()?>

+ +Your MySQL password should be different from your Athena account password. + +
+

+

+ +
+ +canLogin()): ?> + +

Your account [getUsername()?>] is already signed up.

+ + + + + +