--- /dev/null
+<?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");
+?>
projects / eow / blobdiff