Give users completely safe sandboxed raw SQL reporting abilities

in-memory-sqlite-database.php

<?php

// Create in-memory SQLite database
$sqlite = new PDO('sqlite::memory:');

// Generate schema (for whatever tables you want)
$sqlite->prepare('CREATE TABLE users(id, family_id, first_name, last_name, email)')->execute();
$sqlite->prepare('CREATE TABLE families(id, name, phone, address)')->execute();

// Insert pre-defined data
foreach ($users as $user) {
    $sqlite->prepare('INSERT INTO users VALUES (:id, :family_id, :first_name, :last_name, :email)')->execute($user);
}

// Run custom database query
if (($query = $sqlite->prepare($request->query)) and $query->execute()) {
    // Handle results on success
    $results = $query->fetchAll(PDO::FETCH_ASSOC);
} else {
    // Get error on failure
    $error = $sqlite->errorInfo()[2];
}

A few considerations:

• Users could still DOS your server in a very nasty way, would not be hard given this is in memory.
• SQLlight does not guaranty a sandbox, it is possible users will find a way to read from your filesystem/write to it.

To do this I would suggest setting up an ephemeral VM image/docker container that you can fire up quickly and use through an API that has no local data of importance just accept all incoming requests from a known host (your main server).

This will let you limit resources that each user can use up (memory and CPU) and provider a proper secure sandbox.