MyWebSQL

getDatabases(); } function printDbList(&$db) { $dblist = getDbList($db); // if there is one database for the given userid, let's select it to cut out one extra step if (! Session::get('db', 'name') ) { $stDb = 0; $selDb = ""; $stDbList = $db->getStandardDbList(); foreach($dblist as $dbname) { if ( in_array($dbname, $stDbList) ) $stDb++; else $selDb = $dbname; } if (count($dblist) == ($stDb+1)) { Session::set('db', 'name', $selDb); $db->selectDb(Session::get('db', 'name')); } } // --- end of automatic selection logic if (getDbName()) { $folder = $db->name(); echo view(array($folder.'/dblist', 'dblist'), array(), $dblist); } else { print ''.__('Select a database to begin').'.'; } return $dblist; } function getDBClass() { // use the common driver to connect to database if ( ! Session::get('auth', 'valid') ) return array(BASE_PATH . '/lib/db/manager.php', 'DbManager'); $driver = Session::get('db', 'driver'); if ( !$driver || empty($driver) ) return array(BASE_PATH . '/lib/db/manager.php', 'DbManager'); $lib = BASE_PATH . '/lib/db/'.$driver.'.php'; $class = 'DB_' . ucfirst( str_replace('/', '_', $driver) ); return array($lib, $class); } function execute_request(&$db) { // do not append or prepend anything to output if we have to download $output = v($_REQUEST["type"]) == 'download' ? false: true; if ( $output ) { startForm($db); } if ( isset($_REQUEST["type"]) ) { $module_requested = $_REQUEST["type"]; $_REQUEST["query"] = trim(v($_REQUEST["query"], ""), " \t\r\n;"); $module = BASE_PATH . "/modules/".$module_requested.".php"; if (ctype_alpha( $module_requested ) && file_exists($module)) { require( BASE_PATH . '/config/modules.php' ); // check for module access type and allow/disallow as needed if (MODULE_ACCESS_MODE == 'deny' && in_array($module_requested, $DENY_MODULES)) { createErrorPage(); } else if (MODULE_ACCESS_MODE == 'allow' && !in_array($module_requested, $ALLOW_MODULES)) { createErrorPage(); } else { include($module); function_exists('processRequest') ? processRequest($db) : createErrorGrid($db, ""); } } else createErrorPage(); // unidentified type requested } if ( $output ) { print "\n"; print ""; } } function createResultGrid(&$db) { Session::del('select', 'pkey'); Session::del('select', 'ukey'); Session::del('select', 'mkey'); Session::del('select', 'unique_table'); // this is different from the one used for viewing table data $record_limit = Options::get('res-max-count', MAX_RECORD_TO_DISPLAY); //

"; print "\n"; $f = $db->getFieldInfo(); // see if all fields come from one table so we can allow editing if (Session::get('select', 'can_limit') && count($f) > 0) { Session::set('select', 'unique_table', $f[0]->table); for($i=1; $itable != Session::get('select', 'unique_table')) { // bail out, more than one table data Session::del('select', 'unique_table'); break; } } } // desc command returns table name as COLUMNS, so we make it empty //if (Session::get('select', 'unique_table') == "COLUMNS") // Session::del('select', 'unique_table'); $ed = Session::get('select', 'can_limit') && (Session::get('select', 'unique_table') == "" ? false : true);// && ($db->numRows() > 0); // ------------ print header ----------- print ""; print ""; if ($ed) print ""; $v = ""; // more than one field can be part of a primary key (composite key) Session::set('select', 'pkey', array()); Session::set('select', 'ukey', array()); Session::set('select', 'mkey', array()); $fieldNames = ''; $fieldInfo = json_encode($f); $i = 1; foreach($f as $fn) { $cls = $fn->type == 'numeric' ? "th_numeric" : "th"; print ""; $fieldNames .= "'" . str_replace("'", "\\'", $fn->name) . "',"; } print "\n"; // ------------ print data ----------- $j = 0; while($r = $db->fetchRow(0, 'num')) { $i = 0; print ""; print ""; if ($ed) print ""; foreach($r as $rs) { $class = ($rs === NULL) ? "tnl" : ($f[$i]->numeric == 1 ? "tr" : "tl"); if ($ed) $class .= ' edit'; //if ($f[$i]->blob) // $class .= $f[$i]->type == 'binary' ? ' blob' : ' text'; if (!$f[$i]->blob) $data = ($rs === NULL) ? "NULL" : (($rs === "") ? "" : htmlspecialchars($rs)); else $data = getBlobDisplay($rs, $f[$i], $j, $ed); print ""; $i++; } print "\n"; $j++; if (Session::get('select', 'can_limit') && $record_limit > 0 && $j >= $record_limit) break; } $numRows = $j; print "

#		"; if ($fn->pkey == 1) { Session::add('select', 'pkey', $fn->name); print " "; } else if ($fn->ukey == 1) { Session::add('select', 'ukey', $fn->name); print " "; } else if ($fn->mkey == 1 && !$fn->blob) // blob/text fields are FULL TEXT KEYS only Session::add('select', 'mkey', $fn->name); print $fn->name; if (Session::get('select', 'sortcol') == $i) { print ( Session::get('select', 'sort') == 'DESC' ? ' ▾' : ' ▴' ); } $i++; print "
".($j+1)."		$data

"; print ""; $editTableName = Session::get('select', 'unique_table'); $gridTitle = $editTableName == '' ? __('Query Results') : str_replace('{{TABLE}}', htmlspecialchars($editTableName), __('Data for {{TABLE}}')); print '

'. $gridTitle . '

'; $message = ''; if (Session::get('select', 'can_limit')) { // can limit be applied to this type of query (e.g. show,explain cannot be limited/sorted) if (Session::get('select', 'limit')) { // yes, and limit is applied to records by the application $total_records = Session::get('select', 'count'); $total_pages = ceil($total_records / $record_limit); $current_page = Session::get('select', 'page'); $from = (($current_page - 1) * $record_limit) + 1; $to = $from + $db->numRows() - 1; $message = "

".str_replace(array('{{START}}', '{{END}}'), array($from, $to), __('Showing records {{START}} - {{END}}'))."

"; } else { $total_records = $db->numRows(); $total_pages = 1; $current_page = 1; if ($record_limit > 0 && $total_records > $record_limit) $message = "

".str_replace('{{MAX}}', $record_limit, __('Showing first {{MAX}} records only'))."!

"; } } else { $total_records = $db->numRows(); $total_pages = 1; $current_page = 1; $message = ""; } $js = "\n"; print $js; } function createSimpleGrid(&$db, $message) { print "

"; print "

$message".__('Quick Search')."

"; print "\n"; $f = $db->getFieldInfo(); $ed = false; // ------------ print header ----------- print ""; print ""; $v = ""; foreach($f as $fn) { $cls = $fn->type == 'numeric' ? "th_numeric" : "th"; $dsrt = $fn->type == 'numeric' ? "numeric" : "text"; print ""; } print "\n"; // ------------ print data ----------- $j = 0; while($r = $db->fetchRow(0, 'num')) { $i = 0; print ""; print ""; foreach($r as $rs) { $class = ($rs === NULL) ? "tnl" : ($f[$i]->numeric == 1 ? "tr" : "tl"); if ($f[$i]->blob) $class .= $f[$i]->type == 'binary' ? ' blob' : ' text'; $data = ($rs === NULL) ? "NULL" : (($rs === "") ? " " : htmlspecialchars($rs)); print ""; $i++; } print "\n"; $j++; } $numRows = $j; print "

#	"; print $fn->name."
".($j+1)."	$data

"; print "

"; $js = "\n"; print $js; } function createErrorPage() { echo view('error_page'); } // numQueries = number of 'successful' executed queries // affectedRows = some rows maybe affected in batch processing, even if error occured function createErrorGrid(&$db, $query='', $numQueries=0, $affectedRows=-1) { if ($query == '') $query = Session::get('select', 'query'); Session::del('select', 'result'); Session::del('select', 'pkey'); Session::del('select', 'ukey'); Session::del('select', 'mkey'); Session::del('select', 'unique_table'); Session::set('select', 'result', array()); // result blob data $e = $db->getError(); print "

\n"; if ($numQueries > 0) { print "

"; $msg = ($numQueries == 1) ? __('1 query successfully executed') : str_replace('{{NUM}}', $numQueries, __('{{NUM}} queries successfully executed')); $msg .= ".

".str_replace('{{NUM}}', $affectedRows, __('{{NUM}} record(s) were affected')).".

"; print $msg . '

'; } //else // print "No query was successful"; $formatted_query = preg_replace("/[\\n|\\r]?[\\n]+/", "
", htmlspecialchars($query)); print "

".__('Error occurred while executing the query').":

".htmlspecialchars($e)."

".$formatted_query."

"; print "

"; print "\n"; } // batch process will send default params as required function createInfoGrid(&$db, $query="", $numQueries=1, $affectedRows=-1, $addHistory=true, $executionTime=false) { Session::del('select', 'pkey'); Session::del('select', 'ukey'); Session::del('select', 'mkey'); Session::del('select', 'unique_table'); if ($affectedRows == -1) $affectedRows = $db->getAffectedRows(); if ($query == "") $query = $_REQUEST["query"]; print "

\n"; print "

"; $msg = ($numQueries == 1) ? __('1 query successfully executed') : str_replace('{{NUM}}', $numQueries, __('{{NUM}} queries successfully executed')); print $msg . ".

"; print "

".str_replace('{{NUM}}', $affectedRows, __('{{NUM}} record(s) were affected'))."

"; if ($numQueries == 1) { $formatted_query = preg_replace("/[\\n|\\r]?[\\n]+/", "
", htmlspecialchars($query)); print "

".$formatted_query."

"; $warnings = $db->getWarnings(); if (count($warnings) > 0) { print '

'; foreach($warnings as $warning) print htmlspecialchars($warning) . '
'; print '

'; } } print "

"; $tm = $executionTime ? $executionTime : $db->getQueryTime(); print "\n"; } function getQueryType($query) { $type = array('result'=>FALSE,'can_limit'=>FALSE,'has_limit'=>FALSE,'update'=>FALSE); $q = trim($query, " \n\t"); $q = strtolower(substr($query, 0, 7)); // work on only first few required characters of query if( $q == "explain" || substr($q, 0, 6) == "select" || substr($q, 0, 4) == "desc" || substr($q, 0, 4) == "show" || substr($q, 0, 4) == "help" ) { $type['result'] = TRUE; if (substr($q, 0, 6) == "select") { $type['can_limit'] = TRUE; // we don't want to limit results for other queries like 'show...' preg_match(LIMIT_REGEXP, $query, $matches); if (count($matches) == 3) { $type['has_limit'] = true; } } } else $type['update'] = TRUE; return $type; } function sortQuery($query, $field) { $query = trim($query); $sort = ''; $sort_type = Session::get('select', 'sort'); if (!$sort_type) $sort_type = 'ASC'; $limit = ''; // find and extract limit clause out of query (must be the last clause, otherwise sorting will not work) preg_match(LIMIT_REGEXP, $query, $matches); if (count($matches) == 3) { $query = trim($matches[1]); $limit = trim($matches[2]); } preg_match(SORT_REGEXP, $query, $matches); if (count($matches) > 3) { $query = trim($matches[1]); // if sorting with same field, change sorting order if ( trim($matches[3]) == $field ) $sort_type = $sort_type == 'ASC' ? 'DESC' : 'ASC'; else // reset to ascending order $sort_type = 'ASC'; } Session::set('select', 'sortcol', $field); Session::set('select', 'sort', $sort_type); $sort = 'ORDER BY ' . $field . ' ' . $sort_type; $query .= ' ' . $sort . ' ' . $limit; return $query; } function getCommandInfo($sql) { $info = array('db'=>'', 'dbChanged'=>FALSE, 'dbAltered'=>FALSE, 'setvar'=>FALSE); if (preg_match('@^[\s]*USE[[:space:]]*([\S]+)@i', $sql, $match)) { $info['db'] = trim($match[1], ' ;'); $info['dbChanged'] = TRUE; } else if (preg_match('/^(CREATE|ALTER|DROP)\s+/i', $sql)) { $info['dbAltered'] = true; } else if (preg_match('/^SET[\s]+@([a-zA-z0-9_]+|`.*`|\'.*\'|".*")[\s]?=[\s]?(.*)/i', $sql, $matches)) { //SET[\s]+@([a-zA-z0-9_]+)[\s]+=[\s]+(.*) $info['setvar'] = true; $info['variable'] = trim($matches[1]); $info['value'] = trim($matches[2]); } /*preg_match('@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im', $sql)*/ return $info; } function startForm($db, $style="margin:0px;overflow:hidden;width:100%;height:100%") { print ""; print "\n"; print "MyWebSQL\n"; print "\n"; print '

'; print '

'; print "\n"; print ""; print "\n"; print ""; print ""; print ""; print ""; print ""; } function sanitizeCreateCommand($type, $cmd) { $str = preg_replace("/[\\n|\\r]?[\\n]+/", "
", htmlspecialchars($cmd)); return $str; /*if ($type == "table") return $str; else if ($type == "view") { $str = str_replace(" DEFINER=", "
DEFINER=", $str); $str = str_replace(" SQL SECURITY ", "
SQL SECURITY ", $str); $str = str_replace(" AS (", "
AS
(", $str); } else if ($type == "procedure") { $str = str_replace(" DEFINER=", "
DEFINER=", $str); $str = str_replace(" PROCEDURE ", "
PROCEDURE ", $str); $str = str_replace("BEGIN", "
BEGIN
", $str); $str = str_replace(" END", "
END", $str); } else if ($type == "function") { $str = str_replace(" DEFINER=", "
DEFINER=", $str); $str = str_replace(" FUNCTION ", "
FUNCTION ", $str); $str = str_replace("BEGIN", "
BEGIN
", $str); $str = str_replace(" END", "
END", $str); } else if ($type == "trigger") { $str = str_replace("\n", "
", $str); } return $str;*/ } function getBlobDisplay($rs, $info, $numRecord, $editable) { //$pattern = '/[\x00-\x08\x0E-\x1F\x7F]/'; for binary matching $binary = ($info->type == 'binary') ? true : false; $length = strlen($rs); $size = format_bytes($length); $span = ''; if ($rs === NULL) $span .= "NULL"; else if ($rs === "") $span .= " "; else { if (MAX_TEXT_LENGTH_DISPLAY >= $length) $span .= htmlspecialchars($rs); else if ($binary) $span .= str_replace( '{{SIZE}}', $size, __('Blob Data [{{SIZE}}]') ); else $span .= str_replace( '{{SIZE}}', $size, __('Text Data [{{SIZE}}]') ); } $extra = ""; $btype = "text"; if ($binary) { include(BASE_PATH . "/config/blobs.php"); foreach($blobTypes as $k => $v) { if ( $v[1] && matchFileHeader($rs, $v[1]) ) { $btype = $k; break; } } $extra = 'onclick="vwBlb(this, '.$numRecord.', \''.$btype.'\')"'; } $span .= ""; // for binary fields, editing is separated from interface if ($binary) { $span .= " "; return $span; } $span .= ''; // for non editable text fields, we need to show the data associated if (!$editable && $rs !== NULL && MAX_TEXT_LENGTH_DISPLAY < $length) { $extra = 'onclick="vwTxt(this, "'.$size.'", \''.$btype.'\')"'; $span .= " "; } return $span; } function setDbVar( $variable, $value ) { Session::set('vars', $variable, $value); } function loadDbVars(&$db) { $vars = Session::get_all('vars'); foreach($vars as $variable => $value) { $query = 'SET @'.$variable.' = '.$value; $db->query($query); } } ?>