1

We're working on an API to allow clients access to bits of data from a series of tables. We've come up with a JSON based API which works well but it could definitely be improved upon.

It has come to a point where we want to update the API to make it easier to use and an idea was presented of allowing raw SQL queries to be passed to the API for filtering and finding data. To counter the glaringly obvious insecurities involved we would...

  • Restrict the mysql user that will be executing the queries to SELECT only
  • Restrict the user with access to those tables that they need
  • ...?

Would there be any other major concerns with this method?

Script47
  • 217
  • 1
  • 11
  • 3
    Possible duplicate of [Does read-only access to the database prevent sql injection?](https://security.stackexchange.com/questions/86908/does-read-only-access-to-the-database-prevent-sql-injection). The question seems to be the same, though the top answer considers a broader case (arbitrary table read). It should still answer the questions (concerns would be DOS, code exec, file read, etc). – tim Feb 25 '19 at 13:44
  • @̶t̶i̶m̶ ̶t̶h̶a̶t̶ ̶s̶e̶e̶m̶s̶ ̶t̶o̶ ̶f̶o̶c̶u̶s̶ ̶o̶n̶ ̶t̶h̶e̶ ̶S̶Q̶L̶ ̶I̶n̶j̶e̶c̶t̶i̶o̶n̶ ̶a̶s̶p̶e̶c̶t̶ ̶(̶u̶n̶l̶e̶s̶s̶ ̶I̶'̶v̶e̶ ̶m̶i̶s̶r̶e̶a̶d̶)̶ ̶w̶h̶i̶c̶h̶ ̶I̶ ̶d̶o̶n̶'̶t̶ ̶b̶e̶l̶i̶e̶v̶e̶ ̶i̶s̶ ̶t̶h̶e̶ ̶c̶o̶n̶c̶e̶r̶n̶ ̶h̶e̶r̶e̶,̶ ̶I̶'̶m̶ ̶f̶o̶c̶u̶s̶i̶n̶g̶ ̶m̶o̶r̶e̶ ̶o̶t̶h̶e̶r̶ ̶c̶o̶n̶c̶e̶r̶n̶s̶ ̶w̶i̶t̶h̶ ̶v̶i̶e̶w̶i̶n̶g̶ ̶/̶ ̶u̶p̶d̶a̶t̶i̶n̶g̶ ̶/̶ ̶r̶e̶m̶o̶v̶e̶ ̶d̶a̶t̶a̶.̶ Fair enough, I guess the expansion here is that could those be prevented or would it not be worth the effort? – Script47 Feb 25 '19 at 13:48
  • That's a good question, though possibly too broad (it depends on the DBMS, etc). I would say with proper configurations, many issues could be addressed, though DOS and additional attack surface via vulnerabilities in the DBMS would be more difficult to solve. – tim Feb 25 '19 at 13:56
  • @tim Thanks for the replies. As far as DOS goes, I'm not too concerned purely because it is a "hidden" system in the sense that those that don't need it don't know it exists (not that that means that other people don't but I guess some throttling could be added). – Script47 Feb 25 '19 at 13:59
  • @Script47 Rather than use raw SQL, you may want to have a look into OData (https://www.odata.org) which would allow you the extra flexibility you're looking for. – GreatSeaSpider Feb 25 '19 at 14:41

0 Answers0