0

I want the user to be able to provide a custom WHERE statement against a mysql-backed platform... do I need to worry about any possible SQL injection attacks notwithstanding invalid sql? See the pseudo code below.

return sqlExec("select a,b,c from mytable where %s", args[0])

Okay, in all seriousness I can see where this can go horribly wrong.

But given that multiStatements is disabled, what is the most elegant way I can prevent from undesired injections from happening? Would it only be limited to preventing SELECT...UNION and SELECT...INTO? Or would it just be making sure that "%s" is indeed a valid where_condition. Or am I missing something entirely?

If they want to run arbitrary SQL they've got to write it themselves. I'm sick of making "query builders".

Sanchke Dellowar
  • 1
  • If you want anything valid then you're stuffed - a lot of undesirable queries are valid as you know. You might be able to head it off with a decent security model that prevents access of any kind to anything other than "mytable". That would go a bit of the way for friendly users. The other thing is to simply expose that table and point them at a report builder like PowerBI and let them go nuts on the mytable dataset with no further access. – LoztInSpace Feb 25 '21 at 06:26
  • Which programming language? There are most likely libraries for you to do this. For example in Java there are different libraries for building queries dynamically - the JPA Criteria API, or QueryDSL, for example. – Jesper Feb 25 '21 at 12:29
  • What counts as valid? Is `a = (select password from passwords)` valid? – user253751 Feb 25 '21 at 16:47

1 Answers1

5

And then the SQL Engine was updated to include Select ... XYZ

In short don't allow the user to provide any SQL.

In long. The capability of database engines and sql has steadily expanded over the years. What was not valid SQL 10 years ago, is now valid and very powerful. Specifying negatives like not this feature, and not that feature is well and good, except next year there is a new feature that might be dangerous, and you probably won't be paying attention.

Instead try a positive specification. Allow the user to provide some CUSTOM SQL. This is a dialect unique to your application that starts with where and allows a hand picked number of columns, constraints, values, and other operators like is null, and, in (<values>), and whatever else is permitted.

The user submits this CUSTOM SQL to the application which is then parsed and verified as a valid and legitimate string in the CUSTOM SQL language. If it isn't present the user with a reasonable error. If it is, well what do you know it has already been validly compiled to an SQL fragment (as you were smart enough to keep CUSTOM SQL as a strict subset of SQL).

Kain0_0
  • 15,888
  • 16
  • 37
  • I was thinking about the custom sql route... do you have any recommendations as to what type of custom syntax I should use so I don't need to reinvent the wheel? I was looking at this: https://developers.google.com/issue-tracker/concepts/search-query-language – Sanchke Dellowar Feb 25 '21 at 06:01
  • 1
    I'd just write a parser for it. There are several parser engines out there to help you write one, but a simple parser is not that hard to write yourself. As to the exact syntax to support, what conditions do you want to support? No need throwing in stuff you don't want them using. Keep it simple and expand as needed. I'd say a list of property names, basic values like date time, string, int and the basic comparators (=, <, etc...) for starters. That should cover off 85% of all queries you'll ever get. – Kain0_0 Feb 25 '21 at 06:40
  • One big application following this route is Jira, iirc they use pseudo-SQL syntax, which they call JQL (J for Jira). The docs are available online. – jaskij Feb 26 '21 at 21:54