Standard way to prevent formula injections when using AirTable `select` and `filterByFormula`

[Luciano_Mammino]

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…

[Ben_Young1]

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.

[Luciano_Mammino]

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:

1. That airtable would explicitly warn users against this potential threat
2. That the official SDK would provide an integrated way to build formula using variables that are properly escaped (so devs don’t have to use string interpolation and handmade escape functions)

I hope this makes more sense now

[kuovonne]

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.

[Bill_French]

Luciano_Mammino:

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’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?

Luciano_Mammino:

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:

I would consider this a very big security issue that all developers must tackle at the edge.

Luciano_Mammino:

is there any secure way to handle formulas that need to contain user input data?

Um, yeah - it’s called good programming on the front-end. :winking_face:

[Bill_French]

kuovonne:

Bill will do a more thorough job.

LOL - but, there’s a chance I do not fully understand the issue.

[Bill_French]

Luciano_Mammino:

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:

I would consider this a provocative statement from someone who [perhaps] works for one of Airtable’s competitors. Am I getting warm? :winking_face:

[Luciano_Mammino]

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…

[Bill_French]

Luciano_Mammino:

That the official SDK would provide an integrated way to build formula using variables that are properly escaped (so devs don’t have to use string interpolation and handmade escape functions)

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.

[Bill_French]

Luciano_Mammino:

I might try to build a proper demo that showcases the vulnerability,

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.