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".
1 Answer 1
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).
-
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: developers.google.com/issue-tracker/concepts/…Sanchke Dellowar– Sanchke Dellowar2021年02月25日 06:01:40 +00:00Commented Feb 25, 2021 at 6:01
-
1I'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– Kain0_02021年02月25日 06:40:30 +00:00Commented Feb 25, 2021 at 6: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– jaskij2021年02月26日 21:54:08 +00:00Commented Feb 26, 2021 at 21:54
a = (select password from passwords)
valid?