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.

Google PageRank still matters for SEO


On Monday, September 21st we moved this blog to another domain. This has made the traffic on the blog go up from 2000 to 2800 unique visitors per day. You can see the effect in the graph above (source: count-per-day plugin).

Google PageRank


At LeaseWeb we run several websites with different Google PageRank scores as you can see in the picture above (source: In order to let the sites benefit from the highest valued domain we could move the sites from subdomains to paths. We did that for this site (LeaseWeb labs). =>

This higher Google PageRank led to an expected increase of Google search traffic. It went from 1250 to 2000 (+60%) clicks per day. It is incredible what better Google PageRank can do for the traffic of a site.

Moving domains? 301 everything!

Moving content from one domain to another (or from http to https) is not without risk. If you do not proper 301 redirect your content, you may lose traffic/rank due to penalties for having 302 redirects or no redirects at all.

I suggest that you create a second property on the Google’s Search Console so that you can see the search traffic move from one site to the other. You can also tell Google in the search console that the site has moved.


On the above graph (from Google’s Search Console) I have drawn a red line to show the 6 day transition from the old to the new domain.