Your Own Secure php TinyWebDB

CRITERIA

I set myself a challenge a short while before the festive season, 2019, to come up with a key:value pair online storage system with the following requirements:

  • Provides for create, update, delete and read (CRUD) of text based data

  • Can be used in AI2 with existing blocks/components and no extensions

  • Usable with other platforms, e.g. html/javascript, google apps script

  • Allows the end user to create their own database, not one shared with other users

  • Can store values, lists and list of lists(tables)

  • Database contents are password protected, with password not stored anywhere online

  • Database contents encrypted, of no use to anyone who might access the file

  • Easy for a developer to deploy to their php server, and to connect with AI2

What I have come up with hopefully satisfies all the above criteria, and is demonstrated in the following files, blocks and aia projects. I provide two aia projects, the first a more fully fledged login and usage app, the second a more barebones approach.


REQUIREMENTS

  • The developer will need a php server online, (can be run on a localhost server for testing)

  • Sufficient space on the server for the database files (encrypted plain text)

  • Create a directory, named to suit ( you can create a subdomain to this directory if you can...for a better url)

  • Using your ftp program, upload a set of php files to a chosen directory on the server which are accessible via a web call. There is an index.php file provided for testing, this will return "TimAI2" in a browser, confirming the url and php is working. The php files should have file permissions 705, the database file (once created) should be 604 or 600. Make sure the directory is writeable to allow the creation of database files.

  • Note the url to the directory (and add a / at the end)

  • A four digit alpha-numeric key code or PIN - for example "12ab" or "G4T5" or "005Z" - DO NOT forget this PIN code.

  • Create or open a project in App Inventor to access the Secure TinyWebDB

  • For both demo projects you will need to add your server url to the TWDBURL/DatabaseURL variable

  • (You do not need to use a tinywebdb component from the Storage palette. All the work is done with a web component)

FIRST RUN (running in Companion)

  • The user must create a database first. In the first demo, the user is asked to register, then login. During this process the database is created. In the second demo, the user needs to enter a pin code to the designated block at the top of the blocks in the blocks editor. Only when this is done the user can run the companion and click on the Create DB button.

  • The database name (which is a unique name generated by the server) is returned and stored to a variable

  • Now the other database functions can be used

USAGE

  • The user can now store tag:value pairs, get values, get all the tags, delete a tag, get all the data, or clear/empty the database

  • On each call to the database, the database file it is decrypted to plain text, edited or queried, then re-encrypted. Without the pin supplied by the user/app, the data remains encrypted, and the file on disk on the server is always encrypted.

  • In the first demo, user, PIN, and database are stored to a tinydb

  • In the barebones project, the database name is not persistent, so a new database will need to be created on each run, or add blocks to preserve the db name

  • You can. if you wish, delete any left over database files on the server through your ftp program.

  • Make sure you have the "Lists as Json" box ticked

  • Of course, in your own projects you can add AI2 lists as a value

PARAMETERS

There are only four:

  • tag

  • value

  • pin

  • userdb

Perhaps the best way to understand what is going on is to watch a video of the first aia project in action:

securetwdb.mp4

EXPANSION

  • You can save binary files to the Secure TinyWebDB if you first convert then to text using base64. Remember that base64 encoded strings are generally larger in size than the original file. I would use Juan Antonio's extension for this.

  • I thought about having a When Data Changed event, but given that the Get All Data feature is there it didn't seem necessary, as something similar could be coded with blocks