LiveCodeServer-database-API

[stam]

Hi all, I thought this may be of help/interest for some.

LiveCodeServer-database-API is a database-agnostic API with industrial security, and can be adapted to any project. Using Server, you can do all database operations over HTTP using RPC (remote procedure call) rather than REST, which seems a more natural fit for LiveCode.
The repo is available at https://github.com/stam66/LiveCodeServer-database-API.

This project was derived from an effort to provide HTTP access to a MySQL database I'm using in a webapp project (not LiveCode), where I wanted to write companion apps (probably in LiveCode) to use the same database, and actually wanting to play with Server I guess.

Server is the middleware on the remote server that bridges between client app and database. I generated a working version of this, but then I got Claude Code to review my repo, and it surprised me with a large number of critical vulnerabilities I hadn't even considered (like preventing hackers from using time to response as a way of predicting chars in the password, MIME-sniffing, IP-based rate limiting, etc).
With Claude's help, I've created a rock-solid API for my project and then got it to help create a generalisable API, that can be used either with MySQL, PostreSQL, SQLite or ODBC - there are templates for all and a generic endpoint template. It also includes a template for data audit that will capture any data changes in detail automatically.

In short, you would create an API directory on your server that has a database configured and LiveCode Server installed, copy the basic files and modify some templates for your specific setup and it should just work (he says...).

Security features include:
Security Headers

• X-Content-Type-Options: Prevents MIME-sniffing
• X-Frame-Options: Prevents clickjacking
• X-XSS-Protection: Enables browser XSS protection
• CORS: Cross-origin resource sharing configured
• CSP: Content Security Policy

SQL Injection Prevention

• validateNumericID() - Validates and sanitizes numeric IDs
• sqlEscape() - Escapes SQL special characters

Authentication

• JWT-based authentication with HMAC-SHA256 (JSON Web Token)
• Constant-time password comparison (timing attack protection)
• Token expiration (default: 30 minutes)
• Salted password hashing with PBKDF2-like approach

Rate Limiting

• IP-based rate limiting (configured per endpoint)
• Automatic cleanup of expired limits
• Proxy/load balancer support (X-Forwarded-For)

Documentation and templates/examples are included in the repo, but I haven't really tested these thoroughly (my specific project works terrifically, but this was not based on the generalisable version, as that was derived from my specific project).

Feedback/comments welcome here and (preferably) on GitHub - just raise an issue at https://github.com/stam66/LiveCodeServer-database-API.

Stam

[coleman]

Thanks for sharing this, looks like a really useful project.

Using LiveCode Server with an RPC-style API feels like a good fit for LiveCode apps, especially when you’re doing frequent database operations and want to keep things simple on the client side. I also like that security was clearly thought about from the start — things like rate limiting, JWT auth, and basic SQL-injection protection are often overlooked in small projects.

The fact that it can be adapted for different databases (MySQL, PostgreSQL, SQLite, ODBC) makes it practical for real-world use, not just demos. Having templates to start from is a big plus too.

It might be helpful for newcomers if there was a small client-side example showing how to authenticate and run a basic query from a LiveCode stack — that would probably make adoption even easier.

Also, slightly off topic, but when people do an oculus proxies review or talk about tooling and infrastructure choices, it’s nice to see projects like this that focus on security and clean design from the beginning.

Overall, nice work — thanks for putting it out there and documenting it

[markosborne]

Thank you for sharing this Stam,

Looks very useful. Very generous of you.

It's encouraging to hear that you are considering using LiveCode for the companion apps. Out of interest, what are you using to develop the web app?

Best wishes

Mark

[stam]

Out of interest, what are you using to develop the web app?

Hi Mark, I’m using Xojo for the web app. I’m not particularly enamoured with the language but it’s usable and where I get stuck Claude AI helps. And the results with Web are much nicer than what I could achieve out of the box with LC.

I did like using LC Server as the middleware and ideally I would have liked to have seen the mothership putting work into making it actually usable with APIs like this one.

The bolstered security with this is industrial level, which came out of me asking Claude AI to strictly scrutinise and criticise my API and it found so many vulnerabilities, many of which I hadn’t considered at all! The essence of this API is 4 .lc files that basically manage secure access, and then add a template file for each endpoint

As for the companion apps: I decided to practice more with Xojo, so am writing a desktop app in that.

What puts me off using LC, which would have been much easier to develop in using Classic, is the fact that apps will cease working if my sub lapses, and by how things are going it’s not a given that I will keep paying the sub. Having said that XOJO recently pumped up their prices significantly as well, so not sure I’ll stay with that either.

At this point considering Flutter or Python as alternatives but remain undecided.

[keliko]

I like LiveCode, but I have to stop using it because of this FAQ statement:

What happens to my app if my license lapses?
Apps require active licensing to run. If your license lapses, your apps will no longer be licensed and they will not continue to run.

I only develop desktop and mobile applications. Choosing Xojo is an interesting option.

[Imoro]

Impressive work! I like that the API handles security from the start — makes it way easier to build reliable LiveCode apps without worrying about common pitfalls

[stam]

API updated to address a number of bugs, including PBKDF2 implementation , rate-limiting function for incorrect logins and error handling in CRUD operations

• PBKDF2 now fully implemented with 10,000 bitwise operations, as LiveCode does not implement PBKDF2 natively and this needed to be created manually for best possible security. HMAC/PBKDF2 runs about 1.6 additional million bitXor operations above and beyond the millions that MessageDigest does with SHA256.
  The initial implementation was very slow, so I had reduced the iterations to 1000 interactions but this was of course causing a mismatch with systems with natively implemented PBKDF2 (namely apps written in other environments).
  Speed of operation has significantly increased from about 14-15 seconds (unusable as a login system) to about 4 seconds (usable and intentional, to stop hackers from guessing chars in the user's password by the time it takes to reject a password - apparently that's a thing),
  PBKDF2 has been implemented and salted hashes match those of other systems generating a salted hash this way.
• Error handling improved - the API was reporting failure on some successful CRUD operations, now fixed.
• Rate limiting function - prevent excessive incorrect logins from teh same IP addressed is now corrected and works normally (it was previously counting correct logins as well, so the first incorrect login would trigger a timeout, user not permitted to log in, now fixed.

NEW: Password reset functionality via one-time password. The OTP is emailed to the user using MailJet (free tier: 200 emails/day). Database of users needs to store a valid email for this work and needs a flag to denote whether the stored password_hash is an OTP or not so user can be presented with a "create new password" dialog. The MailJet API key is stored in /lib/settings.lc.


The repo is at https://github.com/stam66/LiveCodeServer-database-API
The big green "Code" button gives a link to download as zip archive or clone the project if you're handy with with GitHub

Structure of the API: 4 core files and and a file for each endpoint to implement.
All files reside in the the API directory of the webserver, 3 of these within a subdirectory of this called "lib". A template is provided as a starting point for each endpoint (usually a table in a database). Where the suffix is .example, this is template to make copies of and remove the .example suffice so it's an .lc file. See the repository on GitHub on how to use.
Files included

• lib/db-functions.lc: DB helpers, JSON, password hashing (PBKDF2), rate limiting, JWT. Dependencies: lib/photon-library.lc, lib/settings.lc.
• lib/photon-library.lc Open source MIT licensed JSON library in pure LC script, provided by FerrusLogic.
• lib/settings.lc.example DB config template - make a copy and input your sensitive credentials in this file, removing the ".example" part of the name. If using GitHub ensure the .gitignore file rejects settings.lc, so your sensitive credentials don't get uploaded to GitHub (you can view my .gitignore on GitHub).
• auth.lc Login/refresh token endpoints lib/db-functions.lc
• PLACEHOLDER.lc.example Template for new endpoints. Make a copy for each endpoint (usually a table in a database), rename it to the table's name and remove the .example suffix. Dependenciess: lib/db-functions.lc

If you want to implement these but but not sure how to, put all these files into an AI such as Claude (probably the best AI for coding) and follow instructions in how to implement. It's really quite simple and probably any AI would help implementation.

[markosborne]

Thank you Stam

(And for your reply to my question)

Best wishes

Mark