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 . '})';
?>
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 . '})';
?>