question about clients accessing MySQL database

[twseid58]

I'm not a trained programmer or database administrator, but I've created a stand-alone application that uses a MySQL database for doing student and program assessment at our school. We have a very knowledgeable consultant that supports our servers and works closely with me. I realized that the permissions he set for the MySQL database would not allow any other computer to access the database than my workstation. So I told him I needed for instructors, not only on campus but adjuncts away from campus, to be able to run the application, get information from the database, and insert data from their student evaluation. His reply to my request is:

"We can definitely open it up but this is not a very secure way of running things. Typically in a client server application database is not directly reachable from clients (rather there is a server based middleware that is used to enhance security)."

I searched the forum for "middleware" and couldn't find much information about how others deal with this. Surely this is something developers work with if they create and deploy a stand-alone LiveCode application that makes use of a MySQL database. I seem to have missed something along the way, probably because I originally planned to make this a web application like my previous development of LiveCode server scripts accessing a MySQL database. I didn't realize there would be a difference.

So how do others do this securely? I am having instructors log in with a password that makes use of SHA1 encryption.

-- Tim
(I used HyperCard in the late 80s and love to have it back as LiveCode.)

[bangkok]

The idea of "middleware" is right.

The usual scheme :

-the database "speaks" only to the local server (localhost) : impossible to connect to it from the outside

-a webapp (written in PHP or with LiveCode Server) is designed to receive queries coming from the outside (internet), secure those queries (avoid SQL injection attacks), pass those queries to the DB server, then fetch the results, and display those results to the outside

[twseid58]

I followed a LiveCode tutorial "Connecting to a MySQL database." The example in the tutorial isn't a webapp. If this is only designed for the creator to use locally rather than deploy to other users, then it's not very useful. I don't find any tutorials on implementing such a scheme for an application. Some people reference creating some PHP "middleware" code, but I don't find examples. I didn't realize there was such a difference creating a webapp in LiveCode and creating a stand-alone application. Don't people create apps that access MySQL databases?

[FourthWorld]

Don't people create apps that access MySQL databases?

They do, but for security reasons they don't communicate from the client with MySQL directly, instead using PHP or LiveCode Server (or Perl or Ruby or all sorts of things) to provide restricted APIs for working with the data store, sanitize inputs, etc.

Most folks use PHP for this, because it's been around longer and there's tons of code available.

With LiveCode Server, I don't know about others but in my own use I tend to make very app-specific APIs, so my code would be bloated and irrelevant for your app.

If you choose to use LiveCode Server as your middleware, once you have your code to use the database and you've gained an understanding of how to use LiveCode Server in general, at that point moving your DB code on the server is fairly trivial.

The key of course is gaining an understanding of working with LiveCode Server (or whatever server-side scripting language you prefer). There are numerous tutorials for getting started with LiveCode Server - the Server Guide at livecode.com is a good place to start:
http://livecode.com/developers/guides/server/