php 串接數據之考量事宜

動機：使用 php程式 設計數據串接時應考量那些事情呢?!

準備環境：
1.Client: Windows 10
2.Server: Red Hat Linux

實作步驟：( 本程式碼僅提供 Server 端)
1.在 MySQL 中開設一個專用帳號權限(account/password in database)

2.考慮運用 RESTful api 方式來撰寫較簡單、易讀、方便維護(檔案存取權限 r--r--r--)...如下
// get the HTTP method, path and body of the request
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'], '/'));
$input = json_decode(file_get_contents('php://input'), true);

// connect to the mysql database
$link = mysqli_connect('127.0.0.1', 'account', 'password', 'database');
mysqli_set_charset($link, 'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i', '', array_shift($request));  // table name
if ($table != 'tablename') return null;
// $key_id = array_shift($request) + 0;
$key_id = preg_replace('/[^A-Z0-9_]+/i', '', array_shift($request));  // PID
if(isset($_GET["after_date"]))
  $after_date = $_GET["after_date"];  // after one date

// escape the columns and values from the input object
$columns = preg_replace('/[^a-z0-9_]+/i', '', array_keys($input));
$values = array_map(function ($value) use ($link) {
  if ($value === null) return null;
  return mysqli_real_escape_string($link, (string)$value);
}, array_values($input));

// build the SET part of the SQL command
$set = '';
for ($i = 0; $i < count($columns); $i++) {
  $set.= ($i > 0 ? ',' : '').'`'.$columns[$i].'`=';
  $set.= ($values[$i] === null ? 'NULL' : '"'.$values[$i].'"');
}

// create SQL based on HTTP method
switch ($method) {

  case 'GET':
    $sql = "SELECT * FROM `$table`".($key_id ? " WHERE PID = '$key_id' AND Check_Date >= '$after_date' ORDER BY `Check_Date` DESC" : ""); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key_id"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete `$table` where id=$key_id"; break;
}

// excecute SQL statement
$result = mysqli_query($link, $sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error());
}

// print results, insert id or affected row count
if ($method == 'GET') {
  if (!$key_id) echo '[';
  for ($i = 0 ; $i < mysqli_num_rows($result); $i++) {
    echo ($i > 0 ? ',' : '').json_encode(mysqli_fetch_object($result));
  }
  if (!$key_id) echo ']';
} elseif ($method == 'POST') {
  echo mysqli_insert_id($link);
} else {
  echo mysqli_affected_rows($link);
}

// close mysql connection
mysqli_close($link);

3.考慮利用 Header 中加入 access token 來限制 request 的對象(採用OAuth2也不賴)...如下
function getHeader_var($name) {
  if (function_exists('apache_request_headers')) {
    return isset(apache_request_headers()[$name]) ? apache_request_headers()[$name] : '';
  }

  $name = 'HTTP_' . strtoupper(strreplace('-', '_', $name));
  return isset($_SERVER[$name]) ? $_SERVER[$name] : '';
}
// echo getHeader_var('X-AUTH-TOKEN');
if (getHeader_var('X-AUTH-TOKEN') != '999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999')

  die('token error!!');

4.測試方式，如下
curl -H 'X-AUTH-TOKEN: 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999' https://www.demo.com.tw/cgi-bin/crudapi.php/tablename/G123456789?after_date=20181229

心得：其實，上述程式少考量了許多的層面，例如: 留存log於MySQL's table、限制連接IP或時段...等等!!

參攷：
1.Creating a simple REST API in PHP, https://www.leaseweb.com/labs/2015/10/creating-a-simple-rest-api-in-php/
2.mevdschee/php-crud-api, https://github.com/mevdschee/php-crud-api

留言

張貼留言