Error: Unable to open the database file

Wed Apr 6 16:03:04 EDT 2016

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 <pete at 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 <ray at 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 <ray at 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 <ray at 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 at 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 at 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 at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>