PHP-CRUD-API now supports authorization and validation

Another milestone is reached for the PHP-CRUD-API project. A project that aims to provide a high performance, consistent data API over REST that is easy to deploy (it is a single PHP file!) and requires minimal configuration. By popular demand we have added four important new features:

  1. Tables and the actions on them can be restricted with custom rules.
  2. Access to specific columns can be restricted using your own algorithm.
  3. You can specify “sanitizers” to, for example, strip HTML tags from input.
  4. You can specify “validators” functions to show errors on invalid input.

These features are built by allowing you to define callback functions in your configuration. These functions can then contain your application specific logic. How these function work and how you can load them is explained below.

Table authorizer

The following function can be used to authorize access to specific tables:

/**
 * @param action    'create','read','update','delete','list'
 * @param database  name of your database (e.g. 'northwind')
 * @param table     name of the table (e.g. 'customers')
 * @returns bool    indicates that access is granted  
 **/
  
$f1=function($action,$database,$table){
  return true; 
};

Column authorizer

The following function can be used to authorize access to specific columns:

/**
 * @param action    'create','read','update','delete','list'
 * @param database  name of your database (e.g. 'northwind')
 * @param table     name of the table (e.g. 'customers')
 * @param column    name of the column (e.g. 'password')
 * @returns bool    indicates that access is granted  
 **/
  
$f2=function($action,$database,$table,$column){
  return true; 
};

Input sanitizer

The following function can be used to sanitize input for specific columns:

/**
 * @param action    'create','read','update','delete','list'
 * @param database  name of your database (e.g. 'northwind')
 * @param table     name of the table (e.g. 'customers')
 * @param column    name of the column (e.g. 'username')
 * @param type      type of the column (depends on engine)
 * @param value     input from the user (e.g. 'johndoe88')
 * @returns string  sanitized value
 **/
  
$f3=function($action,$database,$table,$column,$type,$value){
  return $value; 
};

Input validator

The following function can be used to validate input for specific columns:

/**
 * @param action    'create','read','update','delete','list'
 * @param database  name of your database (e.g. 'northwind')
 * @param table     name of the table (e.g. 'customers')
 * @param column    name of the column (e.g. 'username')
 * @param type      type of the column (depends on engine)
 * @param value     input from the user (e.g. 'johndoe88')
 * @param context   all input fields in this action
 * @returns string  validation error (if any) or null
 **/
  
$f4=function($action,$database,$table,$column,$type,$value,$context){
  return null;
};

Configuration

This is an example configuration that requires the above snippets to be defined.

$api = new MySQL_CRUD_API(array(
  'hostname'=>'localhost',
  'username'=>'xxx',
  'password'=>'xxx',
  'database'=>'xxx',
  'charset'=>'utf8',
  'table_authorizer'=>$f1,
  'column_authorizer'=>$f2,
  'input_sanitizer'=>$f3,
  'input_validator'=>$f4
));
$api->executeCommand();

You can find the project on Github.

Creating a simple REST API in PHP

I’m the author of php-crud-api and I want to share the core of the application with you. It includes routing a JSON REST request, converting it into SQL, executing it and giving a meaningful response. I tried to write the application as short as possible and came up with these 65 lines of code:

<?php

// 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('localhost', 'user', 'pass', 'dbname');
mysqli_set_charset($link,'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request)+0;

// 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?" WHERE id=$key":''); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete `$table` where id=$key"; 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) echo '[';
  for ($i=0;$i<mysqli_num_rows($result);$i++) {
    echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
  }
  if (!$key) echo ']';
} elseif ($method == 'POST') {
  echo mysqli_insert_id($link);
} else {
  echo mysqli_affected_rows($link);
}

// close mysql connection
mysqli_close($link);

This code is written to show you how simple it is to make a fully operational REST API in PHP.

Running

Save this file as “api.php” in your (Apache) document root and call it using:

http://localhost/api.php/{$table}/{$id}

Or you can use the PHP built-in webserver from the command line using:

$ php -S localhost:8888 api.php

The URL when ran in from the command line is:

http://localhost:8888/api.php/{$table}/{$id}

NB: Don’t forget to adjust the ‘mysqli_connect’ parameters in the above script!

REST API in a single PHP file

Although the above code is not perfect it actually does do 3 important things:

  1. Support HTTP verbs GET, POST, UPDATE and DELETE
  2. Escape all data properly to avoid SQL injection
  3. Handle null values correctly

One could thus say that the REST API is fully functional. You may run into missing features of the code, such as:

  1. No related data (automatic joins) supported
  2. No condensed JSON output supported
  3. No support for PostgreSQL or SQL Server
  4. No POST parameter support
  5. No JSONP/CORS cross domain support
  6. No base64 binary column support
  7. No permission system
  8. No search/filter support
  9. No pagination or sorting supported
  10. No column selection supported

Don’t worry, all these features are available in php-crud-api, which you can get from Github. On the other hand, now that you have the essence of the application, you may also write your own!

Data-driven API design

We live in a world of APIs. LeaseWeb is an infrastructure provider that has taken on an API-first approach. This means that we first build an API for our products. Only after the API is finished we build the UI for the product and we do this on top of our own API. This “eat your own dog food” principle ensures a high quality API in which bugs are found before the customer encounters them.

Our “bare metal” (formerly: dedicated server) team has an API for switch port management. Opening a switch port may (dependent on the switch brand) take up several seconds. This is because (for instance) a SSH connection has to be made to the switch, then an XML command and answer has to be sent back and forth. Also, some switches may wait a few seconds to respond with a status, as they are trying to identify the link status, after the port is opened.

But what if a customer requests a “faster” API? We can’t make opening or closing a switch port go faster. What to do? If any API call would only modify a single record in the database, then the execution time would be predictable (and always low). To achieve this there are two API designs possible: “asynchronous” and “data-driven”. In this post we will explore both and show you why a data-driven API is a better choice for consistent APIs.

Synchronous API design

This is the naive implementation. It allows you to Open/Enable the switch port attached to the server with a synchronous call:

POST /bareMetals/{bareMetalId}/switchPort/open

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPort/open HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"switchPort": {
		"status": "opened",
		"switchNode": 1,
		"serverId": 1234,
		"serverName": "My Server"
	}
}

Note that this call may take several seconds to complete and does not follow the RESTful resource naming scheme.

Asynchronous API design

If the call is asynchronous then the request is the same:

POST /bareMetals/{bareMetalId}/switchPort/open

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPort/open HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"jobId": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"createdAt": "2015-01-14T15:09:10+01:00"
}

The response is a job identifier that then has to be polled for status, like this:

GET /bareMetals/{bareMetalId}/jobs/{jobId}

Parameters:

bareMetalId (integer) - The id of the bare metal server
jobId (string) - The id of a job

Example request:

GET /v1/bareMetals/1234/jobs/f5825d99-62ed-433b-930b-1b9561fe4a6e HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 3,
	"statusDescription": "Failed",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:12+01:00"
}

All reponses can be sent really fast as they are simple database lookups. It is important to realize that you need some sort of job manager in the back that does actually execute the jobs and updates the job status accordingly. Note that the commands do not follow the RESTful resource naming scheme.

Data-driven API design

When following data-driven API design you are simply modeling the database tables to facilitate the functionality. It allows you to have fast replies and also follows the RESTful resource naming scheme. You may get the status of the switch port attached to the server with a call like this:

GET /bareMetals/{bareMetalId}/switchPorts

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

GET /v1/bareMetals/1234/switchPorts HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"switchPorts": [
		{
			"status": "opened",
			"switchNode": 1,
			"serverId": 1234,
			"serverName": "My Server"
		}
	]
}

When you are opening the switch port you are actually inserting a record in the switchPortOpenRequests table with the standard POST as prescribed by the RESTful resource naming scheme, like this:

POST /bareMetals/{bareMetalId}/switchPortOpenRequests

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPortOpenRequests HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 1,
	"statusDescription": "Pending",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:10+01:00"
}

Just like with the asynchronous model you can poll for status. Note that there is no job identifier, just a normal identifier, as the request is a normal resource that follows the RESTful resource naming scheme. You do still need to have a background process that watches the switchPortOpenRequests table to execute these requests when they are in “pending” state. This background process is responsible for updating both the switchPortOpenRequests and the switchPorts table with the correct status information.

GET /bareMetals/{bareMetalId}/switchPortOpenRequests/{id}

Parameters:

bareMetalId (integer) - The id of the bare metal server
id (string) - The id of the request

Example request:

GET /v1/bareMetals/1234/switchPortOpenRequests/f5825d99-62ed-433b-930b-1b9561fe4a6e HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 3,
	"statusDescription": "Failed",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:12+01:00"
}

As you can see this last way of modeling does not only reduce every API call to be a database lookup, it also allows you to follow the (predictable) RESTful resource naming scheme.

Generating a data-driven API

The advantage of following an API naming scheme is that the API becomes predictable and can be generated. The PHP-CRUD-API project facilitates creating a full-featured API by uploading a single PHP file. This API will follow the resource naming scheme for your MySQL, PostgreSQL or MS SQL Server tables. With a data-driven API design this should not limit you to create any API functionality. Try it!

PHP-CRUD-API now supports SQL Server 2012

Although the project was initially aimed at only providing support for MySQL, now MS SQL Server 2012 is also supported (and even PostgreSQL). PHP-CRUD-API is a single PHP file that will provide a full REST API for your data structure. With the now added MsSQL_CRUD_API class you can also connect to a SQL Server database. This only works if the SQLSRV driver is installed in PHP and this is only available for Windows.

sql_server

The SQL Server code relies on the “OFFSET” command, which was added to SQL Server in version 2012. It also allows for UTF-8 character encoding, IMHO a character set any modern database should use. The offset command was added by popular demand (to SQL Server), because the pagination in SQL Server was quite cumbersome, especially when compared to MySQL. Now they are on par again. Also the choice of the SQLSRV driver over the more compatible, but inferior, FreeTDS driver was intentional.

SELECT * FROM [posts] ORDER BY [published] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

The response on this project has been mostly positive. There are people arguing that it is bad to expose the data structure of your database. My counter argument is that if you do proper database administration (data management) this is not true. And in that case you do not need all that boilerplate code that APIs generally consist of as it can all be automated. That is this project’s philosophy.

https://github.com/mevdschee/php-crud-api

Check out the code on Github and tell me what you think. Use the comments for feedback.

MindaPHP now has RESTful API support

When building applications today you need to follow cool new architectures like “Microservice design” and “API-first”. APIs play an increasingly important role in applications today. Together with database abstraction layers they bring the database technology further from the business logic than ever.

In line with the “Ease of learning” vision for MindaPHP, I decided to add a minimal RESTful API client in the form of a cURL wrapper. It has full integration with the debugger as you can see below:

mindaphp_api

The wrapper class hardly influences the performance of cURL when the debugger is disabled. When the debugger is enabled the performance and the memory usage may be affected, but this gives you a great deal of control as you can see in the image above. In the example below you see how you can use the cURL wrapper to call the Bing search engine and extract the first 10 links for a search query.

<?php
$query = isset($_POST['q'])?$_POST['q']:'';
$results = array();

if ($query) {
    if (Curl::call('GET','http://www.bing.com/search',array('q'=>$query),$result)==200) {
        
        $dom = new DOMDocument();
        @$dom->loadHTML($result);
        
        $xpath = new DOMXpath($dom);
        $elements = $xpath->query('//ol["b_results"]/li[@class="b_algo"]//h2/a');

        foreach ($elements as $element) {
            $text = $element->nodeValue;
            $link = $element->getAttribute("href");
            $results[] = compact('text','link');
        }
    }
}

For more information check out Github or the demo on one of the links below:

 

Code: https://github.com/mevdschee/MindaPHP
Demo: http://maurits.server.nlware.com/hello/bing