| <?php |
| // db settings |
| $dbserver = 'localhost'; |
| $dbuser = 'root'; |
| $dbpassword = 'root'; |
| |
| error_reporting(E_ALL); |
| |
| /* |
| Simple protocol: |
| - Inputs via POST variables. |
| - Output is a string that can be evaluated into a JSON |
| First element of the array contains return status. |
| |
| This simplified tutorial code should not be deployed without a security review. |
| */ |
| |
| @include "json.php"; |
| |
| // set up response encoding |
| header("Content-Type: text/html; charset=utf-8"); |
| |
| // util |
| function getPostString($inName) { |
| // make sure input strings are 'clean' |
| return mysql_real_escape_string(@$_POST[$inName]); |
| } |
| |
| // used for json encoding |
| $json = new Services_JSON(); |
| |
| function echoJson($inData) { |
| global $json; |
| // delay in ms |
| $delay = getPostString('delay'); |
| if (!empty($delay)) |
| usleep($delay * 1000); |
| echo '/* ' . $json->encode($inData) . ' */'; |
| } |
| |
| function error($inMessage) { |
| $inMessage = str_replace('"', '\\"', $inMessage); |
| error_log($inMessage); |
| //echo '/* ({error: true, message: "' . $inMessage . '"}) */'; |
| echoJson(array('error' => true, 'message' => $inMessage)); |
| exit; |
| } |
| |
| |
| function getArray($inResult, $inArray="true") { |
| $o = Array(); |
| while ($row = ($inArray ? mysql_fetch_row($inResult) : mysql_fetch_object($inResult))) |
| $o[] = $row; |
| return $o; |
| } |
| |
| // connect to DB |
| mysql_connect($dbserver, $dbuser, $dbpassword); |
| |
| // select DB |
| $database = getPostString("database"); |
| $database = ($database ? $database : $db); |
| if (!mysql_select_db($database)) |
| error('failed to select db: ' . mysql_error()); |
| |
| // select table |
| $table = getPostString("table"); |
| $table = ($table ? $table : $dbtable); |
| |
| // cache |
| $colCache = NULL; |
| $pkCache = NULL; |
| |
| // set UTF8 output (MySql > 4.0) |
| mysql_query("SET NAMES UTF8"); |
| |
| // server, database, table meta data |
| function getDatabases() { |
| $result = mysql_query("SHOW DATABASES"); |
| $output = Array(); |
| while ($row = mysql_fetch_row($result)) { |
| $r = strtolower($row[0]); |
| if ($r != 'mysql' && $r != 'information_schema') |
| $output[] = $row[0]; |
| } |
| return $output; |
| } |
| |
| function getTables() { |
| global $database; |
| $result = mysql_query("SHOW TABLES FROM $database"); |
| $output = Array(); |
| while ($row = mysql_fetch_row($result)) |
| $output[] = $row[0]; |
| return $output; |
| } |
| |
| function getColumns() { |
| global $table, $colCache; |
| if (!$colCache) { |
| $result = mysql_query("SHOW COLUMNS FROM `$table`"); |
| return getArray($result, false); |
| $colCache = getArray($result, false); |
| } |
| return $colCache; |
| } |
| |
| // returns object: $this->name, $this->index |
| function getPk() { |
| global $pkCache; |
| if (!$pkCache) { |
| $k = ''; |
| $columns = getColumns(); |
| for ($i=0; $i < count($columns); $i++) { |
| $c = $columns[$i]; |
| if ($c->Key == 'PRI') { |
| $k = $c->Field; |
| break; |
| } |
| } |
| $pkCache->index = $i; |
| $pkCache->name = $k; |
| } |
| return $pkCache; |
| } |
| |
| function getTableInfo() { |
| global $table, $database; |
| $c = getColumns(); |
| $r = rowcount(); |
| return array("count" => $r, "columns" => $c, "database" => $database, "table" => $table); |
| } |
| |
| function getOldPostPkValue() { |
| $pk = getPk(); |
| return getPostString('_o' . $pk->index); |
| } |
| |
| function getNewPostPkValue() { |
| $pk = getPk(); |
| return getPostString('_' . $pk->index); |
| } |
| |
| function getPostColumns() { |
| $columns = getColumns(); |
| for ($i=0, $a=array(), $p; (($p=getPostString("_".$i)) != ''); $i++) { |
| $r = new stdClass(); |
| $r->name = $columns[$i]->Field; |
| $r->value = $p; |
| $a[] = $r; |
| } |
| return $a; |
| } |
| |
| function getOrderBy() { |
| $ob = getPostString("orderby"); |
| if (is_numeric($ob)) { |
| $columns = getColumns(); |
| $ob = $columns[intval($ob)-1]->Field; |
| } |
| return $ob; |
| } |
| |
| function getWhere() { |
| $w = getPostString("where"); |
| return ($w ? " WHERE $w" : ""); |
| } |
| |
| // basic operations |
| function rowcount() { |
| global $table; |
| $query = "SELECT COUNT(*) FROM `$table`" . getWhere(); |
| $result = mysql_query($query); |
| if (!$result) |
| error("failed to perform query: $query. " . mysql_error()); |
| if ($row = mysql_fetch_row($result)) |
| return $row[0]; |
| else |
| return 0; |
| } |
| |
| function select($inQuery = '') { |
| global $table; |
| // built limit clause |
| $lim = (int)getPostString("limit"); |
| $off = (int)getPostString("offset"); |
| $limit = ($lim || $off ? " LIMIT $off, $lim" : ""); |
| // build order by clause |
| $desc = (boolean)getPostString("desc"); |
| $ob = getOrderBy(); |
| $orderby = ($ob ? " ORDER BY `" . $ob . "`" . ($desc ? " DESC" : "") : ""); |
| // build query |
| $query = ($inQuery ? $inQuery : "SELECT * FROM `$table`" . getWhere() . $orderby . $limit); |
| // execute query |
| if (!$result = mysql_query($query)) |
| error("failed to perform query: $query. " . mysql_error()); |
| // fetch each result row |
| return getArray($result); |
| } |
| |
| function reflectRow() { |
| global $table; |
| $pk = getPk(); |
| $key = getNewPostPkValue(); |
| $where = "`$pk->name`=\"$key\""; |
| return select("SELECT * FROM `$table` WHERE $where LIMIT 1"); |
| } |
| |
| function update() { |
| // build set clause |
| for ($i=0, $set = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++) |
| $set[] = "`$v->name` = '$v->value'"; |
| $set = implode(', ', $set); |
| // our table |
| global $table; |
| // build query |
| $pk = getPk(); |
| $pkValue = getOldPostPkValue(); |
| $query = "UPDATE `$table` SET $set WHERE `$pk->name` = '$pkValue' LIMIT 1"; |
| // execute query |
| if (!mysql_query($query)) |
| error("failed to perform query: [$query]. " . |
| "MySql says: [" . mysql_error() ."]"); |
| else { |
| return reflectRow(); |
| } |
| } |
| |
| function insert() { |
| global $table; |
| // build values clause |
| for ($i=0, $values = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++) |
| $values[] = $v->value; |
| $values = '"' . implode('", "', $values) . '"'; |
| // build query |
| $query = "INSERT INTO `$table` VALUES($values)"; |
| // execute query |
| if (!mysql_query($query)) |
| error("failed to perform query: [$query]. " . |
| "MySql says: [" . mysql_error() ."]"); |
| else { |
| return reflectRow(); |
| } |
| } |
| |
| function delete() { |
| global $table; |
| // build query |
| $n = getPostString("count"); |
| $pk = getPk(); |
| for ($i = 0, $deleted=array(); $i < $n; $i++) { |
| $key = getPostString("_$i"); |
| array_push($deleted, $key); |
| $query = "DELETE FROM `$table` WHERE `$pk->name`=\"$key\" LIMIT 1"; |
| // execute query |
| if (!mysql_query($query) || mysql_affected_rows() != 1) |
| error("failed to perform query: [$query]. " . |
| "Affected rows: " . mysql_affected_rows() .". " . |
| "MySql says: [" . mysql_error() ."]"); |
| } |
| return $deleted; |
| } |
| |
| // find (full text search) |
| function findData($inFindCol, $inFind, $inOrderBy, $inFullText) { |
| global $table; |
| $where = ($inFullText ? "WHERE MATCH(`$inFindCol`) AGAINST ('$inFind')" : "WHERE $inFindCol LIKE '$inFind'"); |
| $query = "SELECT * FROM $table $where $inOrderBy"; |
| $result = mysql_query($query); |
| // return rows |
| return getArray($result); |
| } |
| |
| // binary search through sorted data, supports start point ($inFindFrom) and direction ($inFindForward) |
| function findRow($inData, $inFindFrom=-1, $inFindForward) { |
| $b = -1; |
| $l = count($inData); |
| if (!$inData) |
| return $b; |
| if (!$inFindFrom==-1 || $l < 2) |
| $b = 0; |
| else { |
| // binary search |
| $t = $l-1; |
| $b = 0; |
| while ($b <= $t) { |
| $p = floor(($b+$t)/2); |
| $d = $inData[$p][0]; |
| if ($d < $inFindFrom) |
| $b = $p + 1; |
| else if ($d > $inFindFrom) |
| $t = $p - 1; |
| else { |
| $b = $p; |
| break; |
| } |
| } |
| if ($inFindFrom == $inData[$b][0]) { |
| // add or subtract 1 |
| $b = ($inFindForward ? ($b+1 > $l-1 ? 0 : $b+1) : ($b-1 < 0 ? $l-1 : $b-1) ); |
| } |
| else if (!$inFindForward) |
| // subtract 1 |
| $b = ($b-1 < 0 ? $l-1 : $b-1); |
| } |
| return $inData[$b][0]; |
| } |
| |
| function buildFindWhere($inFindData, $inKey, $inCol) { |
| $o = Array(); |
| foreach($inFindData as $row) |
| $o[] = $inCol . "='" . $row[$inKey] . "'"; |
| return (count($o) ? ' WHERE ' . implode(' OR ', $o) : ''); |
| } |
| |
| function find($inFindCol, $inFind='', $inOb='', $inFindFrom=0, $inFindForward=true, $inFullText=true) { |
| global $table; |
| // build order by clause |
| $desc = (boolean)getPostString("desc"); |
| if (!$inOb) |
| $inOb = getOrderBy(); |
| if ($inOb) |
| $inOb = "`" . $inOb . "`" ; |
| $orderby = ($inOb ? " ORDER BY $inOb " . ($desc ? " DESC" : "") : ""); |
| // update inputs from post |
| if (!$inFind) |
| $inFind = getPostString('findText'); |
| if (!$inFindCol) |
| $inFindCol = getPostString('findCol'); |
| if (empty($inFindFrom)) |
| $inFindFrom = getPostString('findFrom'); |
| $ff = getPostString('findForward'); |
| if ($ff) |
| $inFindForward = (strtolower($ff) == 'true' ? true : false); |
| $ft = getPostString('findFullText'); |
| if ($ft) |
| $inFullText = (strtolower($ft) == 'true' ? true : false); |
| |
| // get find data |
| $f = findData($inFindCol, $inFind, $orderby, $inFullText); |
| $pk = getPk(); |
| |
| // execute query |
| $where = buildFindWhere($f, $pk->index, 'f'); |
| $query = "SELECT Row, f FROM (SELECT @row := @row + 1 AS Row, $pk->name as f FROM `$table` $orderby) AS tempTable $where"; |
| mysql_query('SET @row = -1;'); |
| if (!$result = mysql_query($query)) |
| error("failed to perform query: $query. " . mysql_error()); |
| |
| // return row number |
| return findRow(getArray($result), $inFindFrom, $inFindForward); |
| } |
| |
| // our command list |
| $cmds = array( |
| "count" => "rowcount", |
| "select" => "select", |
| "update" => "update", |
| "insert" => "insert", |
| "delete" => "delete", |
| "find" => "find", |
| "databases" => "getDatabases", |
| "tables" => "getTables", |
| "columns" => "getColumns", |
| "info" => "getTableInfo" |
| ); |
| |
| // process input params |
| $cmd = @$_POST["command"]; |
| |
| //$cmd="select"; |
| |
| // dispatch command |
| $func = @$cmds[$cmd]; |
| if (function_exists($func)) |
| echoJson(call_user_func($func)); |
| else |
| error("bad command"); |
| ?> |