Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 05, 2022 11:44 AM
NOTE I originally wrote this in a rush hoping that the information provided here would be sufficient to start a conversation. It turned out it was not. Please refer to this comment below for a better explanation (and A DEMO) of the issue: Standard way to prevent formula injections when using AirTable `select` and `filterByFormula` - #15 ...
Hello,
When using your APIs to get data dynamically from a table, is there any secure way to handle formulas that need to contain user input data?
How can we avoid a user trying to alter the formula by crafting an injection (like a SQL injection but for your formula language)?
I couldn’t find anything in the docs, nor any utility in your (JavaScript) SDK…
So far I had to come up with my own escape function…
In case you need an example, this is my formula:
{code} = 'someCodePassedByUser'
If the user passes the following code
' >= 0 & '
I end up with
{code} = '' >= 0 & ''
which is always TRUE!
Considering that injections are the 3rd item in the OWASP security top10, I would consider this a VERY BIG SECURITY FLAW for people using airtable as a backend. :skull_and_crossbones: :skull_and_crossbones: :skull_and_crossbones:
You should mention this in the docs and provide a standard way to sanitize user input for formulas.
A function built in the JavaScript SDK would be ideal…
Aug 05, 2022 02:11 PM
Hey @Luciano_Mammino.
I’m curious.
Could you build, replicate, and document an ‘injection’ for us to look at?
I want to ensure I understand exactly what you’re trying to describe.
Aug 05, 2022 02:45 PM
Let’s say that {code} is a unique code that allows a user to access some private content.
I might have a table where i correlate that code with the info, per record basis
Code1,privateinfo1
code2,privateinfo2
Code3,privateinfo3
In my backend code i might have a query to try to fetch a record by a given code
This might use a formula such as
{code} = ‘someCodePassedByUser’
If the user passes the following code
’ >= 0 & ’
I end up with
{code} = ‘’ >= 0 & ‘’
which is always TRUE!
So this might match any arbitrary record in my table (depending on ordering criteria) and return that record to a user who shouldn’t technically have access to that information.
This is not too different from a SQL injection against a password field.
The solution i have is a custom sanitization function that escapes quotes in the user provided input.
But i wish for 2 things:
I hope this makes more sense now
Aug 05, 2022 02:48 PM
I see that Bill is replying. I look forward to what he has to say and I suddenly feel that there is no point in commenting on the topic at hand because Bill will do a more thorough job.
Aug 05, 2022 02:57 PM
I’m almost certain you don’t understand the limitations of the Airtable API. There is no formula language that the API can work with. You cannot update formula fields, and formula fields expose their data by value, not by reference. As such, I’m almost certain what you are describing is not possible. There’s a downside to this, of course; an export of an Airtable base will not include formulas. Nor are there any back doors to update formulas manually or through programs.
If you’re referring only to the possibility that a web app could allow user-entered queries that expose data not intended by the developer, well – isn’t that what all developers must be concerned with regardless of the back end and irrespective of the query language?
I would consider this a very big security issue that all developers must tackle at the edge.
Um, yeah - it’s called good programming on the front-end. :winking_face:
Aug 05, 2022 02:58 PM
LOL - but, there’s a chance I do not fully understand the issue.
Aug 05, 2022 03:01 PM
I would consider this a provocative statement from someone who [perhaps] works for one of Airtable’s competitors. Am I getting warm? :winking_face:
Aug 05, 2022 03:05 PM
Feel free to look at my CV on LinkedIn. I was not trying to be provocative, i was just trying to highlight that the issue is quite serious IMHO…
This is something that could be improved to help developers build more secure apps while using airtable as a backend data storage.
I might try to build a proper demo that showcases the vulnerability, if i have time over the weekend…
Aug 05, 2022 03:05 PM
I think I’m still not understanding this thread - I write a lot of front and backend stuff, and I don’t have to use string interpolation and handmade escape functions. You gotta give us a little more context.
Aug 05, 2022 03:09 PM
I think that would be wise. You are conflating terms like SDK and API, and I sense I have no clear understanding of an apparently new and VERY BIG SECURITY FLAW that remained concealed until this moment in Airtable’s tenth year with hundreds of thousands of users. It would be like bringing the carcass of Big Foot to a Big Foot convention if you are right.