Hi Mark, Now you've got me worried! I had the impression that since the php scripts run on my server and access the mySQL database on the same server, there wouldn't be any sql injection issues, particularly since I never send any SQL statements from my client app to the server.
I'm just as lazy as the next developer but if I'm setting myself up for securoty issues, I guess I could reluctantly bring myself to fix the loopholes :-) On Wed, Apr 6, 2016 at 12:57 PM Peter Haworth <p...@lcsql.com> wrote: > This is where it gets interesting. My original plan was to directly > connect to mySQL from Livecode using the variableslist parameter to the > various Livecode db functions, which should protect against sql injection. > To be honest, I don't recall why I went away from that plan in favor of > middleware. > > Most web hosts will have a cPanel option to set up a mySQL database, > usually using phpMyAdmin and it's pretty straightforward if you are already > familiar with db structures. > > The only wrinkle I remember is that you have to specify for each db user > which hosts they can connect from in terms of an ip address or domains. > > In fact, I think that's what decided me on using middleware since then all > the db access is from your web hosts domain, thus one entry in the > allowable hosts table. In my case, my users were scattered across various > domains and all had dynamic ip addresses so it would have a been a pain to > keep all that up to date. > > I got a great start on the middleware scripts by downloading Bill's > library (thanks Bill). > > Pete > > > On Wed, Apr 6, 2016 at 10:35 AM Ray <r...@linkit.com> wrote: > >> Yeah, my expectation is that we'll avoid injection issues using the PHP >> middleware. >> >> All in all, it seems like a pretty common need; to store on a server a >> database that multiple users are going to be updating. I wish there >> were some sort of a Livecode lesson which detailed several approaches to >> this including the necessary steps to set up the database on the remote >> server. Do you know of any? >> >> On 4/6/2016 12:42 PM, Peter Haworth wrote: >> > Hi Ray, >> > Lots of things for you to think about! As someone else mentioned, I >> > thought you were using a network file system rather than a web server. >> > >> > Just to set the record straight on a couple of things. >> > >> > mySQL does have transactions, as do all SQL implementations. They're >> part >> > of the SQL spec. postGresql may well have advantages over mySQL but >> that >> > isn't one of them. >> > >> > It's quite feasible to implement multi-user sqlite applications. There >> are >> > several examples on the SQLite web site and in fact their website is >> driven >> > by an sqlite database. Of course it depends on the needs of the >> > application, as it always does. >> > >> > There are many ways to handle queuing of db access when the db is >> locked. >> > There's a PRAGMA that controls how long the lock request should wait >> before >> > reporting it can't get the lock. It's easy to program that yourself in >> a >> > repeat loop/send in time structure too. >> > >> > I started a thread a few months back about direct calls to server-based >> SQL >> > databases from Livecode versus middleware which generated a lot of >> possible >> > approaches. I ended up going the middleware route using php scripts. >> My >> > Livecode app uses the POST command to send a code that indicates to the >> > server which SQL operation is to be executed along with all the data >> needed >> > to execute it. No SQL statements ever go over the connection so no sql >> > injection issues. That's just one possible implementation of course and >> > there are many other ways to do it. >> > >> > Good luck! >> > >> > On Wed, Apr 6, 2016 at 8:32 AM Ray <r...@linkit.com> wrote: >> > >> >> Richard - thanks for this advice. It's really quite helpful. We've >> >> abandoned the sqLite idea but I think mySQL should work fine. The >> >> purpose of this database is to maintain an index of bug reports. The >> bug >> >> reports themselves are actually Livecode stacks. The database will >> >> serve as an index to all bug reports. The plan is to have just single >> >> table of about four columns; username, bug name, date, and status. >> >> Hopefully it will stay this simple. >> >> >> >> Since we'll be updating an entire record at a time I don't think the >> >> lack of dependency will ever be a problem, but let me know what you >> think. >> >> >> >> Thanks, >> >> >> >> Ray >> >> >> >> On 4/6/2016 11:14 AM, Dr. Hawkins wrote: >> >>> On Wed, Apr 6, 2016 at 6:05 AM, Ray <r...@linkit.com> wrote: >> >>> >> >>>> I thought of downloading it, updating it, and then putting it back on >> >> the >> >>>> server but that wouldn't work if two users simultaneously did so. >> Since >> >>>> I'll have many users using the database simultaneously everything has >> >> to be >> >>>> done on the server. I know only one user can write to an sqLite >> >> database >> >>>> at a time, but that only takes about 20 milliseconds if done on the >> >> server >> >>>> and the other writes get cued, something that wouldn't happen in the >> >>>> download/re-upload scenario. >> >>>> >> >>> You are going past what SQLite is meant to handle, and asking for >> >> trouble. >> >>> When SQLite writes, it changes a patch of disk (I couldn't tell you >> how >> >>> much). >> >>> >> >>> The other users won't be queued up waiting to write; they'll be >> getting >> >>> failure to open. >> >>> >> >>> You're either going to need a persistent middleware app running on the >> >>> server, or to follow the advice of the SQLite team: use postgres for >> >>> something like this. >> >>> >> >>> SQLite is wonderful, but it also knows it's limits. I use it >> in-memory, >> >>> and as a convenient way to throw backup files. >> >>> >> >>> And depending upon what you're doing, mySQL may not be an appropriate >> >>> choice. In particular, it doesn't handle real transactions. >> >>> >> >>> SQLite and postgres can handle >> >>> >> >>> BEGIN TRANSACTION; >> >>> >> >>> SELECT this from that; >> >>> >> >>> UPDATE that WITH thisstuff; >> >>> >> >>> UPDATE somethingElse WITH that >> >>> >> >>> END TRANSACTION; >> >>> >> >>> >> >>> whereas mySQL would do this as separate SELECT and two UPDATEs >> >>> >> >>> If you need either all or none of them to happen (e.g., dependencies >> and >> >>> consistency), mySQL is not your choice. >> >>> >> >>> postgres also means a single 20ms transaction for such things, while >> >> mySQL >> >>> would be three separate 20ms transactions. >> >>> >> >>> >> >> >> >> _______________________________________________ >> >> use-livecode mailing list >> >> use-livecode@lists.runrev.com >> >> Please visit this url to subscribe, unsubscribe and manage your >> >> subscription preferences: >> >> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> >> > _______________________________________________ >> > use-livecode mailing list >> > use-livecode@lists.runrev.com >> > Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> > http://lists.runrev.com/mailman/listinfo/use-livecode >> >> >> _______________________________________________ >> use-livecode mailing list >> use-livecode@lists.runrev.com >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> > _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
