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:


// get the HTTP method, path and body of the request
$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');

// 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);

// build the SET part of the SQL command
$set = '';
for ($i=0;$i<count($columns);$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) {

// 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

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


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


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:


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!


PHP-CRUD-API now supports PostgreSQL 9

For the pasts months I have been building PHP-CRUD-API (formerly MySQL-CRUD-API). It is a single PHP file that provides an instant powerful and consistent REST API for a MySQL, PostgreSQL or MS SQL Server database. The application uses reflection to “detect” the table structure and then provide an API in only a few hundred lines of PHP code. It is quite comparable to the REST functionality of the experimental HTTP plugin in MySQL 5.7.

Production performance < 10 ms

I recently finished the test suites and started using it in production. I’ve added some simple .htaccess based firewalling to ensure that only trusted applications can talk to it. Most REST API calls are handled well under 10 ms, so the performance impact on the consuming web application is acceptable. It manages to keep page loads under 100 ms even when doing several REST API calls.

PostgreSQL support and more

Recently PostgreSQL was added as a supported database. With the addition of this third database backend I also changed the name from MySQL-CRUD-API to PHP-CRUD-API. Other features that were recently added are support for “CORS pre-flight requests” (mainly for AngularJS) and “JSON batch insert”. Feature requests are very welcome on the php-crud-api Github page.

Contributions / Future

If you feel like contributing, then maybe these topics inspire you:

  1. Set up Travis automated tests
  2. Add an API documentation generator
  3. Create a plugin system for authentication, authorization and accounting
  4. Port to NodeJS, Java or C#

If you like the project, please give it a star on Github!


Static code analysis for PHP templates

Templating is cool. Everybody is using Twig today. Other popular choices are: Smarty, Mustache and Latte. You may also want to read what Fabien Potencier has written about PHP templates languages. It makes sense.

Still I can think of two reasons why we don’t want a templating language and we rather use PHP itself for templating. First reason: PHP templating is easier to learn than a PHP templating language.  Second reason: it executes faster.

PHP templating languages improve security

I tried to understand what the primary reason is that people are using a templating language. It seems to be ease of use, while keeping the application secure. The following example shows how easily you can write unsafe code:

Hello <?php echo $POST['name']; ?>!

It would only be safe to print a POST variable when using:

<?php echo htmlspecialchars($POST['name'],ENT_QUOTES,'UTF-8'); ?>

A templating language typically allows you to write something like:

Hello {{ name }}!

I agree that security is improved by using a templating language. The templating language escapes the output strings in order to prevent XSS vulnerabilities. But still I wonder: Can’t we get the same security benefits when we use native PHP for templating?

Helper function

As you have seen the PHP way of escaping is rather long. Fortunately, you can easily define a function that allows an alternative syntax, for instance:

Hello <?php e($POST['name']); ?>!

Yup, that is the “e” for “echo” :-). Now we can report all native (unescaped) echo function calls as being potentially unsafe. This can be achieved by doing static code analysis. While analyzing the code the analyzer could complain like this:

PHP Warning:  In "template.php" you should not use "echo" on line 1. Error raised  in analyzer.php on line 11

This could be limited to debug mode as static code analysis actually takes some time and may harm the performance of your application.

Static code analysis in PHP

I worked out the idea of secure PHP templating using static code analysis. In development (debug) mode it should warn the programmer when he uses a potentially non-safe construct.

The following analyzer script shows how this works:

$tokens    = array('T_ECHO', 'T_PRINT', 'T_EXIT', 'T_STRING', 'T_EVAL', 'T_OPEN_TAG_WITH_ECHO');
$functions = array('echo', 'print', 'die', 'exit', 'var_dump', 'eval', '<?=');
$filename  = 'template.php';

$all_tokens = token_get_all(file_get_contents($filename));
foreach ($all_tokens as $token) {
  if (is_array($token)) {
    if (in_array(token_name($token[0]),$tokens)) {
      if (in_array($token[1],$functions)) {
        trigger_error('In "'.$filename.'" you should not use "'.htmlentities($token[1]).'" on line '.$token[2].'. Error raised ', E_USER_WARNING);

It will analyze the “template.php” file and report potentially insecure or erroneous language constructs.

This form of templating and static code analysis is fully implemented in the MindaPHP framework that you can find on my Github account. You can find the source code of the PHP static code analyzer class here.


RewriteCond and RewriteRule tricks for .htaccess

The Apache web server has a module called “mod_rewrite”. It allows for redirecting and modifying the requested URL. Below are some of the most popular modifications and redirects that can be executed. Put these commands in a  “.htaccess” file in the document root of the web site. In order of popularity:

#1. Redirect everything the www subdomain

With or without “www”? Not such a hard question considering that you can answer on both and redirect one. This snippet, which can be combined with the previous one, redirects all non-www requests to the www subdomain:

<IfModule mod_rewrite.c>
 RewriteEngine On
 RewriteCond %{HTTP_HOST} !^www\. [NC]
 RewriteRule ^(.*)$ http://www.%{HTTP_HOST}/$1 [R=301,L]

Having your website on the “www” subdomain may be beneficial when dealing with CDN or security services.

#2. Redirect everything to HTTPS

There is hardly any reason not to run your website over SSL (HTTPS). Also it is very important that you answer to both HTTP and HTTPS. This snippet redirects all non-HTTPS traffic to HTTPS:

<IfModule mod_rewrite.c>
 RewriteEngine On
 RewriteCond %{HTTPS} !on [NC]
 RewriteRule ^(.*)$ https://%{HTTP_HOST}/$1 [R=301,L]

It is important to choose one true (canonical) URL for SEO reasons.

#3. PHP file to handle all non-static requests

Also known as the front controller pattern. This mechanism is the basis for any web framework. In PHP it allows you to read the actual requested path in the $_SERVER[‘REQUEST_URI’] global variable. The rewrite looks like this:

<IfModule mod_rewrite.c>
 RewriteEngine On
 RewriteCond %{REQUEST_FILENAME} !-d
 RewriteCond %{REQUEST_FILENAME} !-f
 RewriteRule ^(.*)$ index.php [QSA,L]

Note that the PHP file is bypassed for existing files (static content).

#4. Rewrite GET parameter to URL part

If you have an URL that you should be calling with “GET /orders?id=13” and you want it to respond as if “GET /orders/13” was called, then you may use the following:

<IfModule mod_rewrite.c>
 RewriteEngine On
 RewriteCond %{REQUEST_URI} ^/orders [NC]
 RewriteCond %{QUERY_STRING} ^id=([0-9]+)$ [NC]
 RewriteRule ^(.*)$ %{REQUEST_URI}/%1\? [R,L]

This is especially useful when migrating URL schemes and need legacy support. Note how the escaped question mark at the end of the “RewriteRule” removes the GET parameter(s).



Jeff Atwood: Learning to code is NOT overrated

Jeff Atwood, author of the Coding Horror blog and creator of the Q&A site StackOverflow, wrote an article with the provocative title:

 Learning to code is overrated

He argues that we should not teach our children software development, because there are better, greater skills that we should teach our children. He seems not to realize how much of his own perspective is involved in this opinion.

One of the great achievements of modern computing is that we no longer need to be programmers to create, build and get things done

Oh, and let’s just pretend he did not write that and we did not read that.

Language vs. Mathematics

So the main reasoning in the article is this:

There’s nothing wrong with basic exposure to computer science. But it should not come at the expense of fundamental skills such as reading, writing and mathematics.

Sounds reasonable, but wait, what?! We should not teach our children (computer) science, because they might get behind on reading, writing and math? I would argue the opposite about this fantastic applied science: We should learn children to create software in order to make them want to learn English (reading and writing) and master mathematics.

I know that I was totally unmotivated at primary school for reading, writing and mathematics. On the other hand I was highly motivated to learn whatever was needed to understand IBM-Basic programming language. This challenge showed me why it was important to pay attention at school.

When Jeff Atwood says that reading and writing are more important he may also be referring to “Literature vs. Computer Science” or more generally “Language vs. Mathematics”. I do not see how you could argue that one is more important than the other. I do on the other hand recognize that people tend to lean more towards one than the other.

Programmers can’t communicate

Jeff Atwood seems to imply that the world solely consists of “Mathematical” people, when he writes:

Learning to talk to the computer is the easiest part. […] The people — well . . . you’ll spend the rest of your life figuring that out.

I think this is where Jeff is blinded by his own perspective. Children on a primary school are not guaranteed to think talking to the computer is easy and talking to people is hard. Only the people that do not need encouragement to go into computer science are like that. For most of the people the opposite is true. These people are discouraged to enter the profession, due to the lack of diversity.

… typing in pedantic command words in a programming environment

This is how Jeff Atwood describes programming. I guess somebody convinced him that programming is an inferior activity and not the creative and intellectually challenging craft that it actually is. It makes me wonder who in Jeff Atwood’s life is responsible for destroying the ability to enjoy the magic of creating software that I cherish so much. I promise that I won’t let this happen to me.


Jeff Atwood wrote an article to criticize the mayor de Blasio plan to teach programming to kids. His main argument seems to be that he values language skills over applied mathematics. But he seems to forget that we are not all mathematical minds that can easily learn programming. Learning kids to code may improve the popularity of the profession and hopefully also the diversity.