Improved btraced PHP server code

Forum / topics about Btraced protocol and webservice (uploading gps data) Also Btraced sample code.

Improved btraced PHP server code

Postby nyzeta » Thu May 28, 2015 1:05 pm

It happens so that I've but some time in generating code for the server side of btraced. The sample files works well, but the code generates some overhead in the SQL-tables and loading a big database might not be that efficient.
Pre-requirements is a SQL-server with one database and that you know how to add users. You will need at least one user but one user for each cellphone is adviced.

The scripts are located at checkin.example.com and the main page runs from example.com. replace with whatever suits you.

I've also added a token-functionality (static for the moment) that adds a low level of extra security in therms of automated scripts trying to abuse your page. Without the token you are redirected to the main page. If you also provide a devId when calling the page from a regular browser you will be sent to a page returning the most recent (and ongoing) trip as a json in case you would like to use the data together with a google-maps or openstreetmaps API. Inside the returning Json there are 'posdata' that contain an array of coordinates, 'zoom' that put a zoomlevel to google-maps and openstreetmaps (can and should be modified according to the size of your window) and finally 'center' to be able to place the center of your track in senter of your choosen window.

Since its so popular doing injection of code these days, I've also rewritten the whole SQL-input to use PDO and bindparam functionality. It should be harder to inject code now :twisted:

The system needs at least a user with read access and a user with select, insert and update access. They can be the same user, but I recommend not to. The select,insert and update username and password will be given to the cellphone user because the username and password from the XML is forwarded to the mysql insert functions. Btraced needs a custom url. It will be http://checkin.example.com/?atoken=[your 32bit hash here]

The database is filled with tables with this script
Code: Select all
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE TABLE IF NOT EXISTS tblPointdata (
  ID int(11) NOT NULL AUTO_INCREMENT,
  PointID int(11) NOT NULL,
  PointDate datetime NOT NULL,
  PointLat double NOT NULL,
  PointLon double NOT NULL,
  PointHAccu double NOT NULL,
  PointVAccu double NOT NULL,
  PointAltitude double NOT NULL,
  PointContinuos int(11) NOT NULL,
  PointRelativeDistance double NOT NULL,
  PointTotalTime int(11) NOT NULL,
  TripdataID int(11) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS tblTripdata (
  ID int(11) NOT NULL AUTO_INCREMENT,
  DevID varchar(100) NOT NULL,
  TripID int(11) NOT NULL,
  TripName varchar(40) NOT NULL,
  TripLength float NOT NULL,
  TripTotalPoints int(11) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

As you see I've made two tables, one for the trip info and one for the point info.
Then there are two main files. functions.php and index.php

index.php:
Code: Select all
<?php

include_once "functions.php";

// First of all we fetch data from input
$body = @file_get_contents('php://input');

// Then we fetch a parameter from the url to see if we are supposed to show some data at all
$atoken = (isset($_GET['atoken']) ? $_GET['atoken'] : NULL);

if(empty($body) && ($atoken == $ctoken)) {
   //Lets present some usefull data because the user has token but no data
      
   if(isset($_GET['devid'])) {
      //We need the devid to be able to show the correct stream
      $devid = $_GET['devid'];
      $rdata = ReadDataFromSQL($devid);
   }
   if($rdata == null) {
      $rdata = array(
         'errorcode' => 101,
         'error' => 'No current trip'
      );
   }
   if(!isset($_GET['devid']) ) {
      $rdata = array(
         'errorcode' => 100,
         'error' => 'no device ID provided'
      );
   }
   echo json_encode($rdata);

} elseif(!empty($body) && ($atoken == $ctoken)) {

   //The user has both body data and token.. lets initialize the database writing
   $xml = simplexml_load_string($body);

   if ($xml == false) {

      //This XML seems to be invalid lets tell the user just that by sending back a JSON
      echo '{ "id":902, "error":true, "message":"Cant load XML", "valid":true }';

   }
   else
   {
      //The user has done everything right so far. Lets call up the function that starts the good stuff
      WriteDataToSQL($xml);
   }
   
} else {
   //This user got nothing.. Should we just send him to a regular page?
   ?><!DOCTYPE HTML>
<html lang="en-US">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="refresh" content="1;url=http://www.example.com">
        <script type="text/javascript">
            window.location.href = "http://www.example.com"
        </script>
        <title>Page redirection</title>
    </head>
    <body>
        <!-- Note: don't tell people to `click` the link, just tell them that it is a link. -->
        Checkin.example.com is a server side landing page for a mobile app.<br/>
      Your browser/service did not authenticate in the right way and is forwarded automatically to the main page.<br/>
      If this does not happen please click/tap the following link <a href='http://www.example.com'>example.com</a>
    </body>
</html><?php
}

?>

its worth mentioning that you need to replace example.com with your own domain :)

functions.php:
Code: Select all
<?php

/*

   File to be included for checkin and driving the webpage

*/

$hostname = "localhost";
$database = "database";
$ctoken = '32bit hash?';

function ReadDataFromSQL($devid) {
   global $hostname, $database;
   
   //username and password for read database
   $username = 'a read user for the web interface';
   $password = 'a wise password';
   
   //establishing the PDO connection
   $db = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8",$username,$password);
   
   $trip_sel = $db->prepare("SELECT PointLat as Lat,PointLon as Lng FROM tblPointdata WHERE TripdataID = (SELECT MAX(TripdataID) FROM tblPointdata PD JOIN tblTripdata TD ON PD.TripdataID = TD.ID WHERE DevID = :DevID and PointDate > NOW() - INTERVAL 12 HOUR) ORDER BY ID");
   $trip_sel->execute(array(':DevID' => $devid));
   if($trip_sel->rowCount()>0)  {
      $result = array('posdata' => $trip_sel->fetchAll(PDO::FETCH_NUM));
      $lon = 0;
      $lat = 0;
      $maxlat = -360; //max lat (bredde)
      $minlat = 360;
      $maxlon = -360; //max lon (høyde)
      $minlon = 360;
      foreach ($result['posdata'] as $datapoint) {
         $lon += $datapoint[0];
         $lat += $datapoint[1];
         $maxlat = max($maxlat,$datapoint[1]);
         $minlat = min($minlat,$datapoint[1]);
         $maxlon = max($maxlon,$datapoint[0]);
         $minlon = min($minlon,$datapoint[0]);
      }
      $zoomvalue = max($maxlat-$minlat,$maxlon-$minlon);
   
      if($zoomvalue > 0 && $zoomvalue <= 0.125)
         $result['zoom'] = 13;
      elseif($zoomvalue > 0.125 && $zoomvalue <= 0.25)
         $result['zoom'] = 12;
      elseif($zoomvalue > 0.25 && $zoomvalue <= 0.5)
         $result['zoom'] = 11;
      elseif($zoomvalue > 0.5 && $zoomvalue <= 1)
         $result['zoom'] = 10;
      elseif($zoomvalue > 1 && $zoomvalue <= 2)
         $result['zoom'] = 9;
      elseif($zoomvalue > 2 && $zoomvalue <= 4)
         $result['zoom'] = 8;
      elseif($zoomvalue > 5 && $zoomvalue <= 5)
         $result['zoom'] = 7;
      elseif($zoomvalue > 5 && $zoomvalue <= 10)
         $result['zoom'] = 6;
      elseif($zoomvalue > 10 && $zoomvalue <= 15)
         $result['zoom'] = 5;
      elseif ($zoomvalue > 15 && $zoomvalue <= 20)
         $result['zoom'] = 4;
   
      $result['center'] = array(0 => $lon/$trip_sel->rowCount(), 1 => $lat/$trip_sel->rowCount());
      return $result;
   }
   
}

function WriteDataToSQL($xml) {
   global $hostname, $database;
   
   //preparing returning arrays/objects
   $travel = (object)array('id','getTripUrl','description','length','time','tpoints','uplpoints');
   $point = (object)array('id','date','lat','lon','speed','course','haccu','bat','vaccu','altitude','continous','tdist','rdist','ttime');
   
   //establishing the PDO connection
   $db = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8",$xml->username,$xml->password);
   
   //preparing the select, insert and update maindata SQL and the insert pointdata SQL
   $trip_sel = $db->prepare("SELECT ID FROM tblTripdata WHERE DevID=:DevID and TripID=:TripID");
   $trip_ins = $db->prepare("INSERT INTO tblTripdata (DevID, TripID, TripLength, TripName, TripTotalPoints) VALUES (:DevID, :TripID, :TripLength, :TripName, :TripTotalPoints)");
   $trip_upd = $db->prepare("UPDATE tblTripdata SET TripLength = :TripLength, TripName = :TripName, TripTotalPoints = :TripTotalPoints WHERE DevID = :DevID and TripID = :TripID");
   $point_ins = $db->prepare("INSERT INTO tblPointdata (PointAltitude, PointContinuos,PointDate, PointHAccu, PointID, PointLat, PointLon, PointRelativeDistance, PointTotalTime, PointVAccu, TripdataID) SELECT :PointAltitude, :PointContinuos,:PointDate, :PointHAccu, :PointID, :PointLat, :PointLon, :PointRelativeDistance, :PointTotalTime, :PointVAccu, ID FROM tblTripdata WHERE DevID = :DevID and TripID = :TripID");

   // Prepare list of points
   $goodPointsList = "";

   // Start processing each travel
   foreach ($xml->travel as $travel) {

      //Check if there is a new trip and add it if it is. Otherwise update it
      $trip_sel->execute(array(
         ':DevID'   => $xml->devId,
         ':TripID'   => $travel->id,
      ));

      //if existing trip then update else create
      if($trip_sel->rowCount() > 0) {
         $trip_upd->execute(array(
            ':DevID'         => $xml->devId,
            ':TripID'         => $travel->id,
            ':TripName'         => $travel->description,
            ':TripLength'      => $travel->length,
            ':TripTotalPoints'   => $travel->tpoints,
         ));
      } else {
         $trip_ins->execute(array(
            ':DevID'         => $xml->devId,
            ':TripID'         => $travel->id,
            ':TripName'         => $travel->description,
            ':TripLength'      => $travel->length,
            ':TripTotalPoints'   => $travel->tpoints,
         ));
      }
      
      // Prepare the successful points
      $goodPointsList = "";

      // Process each point
      foreach ($travel->point as $point) {
   
         //Inserting point
         $point_ins->execute(array(
            ':DevID'               => $xml->devId,
            ':TripID'               => $travel->id,
            ':PointContinuos'         => $point->continous,
            ':PointDate'            => date("Y-m-d H:i:s", trim($point->date)),
            ':PointID'               => $point->id,
            ':PointTotalTime'         => $point->ttime,
            ':PointAltitude'         => $point->altitude,
            ':PointHAccu'            => $point->haccu,
            ':PointLat'               => $point->lat,
            ':PointLon'               => $point->lon,
            ':PointRelativeDistance'   => $point->rdist,
            ':PointVAccu'            => $point->vaccu,
         ));

         //If everything goes to plan add one to the pointlist otherwise return errorcode
         if($point_ins->errorCode() == "00000") $goodPointsList .= $point->id.","; else  print_r($point_ins->errorInfo());
      }   
   }
   //Return a list of accepted points (if there where any)
   echo '{"id":0, "tripid":'.$travel->id.($goodPointsList != "" ? ',"points":['.substr($goodPointsList, 0, -1).']' : '').',"valid":true}';
}

?>
nyzeta
 
Posts: 1
Joined: Thu May 28, 2015 12:31 pm

Re: Improved btraced PHP server code

Postby Btraced » Sat Jun 13, 2015 11:09 am

looks great!
thanks for posting
User avatar
Btraced
Site Admin
 
Posts: 60
Joined: Sat Dec 10, 2011 3:03 pm


Return to Btraced Webservice Protocol And Sample code etc.

Who is online

Users browsing this forum: No registered users and 1 guest

cron