SQL Query Executing Twice in PHP

---

I finally fixed a crazy bug on one of my sites where PHP + SQLite was causing some queries to run twice.

I run a lot of web sites, and I like PHP because it's easy to deploy and easy to maintain. More importantly it's easy to manage a large number of sites if they are all similarly setup. I run a lot of small side projects. These projects are the sort of things that I want to build in a weekend and then not really think about again.

Many of my PHP sites use the SQLite database. SQLite is great because it requires no setup or management. It's built into PHP, and you use it by just, well using it. There's no server or separate program to manage.

Most of my PHP/SQLite sites have an administrator section. In this section, I allow a form where I can type in and execute SQL statements directly. This is much more convenient than logging in with SSH and using the command line tools.

However, a while back I noticed a really weird bug. When I would execute an SQL statement through this interface, it would act as though it was running the statement twice!

For example, I would execute the following:

INSERT INTO example (name, url) VALUES ("bob", "example.com");

Instead of inserting one row, I would see it insert two! I spent a lot of time debugging and trying to track this down.

My PHP code looks something like this:

    $database = new SQLite3('my_database');

    $statement = $database->prepare($_POST['query']);
    $result = $statement->execute();

    print("<table id='results'>");
    while($row = $result->fetchArray(SQLITE3_ASSOC)) {
        //Print out row of results
    }
    print("</table>");

So my first thought was that perhaps the browser was sending the POST request twice. I put in some logging statements with the PHP error_log() function and proved that the code was only running one time. Here's where I was really thrown for a loop. I'm only calling $statement->execute() once. How can it be running the query twice?

The answer is that $statement->execute() runs the query once, and $result->fetchArray() runs it a second time! I have no idea why it works this way, but that is what happens.

The solution is pretty simple. If you're doing something that changes the database (e.g. INSERT), just make sure not to call fetchArray(). Calling it is pointless anyway, since SQLite doesn't support the RETURNING syntax.

You can do this in a flexible way by looking at numColumns() to first see if there are any results. If there are results, it is safe to call fetchArray(), and if there aren't results, you'd really better not call it.

That makes the above code into something more like the following:

    $database = new SQLite3('my_database');

    $statement = $database->prepare($_POST['query']);
    $result = $statement->execute();

    if ($result->numColumns()) {
        print("<table id='results'>");
        while($row = $result->fetchArray(SQLITE3_ASSOC)) {
            //Print out row of results
        }
        print("</table>");
    }

With the numColumns() check, we can be sure that fetchArray() is going to be called except on read-only queries.

For further reading, there is a report on the php bug tracker here. Apparently this issue has been around since 2013.

---

---