mitsql.lib.php

   1 <?php
   2 /*      mitsql.lib.php
   3         (c) 2005 Joe Presbrey
   4         written for beta SIPB/MIT SQL service in conjunction with scripts.mit.edu
   5 */
   6
   7 define('DEBUG', 0);
   8 define('VERSION', '0.1beta');
   9
  10 define('DEFAULT_MAX_DBS', 5);
  11 define('DEFAULT_MAX_SIZE', 26214400);
  12 define('DEFAULT_MAX_DB_SIZE', 0);
  13
  14 define('FORM_METHOD', 'GET');
  15
  16 define('PASS_MIN_LEN', 4);
  17 define('DELIMETER', '+');
  18
  19 define('TEXT_NOREG', 'Your account is not registered with this utility.');
  20 define('TEXT_DBLIMIT', 'You\'ve reached your database limit.');
  21 define('TEXT_FIXREG', 'Fixing database registration for: ');
  22 define('TEXT_NOTREG', 'Database operations are allowed only for those registered to your account.');
  23 define('TEXT_REGPASSTOOSHORT', 'Your SQL password must be at least '.PASS_MIN_LEN.' characters.');
  24
  25 define('HOST', 'sql.mit.edu');
  26 define('ADMINUSER', 'root');
  27 //define('ADMINPASS', 'b6808ded2');
  28 define('ADMINPASS', 'MyBeatsPost@');
  29 //define('ADMINPASS', base64_decode('YjY4MDhkZWQy'));
  30 define('MANAGEDB', 'mitsql');
  31 define('MANAGEDBTABLE', 'db');
  32 define('MANAGEOWNERTABLE', 'owner');
  33
  34 define('SQLSELECT', 'SELECT * FROM %s');
  35 define('SQLSELECTWHERE', 'SELECT * FROM %s WHERE %s');
  36 define('SQLSELECT1WHERE', 'SELECT * FROM %s WHERE %s LIMIT 1');
  37 define('SQLINSERT', 'INSERT INTO %s VALUES (%s)');
  38 define('SQLDELETE', 'DELETE FROM %s WHERE %s');
  39 define('SQLDELETE1', 'DELETE FROM %s WHERE %s LIMIT 1');
  40
  41 $cxn = mysql_connect(HOST, ADMINUSER, ADMINPASS);
  42
  43 function redirectLocal($target) {
  44         $dir = dirname($_SERVER['PHP_SELF'])=='/'?'':dirname($_SERVER['PHP_SELF']);
  45         redirectFull($dir.$target);
  46 }
  47 function redirectFull($target) {
  48         $ssl = $_SERVER['SERVER_PORT'] == 443 ? true : false;
  49         redirect(($ssl?'https://':'http://').$_SERVER['SERVER_NAME'].$target);
  50 }
  51 function redirect($target) {
  52         if (ob_get_contents()) ob_end_clean();
  53         header('Location: '.$target);
  54         exit;
  55 }
  56
  57 function sprintSize($bytes, $float=2) {
  58         if (is_null($bytes)) return null;
  59         $kb = round($bytes / 1024, $float);
  60         $mb = round($bytes / 1024 / 1024, $float);
  61         $gb = round($bytes / 1024 / 1024 / 1024, $float);
  62
  63         return ($bytes<1||$kb<1?$bytes.' B':($mb<1?$kb.' KB':($gb<1?$mb.' MB':$gb.' GB')));
  64 }
  65
  66 function sprintTS($timestamp) {
  67         return substr($timestamp,0,4).
  68                 '-'.substr($timestamp,4,2).
  69                 '-'.substr($timestamp,6,2).
  70                 ' '.substr($timestamp,8,2).
  71                 ':'.substr($timestamp,10,2).
  72                 ':'.substr($timestamp,12,2);
  73 }
  74
  75 function getDBSize($tdb) {
  76    mysql_select_db($tdb);
  77
  78    $sql_result = "SHOW TABLE STATUS FROM `" .mysql_escape_string($tdb)."`";
  79    $result = @mysql_query($sql_result);
  80
  81    if($result) {
  82        $size = 0;
  83        while ($data = mysql_fetch_array($result)) {
  84              $size += $data["Data_length"] + $data["Index_length"];
  85        }
  86        return $size;
  87    }
  88    else {
  89        return null;
  90    }
  91 }
  92
  93 function getSSLIdent() {
  94         if (isset($_SERVER['SSL_CLIENT_S_DN_Email'])) {
  95                 $sslemail = $_SERVER['SSL_CLIENT_S_DN_Email'];
  96                 $ssluser = explode('@',$sslemail);
  97                 $ssluser = $ssluser[0];
  98                 return $ssluser;
  99         } else {
 100                 return null;
 101         }
 102 }
 103
 104 class DBManage {
 105         var $username = "", $dbs;
 106         var $r_owner;
 107         var $sizeNow = 0;
 108
 109         function DBManage($username = "") {
 110                 if (!empty($username)) {
 111                         $this->username = $username;
 112                         $this->load();
 113                         if ($this->fix())
 114                                 $this->load();
 115                 }
 116         }
 117         function load() {
 118                 $rs = mysql_db_query(MANAGEDB,
 119                         sprintf(SQLSELECT1WHERE,
 120                                 MANAGEOWNERTABLE,
 121                                 'name="'.mysql_escape_string($this->username).'"'));
 122                         //or exit(mysql_error());
 123                 while($r = mysql_fetch_assoc($rs)) {
 124                         $this->r_owner = $r;
 125                 }
 126                 mysql_free_result($rs);
 127
 128                 $dbs = array();
 129
 130                 $rs = mysql_db_query(MANAGEDB,
 131                         sprintf(SQLSELECTWHERE,
 132                                 MANAGEDBTABLE,
 133                                 'owner="'.mysql_escape_string($this->username).'" ORDER BY name')) or exit(mysql_error());
 134                 while($r = mysql_fetch_assoc($rs)) {
 135                         $t = $r;
 136                         $t['registered'] = 1;
 137                         $t['granted'] = 0;
 138                         $dbs[$r['name']] = $t;
 139                 }
 140                 mysql_free_result($rs);
 141
 142                 $rs = mysql_db_query('mysql',
 143                         sprintf(SQLSELECTWHERE,
 144                                 MANAGEDBTABLE,
 145                                 'User="'.mysql_escape_string($this->username).'" ORDER BY Db'));
 146                 while($r = mysql_fetch_assoc($rs)) {
 147                         if (!isset($dbs[$r['Db']])) {
 148                                 $dbs[$r['Db']] = array();
 149                                 $dbs[$r['Db']]['registered'] = 0;
 150                         }
 151                         $dbs[$r['Db']]['granted'] = 1;
 152                         $dbs[$r['Db']]['name'] = $r['Db'];
 153                 }
 154                 mysql_free_result($rs);
 155
 156                 foreach($dbs as $d1=>$d2) {
 157                         $size = getDBSize($d1);
 158                         if (is_null($size)) {
 159                                 $dbs[$d1]['sizeNow'] = 0;
 160                                 $dbs[$d1]['exists'] = 0;
 161                         } else {
 162                                 $dbs[$d1]['sizeNow'] = $size;
 163                                 $this->sizeNow += $size;
 164                                 $dbs[$d1]['exists'] = 1;
 165                         }
 166                 }
 167                 $this->dbs = $dbs;
 168         }
 169         function isInit() {
 170                 return !is_null($this->r_owner);
 171         }
 172         function grantDB($dbname) {
 173                 mysql_query('GRANT ALL PRIVILEGES ON '
 174                                 .'`'.mysql_escape_string($dbname).'` . * '
 175                                 .'TO \''.mysql_escape_string($this->username).'\'@\'%\'') or exit(mysql_error());
 176                 $this->flushPriv();
 177         }
 178         function flushPriv() {
 179                 mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());
 180         }
 181         function revokeDB($dbname) {
 182                 mysql_db_query('mysql',
 183                         'DELETE FROM `db` WHERE '
 184                         .'User = \''.mysql_escape_string($this->username).'\' '
 185                         .'AND Db = \''.mysql_escape_string($dbname).'\'') or exit(mysql_error());
 186                 $this->flushPriv();
 187         }
 188         function setPassword($password) {
 189                 mysql_query('SET PASSWORD FOR '.
 190                 '\''.mysql_escape_string($this->username).'\'@\'%\'='.
 191                 'PASSWORD(\''.mysql_escape_string($password).'\')') or exit(mysql_error());
 192         }
 193         function init($password, $maxDBs = DEFAULT_MAX_DBS, $maxSize = DEFAULT_MAX_SIZE) {
 194                 mysql_query('GRANT USAGE ON * . * TO '.
 195                         '\''.mysql_escape_string($this->username).'\'@\'%\' '.
 196                         'IDENTIFIED BY \''.mysql_escape_string($password).'\'') or exit(mysql_error());
 197                 $this->flushPriv();
 198                 mysql_db_query(MANAGEDB,
 199                         sprintf(SQLINSERT,
 200                                         MANAGEOWNERTABLE,
 201                                         sprintf("'%s','%s','%s',NOW(),NOW()",
 202                                                 mysql_escape_string($this->username),
 203                                                 $maxDBs,
 204                                                 $maxSize))) or exit(mysql_error());
 205         }
 206         function uninit() {
 207                 mysql_db_query('mysql',
 208                         'DELETE FROM `user` WHERE '
 209                         .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
 210                 mysql_db_query('mysql',
 211                         'DELETE FROM `db` WHERE '
 212                         .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
 213                 mysql_db_query('mysql',
 214                         'DELETE FROM `tables_priv` WHERE '
 215                         .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
 216                 mysql_db_query('mysql',
 217                         'DELETE FROM `columns_priv` WHERE '
 218                         .'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
 219                 mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());
 220
 221                 mysql_db_query(MANAGEDB,
 222                         sprintf(SQLDELETE,
 223                                         MANAGEDBTABLE,
 224                                         'owner=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());
 225
 226                 foreach($this->dbs as $db)
 227                         $this->dropDB($db['name']);
 228
 229                 mysql_db_query(MANAGEDB,
 230                         sprintf(SQLDELETE1,
 231                                         MANAGEOWNERTABLE,
 232                                         'name=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());
 233         }
 234         function registerDB($name, $maxSize = DEFAULT_MAX_DB_SIZE) {
 235                 mysql_db_query(MANAGEDB,
 236                         sprintf(SQLINSERT,
 237                                         MANAGEDBTABLE,
 238                                         sprintf("'%s','%s','%s','',NOW(),NOW()",
 239                                                 mysql_escape_string($name),
 240                                                 mysql_escape_string($this->username),
 241                                                 $maxSize))) or exit(mysql_error());
 242         }
 243         function unregisterDB($name) {
 244                 mysql_db_query(MANAGEDB,
 245                         sprintf(SQLDELETE1,
 246                                         MANAGEDBTABLE,
 247                                         'name="'.mysql_escape_string($name).'"')) or exit(mysql_error());
 248         }
 249         function createDB($name) {
 250                 @mysql_create_db($name);
 251         }
 252         function dropDB($name) {
 253                 @mysql_drop_db($name);
 254         }
 255         function addDB($name) {
 256                 if (!$this->isExists($name))
 257                         $this->createDB($name);
 258                 if (!$this->isRegistered($name))
 259                         $this->registerDB($name);
 260                 if (!$this->isGranted($name))
 261                         $this->grantDB($name);
 262         }
 263         function delDB($name) {
 264                 $this->revokeDB($name);
 265                 $this->unregisterDB($name);
 266                 $this->dropDB($name);
 267         }
 268         function getNumDBs() {
 269                 return count($this->dbs);
 270         }
 271         function getTotalSize() {
 272                 return $this->sizeNow;
 273         }
 274         function isRegistered($name) {
 275                 if (isset($this->dbs[$name]))
 276                         return $this->dbs[$name]['registered'];
 277                 else return false;
 278         }
 279         function isExists($name) {
 280                 if (isset($this->dbs[$name]))
 281                         return $this->dbs[$name]['exists'];
 282                 else return false;
 283         }
 284         function isGranted($name) {
 285                 if (isset($this->dbs[$name]))
 286                         return $this->dbs[$name]['granted'];
 287                 else return false;
 288         }
 289
 290         function printOwnerHeader() {
 291                 echo '<th><tr>';
 292                 echo '<td class="header2"><p></p></td>';
 293                 echo '<td class="header1"><p>databases:</p></td>';
 294                 echo '<td class="header2"><p>storage:</p></td>';
 295                 echo '<td class="header1"><p>modified:</p></td>';
 296 //              echo '<td class="header2"><p>created:</p></td>';
 297                 echo '</tr></th>';
 298         }
 299         function printOwner() {
 300                 $obj_owner = $this->r_owner;
 301                 echo '<table>';
 302                 $this->printOwnerHeader();
 303                 echo '<td><p>status:</p></td>';
 304                 echo '<td><p>',$this->getNumDBs(),'</p></td>';
 305                 echo '<td><p>',sprintSize($this->getTotalSize()),'</p></td>';
 306                 echo '</tr><tr>';
 307                 echo '<td><p>limits:</p></td>';
 308                 echo '<td><p>',$obj_owner['maxDB'],'</p></td>';
 309                 echo '<td><p>',sprintSize($obj_owner['maxSize']),'</p></td>';
 310                 echo '<td><p>',sprintTS($obj_owner['modified']),'</p></td>';
 311 //              echo '<td><p>',sprintTS($obj_owner['created']),'</p></td>';
 312                 echo '</tr>';
 313                 echo '</table>';
 314         }
 315         function printOwnerDBs($showOwner = false) {
 316                 $rows = $this->dbs;
 317                 echo '<th><tr>';
 318                 echo '<td class="header1"><p>name:</p></td>';
 319                 echo '<td class="header2"><p>owner:</p></td>';
 320                 echo '<td class="header1"><p>last size:</p></td>';
 321                 echo '<td class="header2"><p>size:</p></td>';
 322                 echo '<td class="header1"><p>modified:</p></td>';
 323 //              echo '<td class="header2"><p>created:</p></td>';
 324                 echo '</tr></th>';
 325                 foreach($rows as $name=>$r) {
 326                         echo '<tr>';
 327                         echo '<td><p>',$name,'</p></td>';
 328                         echo '<td><p>',$r['owner'],'</p></td>';
 329         /*              echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
 330                         echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
 331                         echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
 332                         echo '<td><p>',sprintTS($r['modified']),'</p></td>';
 333 //                      echo '<td><p>',sprintTS($r['created']),'</p></td>';
 334
 335                         echo '<form method="',FORM_METHOD,'"><td>';
 336                         echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
 337                         echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
 338
 339                         echo '<form method="',FORM_METHOD,'"><td>';
 340                         echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
 341                         echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
 342
 343                         echo '<form method="',FORM_METHOD,'"><td><input type=hidden name="op" value="deldb"><input type=hidden name="name" value="',$name,'"><input type=submit value="X"></td></form>';
 344                         echo '</tr>';
 345                         flush();
 346                 }
 347         }
 348         function fix() {
 349                 $fixed = false;
 350                 foreach($this->dbs as $db) {
 351                         if (!$db['registered']) {
 352                                 echo '<p class="err">',TEXT_FIXREG,$db['name'],'</p>';
 353                                 flush();
 354                                 $this->registerDB($db['name']);
 355                                 $fixed = true;
 356                         }
 357                 }
 358                 return $fixed;
 359         }
 360         function getNumMaxDBs() {
 361                 return $this->r_owner['maxDB'];
 362         }
 363 }
 364
 365 class DBAdmin {
 366         function mysqlDBList() {
 367                 $rs = mysql_query('SHOW DATABASES');
 368                 $dbs = array();
 369                 while($r = mysql_fetch_row($rs)) {
 370                         $dbs[$r[0]] = array();
 371                         $dbs[$r[0]]['registered'] = 0;
 372                         $dbs[$r[0]]['granted'] = 0;
 373                         $dbs[$r[0]]['exists'] = 1;
 374                         $dbs[$r[0]]['owner'] = '';
 375                         $dbs[$r[0]]['sizeLast'] = 0;
 376                         $dbs[$r[0]]['sizeNow'] = '';
 377                         $dbs[$r[0]]['modified'] = '';
 378                         $dbs[$r[0]]['created'] = '';
 379                 }
 380                 return $dbs;
 381         }
 382         function fullDBList() {
 383                 //$dbs = array();
 384                 $dbs = $this->mysqlDBList();
 385
 386                 $rs = mysql_db_query(MANAGEDB,
 387                         sprintf(SQLSELECT,
 388                                 MANAGEDBTABLE)) or exit(mysql_error());
 389                 while($r = mysql_fetch_assoc($rs)) {
 390                         $t = $r;
 391                         $t['registered'] = 1;
 392                         $t['granted'] = 0;
 393                         $dbs[$r['name']] = $t;
 394                 }
 395                 mysql_free_result($rs);
 396
 397                 $rs = mysql_db_query('mysql',
 398                         sprintf(SQLSELECT,
 399                                 MANAGEDBTABLE));
 400                 while($r = mysql_fetch_assoc($rs)) {
 401                         if (!isset($dbs[$r['Db']])) {
 402                                 $dbs[$r['Db']] = array();
 403                                 $dbs[$r['Db']]['registered'] = 0;
 404                         }
 405                         $dbs[$r['Db']]['granted'] = 1;
 406                         $dbs[$r['Db']]['name'] = $r['Db'];
 407                 }
 408                 mysql_free_result($rs);
 409
 410                 foreach($dbs as $d1=>$d2) {
 411                         $size = getDBSize($d1);
 412                         if (is_null($size)) {
 413                                 $dbs[$d1]['sizeNow'] = 0;
 414                                 $dbs[$d1]['exists'] = 0;
 415                         } else {
 416                                 $dbs[$d1]['sizeNow'] = $size;
 417                                 //$this->sizeNow += $size;
 418                                 $dbs[$d1]['exists'] = 1;
 419                         }
 420                 }
 421                 return $dbs;
 422         }
 423         function printDBs($rows) {
 424                 if (ob_get_contents()) ob_end_flush();
 425                 echo '<th><tr>';
 426                 echo '<td class="header1"><p>name:</p></td>';
 427                 echo '<td class="header2"><p>owner:</p></td>';
 428                 echo '<td class="header1"><p>last size:</p></td>';
 429                 echo '<td class="header2"><p>size:</p></td>';
 430                 echo '<td class="header1"><p>modified:</p></td>';
 431                 echo '<td class="header2"><p>created:</p></td>';
 432                 echo '</tr></th>';
 433                 foreach($rows as $name=>$r) {
 434                         echo '<tr>';
 435                         echo '<td><p>',$name,'</p></td>';
 436 //                      echo '<td><p>',$r['owner'],'</p></td>';
 437                         echo '<td><p><a href="?op=admin&owner=',$r['owner'],'">',$r['owner'],'</a></p></td>';
 438         /*              echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
 439                         echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
 440                         echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
 441                         echo '<td><p>',sprintTS($r['modified']),'</p></td>';
 442                         echo '<td><p>',sprintTS($r['created']),'</p></td>';
 443
 444                         echo '<td><pre>';
 445                         echo $r['exists']?'E':'';
 446                         echo $r['granted']?'G':'';
 447                         echo $r['registered']?'R':'';
 448                         echo '</pre></td>';
 449
 450                         echo '<form method="',FORM_METHOD,'"><td>';
 451                         echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
 452                         echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
 453
 454                         echo '<form method="',FORM_METHOD,'"><td>';
 455                         echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
 456                         echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
 457
 458                         echo '<form method="',FORM_METHOD,'"><td><input type=hidden name="op" value="deldb"><input type=hidden name="name" value="',$name,'"><input type=submit value="X"></td></form>';
 459                         echo '</tr>';
 460                         flush();
 461                 }
 462         }
 463 }