Taking a step back from the app level of this. You can start ensuring it's strictly read only by only giving the DB user read only permissions. That's probably going to eliminate a huge part of your security headache.
On Fri, Oct 29, 2010 at 1:22 AM, chrisjacob <[email protected]> wrote: > Goal: > Low level read-only public access to query a large complex MySQL > database and return JSON(P)/XML results. > > Intro: > The following outlines some stuff specific to my own personal open- > source project (yet to be built). It should give this discussion some > direction... but more generalised answers are of course very welcome. > > Why?: > Technical audience, looking for low level control over results... > coding, documenting and maintaining a fully featured API would be to > much work for me alone. I plan to build a frontend app that lets > people test, share, favourite and vote on queries they are using... > it's kind of an experiment in building a community driven API to this > database. > > Environment: > A large MySQL database (GB's), Ruby on Rails, hosted on Engine Yard. > Database remains *static* for 24 hours - i.e. an incremental import is > run once daily. Large static database to be kept separate from the > main Rails application database - imports are taken from a 3rd party > and this database is only intended for data querying. > > The Obvious: > Strict limitations need to be put in place to avoid exploits and to > prevent the whole thing from crashing and burning if the API gains > popularity. > > Knocking Heads: > Additional ideas and any technical advice/tips on implementing the > following would be warmly welcomed... > 1. Enforce read-only access to the database - no matter what's thrown > at it! > 2. Fail elegantly on syntactically incorrect SQL > 3. Terminate & clean up any query's running for more then 10 seconds - > a strict timeout. > 4. Batch/Limit results to sets of 1000 > 5. Caching which takes advantage of the database being static for > 24hrs > 6. Speed - avoid any Rails bottlenecks > > Me: > I'm punching above my skill level with this project... so I expect to > do a lot of research, reading and learning. PHP background. Thinking > of looking behind the scenes of phpMyAdmin and rbDB (http://github.com/ > redox/rbdb) for some insights into DB interfaces to begin with. I like > a challenge ^_^ > > Outro: > If you think this is nuts I would love to hear your thoughts on an > alternative approach... constructive criticism plz. > > OMG you made it this far! > Thanks for reading! > - Chris > > -- > You received this message because you are subscribed to the Google Groups > "Ruby or Rails Oceania" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/rails-oceania?hl=en. > > -- Checkout my new website: http://myachinghead.net http://wakeless.net -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
