Control a stack with database access via remote access?

[XPeterXKennethX]

Dear forum community, I would like to find out if it is possible to control from a LiveCode standalone (desktop) a LiveCode stack that is located - together with a database - on a webhost. The webhost stack would receive a SQL statement from my desktop app and perform an arbitrary operation on the database that resides in its location, and pass the output back to the desktop app. I did find an example of how to call a webhost stack from your desktop app and take over its image file, but an example of the remote database control I mentioned earlier via such a webhost stack did not. Is there possibly an example for this or has anyone done something similar?

Thanks a lot for your help!

[Klaus]

Hi Peter,

I don't think this is possible, since a stack is just a "dumb" file without the engine wrapped around it (runtime)
or wihtout being opened by another runtime or the IDE.

You can of course open that stack with your desktop app, but then it will lose its connection to the database on the server
and it will then act like a local file/stack on your machine.

The only way I can think of is to let your desktop app access the database directly on your server.
However I'm not sure if this will work with a SQLite db, in case that is what your are talking about.


Best

Klaus

[XPeterXKennethX]

Hello Klaus,

that was exactly my point. I wanted to control a SQLite database via a stack that is in the same scope as it. This stack is able to execute its own actions when called, which I bind to the "preopenStack" event, for example, and thus possibly also execute an SQL query in a nearby SQLite file.
Whether this is at all useful or practicable is certainly another matter.
But it would be interesting for me to know.

Thank you very much + kind regards,

Peter.

[FourthWorld]

Sounds like LiveCode Server is what you're looking for.
https://lessons.livecode.com/m/4070

[XPeterXKennethX]

Yes, that seems to be the more sensible option. Many thanks!

[AndyP]

Hi Peter, you can add a lc server script next to your sqlite db to act on a post from your descktop app.

Here is a checked working example to get all records from a table.

Desktop code

Code: Select all

on mouseUp pMouseButton
   --construct the sql
   put "YOUR TABLE NAME" into tTable
   put "SELECT * from" && tTable into tSQL
   put "sql=" & URLEncode(tSQL) into tData
   post tData to URL "FULL URL TO YOUR LC SCRIPT"
   
   put UrlDecode(it) --contains the returned data
end mouseUp

And a sample lc server script

Code: Select all

<?lc

--for live, turn off error reporting, helps increase security
--set the errorMode to "quiet"

--for dev
set the errorMode to "inline"

--------------------------------------------------------------------------

--the sql commands posted
put $_POST["sql"] into tSQL

--------------------------------------------------------------------------

--setup db
--assuming your db and lc file are in a sub directory of your root
put $_SERVER["DOCUMENT_ROOT"] into tRoot
put tRoot & "/SUB DIRECTORY/YOUR DB NAME.db" into tDatabaseName

--------------------------------------------------------------------------

put revOpenDatabase("sqlite", tDatabaseName, , , , ) into tDatabaseID
put textDecode (revDataFromQuery(comma,return,tDatabaseID,tSQL), "UTF-8") into tRecords
revCloseDatabase tDatabaseID
put tRecords

-------------------------------

?>

Tis should be enough to get you started

[mwieder]

...but please don't implement it this way, as it leaves your database wide open to SQLInjection attacks.

[AndyP]

...but please don't implement it this way, as it leaves your database wide open to SQLInjection attacks.

Yes agree, this was just an example to get Peter started.

Normally I would wrap the whole of the code in an if - end and send with the sql a security key code that only if correct allows the following code to be actioned. Also I would send an action command to act upon the code.

All of this is encrypted on post from the desktop app, decrypted by the server file, returns are also encrypted by the server file.
The security key code is encrypted differently to the sql.

e.g

Code: Select all

put $_POST["keyCode"] into tkey

--decrypt tKey routine here

--only function if the keyCode is correct
if tKey is "mySecurity String" then

put $_POST["action"] into tAction
put $_POST["sql"] into tSql

--decrypt tAction + sql routine here

select tAction

case "myAction"

--myAction sql code here

break

case "myAction2"

--myAction2 sql code here

break

...

end select

end if

This is good enough for my needs, this is only used for a family shopping list.

[FourthWorld]

...but please don't implement it this way, as it leaves your database wide open to SQLInjection attacks.

Yes agree, this was just an example to get Peter started.

I struggle with these choices myself, whether to provide simple examples or thorough examples.

Many of the examples I post here don't include adequate error checking. It adds more to the code, and to the untrained eye may make a simple concept seem more onerous than it is.

Over time I've gradually been leaning toward accuracy over simplicity, if only because it's a better reflection of how LC is used in real-world contexts.

With security, this choice is both more and less simple.

It's a simpler choice because explaining the "why" behind secure practice is a lot of work, for the reader as well as the writer. Omit it and everything becomes invitingly easy.

But it's also not so simple, as it fills the indexable web with examples unsuitable for actual work, the sort of thing newcomers are most likely to find and use.

With both error checking and security, yeah, they eat up time. But they're also part of the art of programming.

I remember when I was starting out how annoying it was to do all the extra typing to handle errors well. But I've found it doesn't take long before thinking in terms of error handing becomes second nature. Habits will emerge only with practice, and practice will begin only with guidance.

Security is murkier because the scope of threats is beyond what can be known. We have to assume components we depend on will eventually have flaws, and the the bad guys are smarter than us. This can be paralyzingly dismaying, but in the end we have to reach a Zen comfort with at least addressing well-known risks, with the focus being not so much to create the world's first impenetrable system, but just one that would survive review should a question arise about whether we even tried.

It's one thing to be the first site where an exotic zero day was used, but quite another to have a run-of-the-mill exploit we anticipated but just never got around to preventing.

This is good enough for my needs, this is only used for a family shopping list.

Beyond login-password pairs, it's rarely the data they're after. No one cares about my calendar entry for a client meeting.

But the server itself is valuable as a compute resource rentable in a botnet.

Spam, crypto mining, and DDoS are just a few of the things other people's servers are useful for.

One of the biggest DDoSes ever was accomplished by harnessing security cams, since each unit is a tiny computer with an OS and network connection, and with a sloppy firmware update mechanism that was all that's needed to repurpose it:
https://www.techradar.com/news/internet ... er-1329480

Not everyone has time or interest in subscribing to infodec newsletters and reading CVE DBs. But I believe we can raise the bar at least high enough to knock out the script kiddies by encouraging contemporary good practice like sanitizing inputs, staying current with updates, and being mindful of permissions.

It's not the single-user single-machine world many of us started in with HyperCard. Our modern connected world is vastly more interesting, opening up so many more possibilities for using computers to enhance our work and play.

But like they say, with great power comes great responsibility. And like error checking, security awareness is a good habit to establish early on.