PDA

View Full Version : mySQL to JSON phpscript...comments welcome



sintax.era
21 Jan 2008, 2:05 PM
Hi all, this is a script I just wrote to retrieve JSON data from a mySQL database.
Ive had a quick go at securing it, as you dont want to give anyone access to your mySQL database via the client.
Ive used a simple md5 checksum. Any criticism or comments would be greatly appreciated.
used like this (if it were get, not post) http://server/mySQL2PHP.php?table=customers&check=abc123yourmd5hashgoeshere
and outputs JSON for a dataStore.


<?
function sanitize($input){
return ereg_replace("[^A-Za-z0-9]", "", $input);
}

$user='?'; //user
$pw='?'; //user password
$db='?'; //name of database
$table= sanitize($_POST['table']); //name of table data stored in
$check= sanitize($_POST['check']); //md5 Checksum (Table name appended with secret key)
$md = md5($table.'secretkey'); // Table name appended with secret key
if ($md != $_GET['check']) die('Incorrect Checksum');

$connection = mysql_connect("localhost", $user, $pw) or die("Could not connect: " . mysql_error());
mysql_select_db($db) or die("Could not select database");
$sql = 'SELECT * FROM ' . $table;
$result = mysql_query($sql);
while($rec = mysql_fetch_array($result, MYSQL_ASSOC)){
$arr[] = $rec;
};
$data = json_encode($arr);
echo '({"results":' . $data . '})';
?>

devnull
22 Jan 2008, 3:21 PM
Hope you dont mind a bit of constructive criticism :)
It would make sense to me to return any errors in JSON rather than die()'ing out and causing Ajax.request to puke. Also missing one more error handler- the actual sql could be malformed or invalid (in this case that could really only be a bad table name, but still...), which would cause $result to be null and by extension $arr to be null (which would again cause dataProxy to blow up). In my scripts I have an if/then on the $result = mysql_query() lines; if it fails then I return the error, as well as the generated sql as members of the JSON (so that they can at least be seen in the firebug console).

sintax.era
28 Jan 2008, 11:13 AM
Excellent points :D