New file |
0,0 → 1,379 |
<?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"); |
?> |