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