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

Development and integrations API
#21

I think we can debate as long as we want whether this use case is realistic or not or whether airtable is suitable for this use case or not, but i don’t think that debate really helps anyone, except maybe our egos, @Bill.French…

As long as airtable offers apis, people will end up using them in all sorts of ways (especially true for a product that has been long enough in the market and with so many users).

Security is everyone responsibility and it’s one of those things where it’s better to be safe than sorry.

I’ll do my best to report this to the documentation team.

Thanks for indulging me in the conversation

Regards

1 Like
#22

I agree in theory. In practice I think people will take as much as they can get.

I don’t have one. But I think people who don’t want to be developers should take care in who they hire as developers, and people who want to be developers need to be in environments where they can learn what they do not know they need to learn.

Very true.

Also true.

Thank you.

You’re welcome. Anytime I have a conversation that involves Bill, I learn something.

#23

We can all agree that if there’s a way to achieve something with an API, well-intentioned users and developers will explore these pathways. But, this is no different than using a chain saw. There are many things it can do; safe, responsible use ultimately falls to the consumer. If you’re not certain that you can operate it, hire a professional, but do not place the burden of safety 100% on the manufacturer.

Airtable has a duty to provide a security context for access to its data. I believe they have and continue to meet this obligation with modern and appropriate techniques. They do not have a duty to provide 100% of the education required to build everything possible with an API in any or many languages. And any attempt to do so may encourage people to use it in ways it was probably never intended to be used.

This is your first post in this community. It contains skulls, crossbones, and an indirect indictment of Airtable’s security. Whose ego did your opening statement serve exactly? :wink:

1 Like
#24

For the records, I did not mean to offend anyone’s sensibility in any way. Nor I intended to attack or belittle Airtable as a product and as a company in any way. So, sincere apologies if my post came across in some negative way and sincere apologies if anyone got offended by my writing style. That was not my intent.

With that being said, I hope people can leave out the form and focus on the substance of this thread.

It’s OK if we don’t agree on the severity of the issue here, but there is definitely an injection threat when using select with filterByFormula and user-provided data.

If we managed to do our little part to make things better against this threat, even by a little bit, we might have done something good with our time…

#25

For me, when the manufacturer markets its chainsaw as a tool that is easy for-non professionals to use, even if the customer has never used power tools before and doesn’t know where to go to hire a professional, that manufacturer has a higher burden to educate the consumer than the manufacturer who markets to only professional contractors.

If you continue to use Airtable, you will see that most threads in this community are not nearly as heated as this one. Most people here are friendly, helpful, and passionate about Airtable. Soft skills can be as difficult to learn and practice as the more technical ones, especially if they don’t come naturally. (I am guilty of this too!)

1 Like
#26

This is a huge security issue with Airtable and the dismissal of it is baffling. I will suggest my org gets off Airtable as quickly as possible.

1 Like
#27

I am baffled by your post. It appears like you only joined this forum a few minutes before posting this. The solution to the issue in this thread is also very simple, with two solutions already posted.

Why join a forum in order to post that you are leaving the platform? Why leave a platform due to a perceived security risk that even the original poster admits is not a flaw specific to Airtable?

If you are trying to make a statement to Airtable, prior comments in this thread state that anyone from Airtable is unlikely to read this thread. If you have an issue with Airtable, you can vote with your wallet, or you can contact support. Both of those actions are more likely to be noticed by Airtable compared to posting in this thread.

3 Likes
#28

I think I’ve made it clear that we both agree the risk of compromised access to data in any platform is high when you do things that allow it to be high. Please stop saying we don’t agree. We agree. It’s bad, and my recommendation 3500 words ago was don’t do that. :wink:

If you take that a step further and expose a web app that allows dynamic query construction without a security context in the web app itself, you’ve elevated the risk. If you comingle records intended for multiple classes of users, expose them to an API, and then fail to wrap such access with a security layer singling out the specific class intended, you are creating an even greater risk that class (a) gets a look at class (b’s) data. You don’t have to use filterByFormula to create this additional security risk.

Initially, you framed the narrative as a broad security flaw related to Airtable. You have since agreed that it’s not really Airtable; it’s any poorly designed web app. I think we both agree that the security architecture of web apps that lean on APIs from ANY platform matters greatly, and the complexities and risks of getting it wrong are far greater than simply focusing on filterByFormula.

Where I think we do not agree is the very premise of using filterByFormula in use cases external to the Airtable platform;

  • I think it’s no different than the risks posed by javascript eval() and should be avoided if at all possible. I have found many ways to do this, and I tend to believe the best part in a machine is no part at all. More parts lead to a greater risk of breakage and a whole host of problems. filterByFormula is simply one that exacerbates the likelihood of a security issue, but it also has the propensity of easily creating unanticipated queries which fail, and this is especially the case when allowing user input to craft such filters.

  • You’ve made it clear that filterByFormula is fine if you undertake the challenge of properly escaping various characters. Some developers have no qualms about using eval() as well.

Both our viewpoints are a matter of engineering preference and ironically resolve deep in the shadow of a #no/low-code platform. Since you clearly understand this problem well, why don’t you just build a better SDK? What’s stopping you from showing Airtable and the user community a better approach?

If you agree to stop sensationalizing this as an Airtable security flaw, I’ll agree to like your future posts where you frame narratives that are reasoned and helpful to API users without creating unnecessary fear, uncertainty, and doubt. Deal?

1 Like
#29

You and you org don’t need to leave Airtable. If you switch to another provider, chances are that they will have similar security threats.

Also, the dismissal is not by Airtable, but only by some users in the community. I reached out to Airtable support on other channels and I am sure they’ll be interested in discussing this…

Finally, keep in mind that what we are debating here is not whether airtable is secure or not, but if airtable could do more to mitigate the risk of users shooting themselves in the leg while using certain features of the platform…

1 Like
#30

OK… Bill, honestly I don’t know what to tell you. I already apologised for my “sensationalized” writing style. I made it clear that I just wanted to highlight what I still believe to be a severe threat for Airtable users. If you are still upset with me and keep attacking me, I doubt there’s anything else I can do for you!

@kuovonne here is also questioning why the debate got so heated here. So maybe I can give you one last “Sorry” if I stepped into your own personal space, triggered some bad memory, undermined your position here, or something like that. None of that was my intent!

You just keep remarking your points and telling me that my form was wrong and unnecessary. I don’t think that helps anyone at this point… We all got it and I apologized!

Now, I believe this is an open community and that you are not a gatekeeper here. Therefore, I don’t need to make a deal with you if I want to keep posting here. If I have something else to add here or in another thread I will do it, regardless if I have your personal approval or not. So thank you, but no thank you!

I expressed my points here in one way or another and I have nothing else to add to this post. So I’ll leave the stage to you now. Cheers!

PS: A big thanks to @kuovonne for engaging in an open and helpful way, I should have said that earlier!

#31

Oh dear. To be clear, I am not questioning this. I agree with Bill. I just have a different writing style and prefer to focus on different aspects of the issue.

Bill is not a gatekeeper, but he is an established, respected member of this community. He is also one of the most skilled users of the Airtable REST API and understands it better than any other frequent poster on this forum.

Bill was not offering a deal to allow you to keep posting. Rather, he was offering a deal where he would like your posts. That is a subtle but important distinction.

I also have the benefit of experience with Bill’s writing style, and I interpret his offer differently from you. It is his way of recognizing that you are capable of producing helpful, informative, balanced content and expressing a desire that you do so. I think that would be a better approach than repeated apologies.

2 Likes
#32

Since the original post that started this thread is basically reflected in your sentiment as well, and you apparently haven’t read the dialogue deeply enough to realize query injection is not a security issue with or central to only Airtable, I’ll try to be brief. But people who know me know that this is fundamentally impossible. Buckle up!

Poorly designed web apps that use features described by @Luciano_Mammino in the manner that he presented can be risky.

Let’s be really clear by breaking the security risk into more manageable points. I think the use of predicates matters greatly in any conversations involving security. :wink:

  1. Why # zero you ask? This point does not go without saying because it is central to the overall risk envelope that @Luciano_Mammino has raised. The risk exists if – AND ONLY IF – filterByFormula can be dynamically programmed through the web app’s UI with user input.

  2. In many cases, the risk of an injection attempt designed to see other records is zero because the entire data table is intended to be publically accessible.

  3. In some cases, the developer has exposed to the open Internet a mix of record classes in a manner that could cause certain users (i.e., hackers) to access records not intended for consumption.

  4. The attack vectors mentioned in this thread are framed without the benefit of a security context for the web app itself. @Luciano_Mammino failed to include this point in his scenario, perhaps to simplify his assertions. I cannot speak for his reasoning.

#0 narrows the breadth of this issue to a very small fraction of web apps built on Airtable.

If perhaps 3% of Airtable solutions are front-ended with a filterByFormula query, then perhaps less than 3/10th’s of one per cent are instrumented with a dynamic filterByFormula capability that can actually be altered by users. Despite the rarity of this design choice, some developers use this technique to make it easy for users to intentionally “inject” filtering parameters. @Luciano_Mammino is correct in assuming that web apps can be much more useful with this approach, but it’s not the only way to achieve it.

@Luciano_Mammino’s warning is valid in this very narrow use case if you are doing this.

In my view, this approach is a bit dated because it requires new REST request/response interchanges with the API to effectuate each new query. Long-session HTTP gateways provide a much faster query response without the risk of injection and all while lessening the API load on the Airtable instance (it’s a thing, BTW).

#1 is no factor.

No one cares if filterByFormula is used in an unpredictable manner.

#2 is certainly worrisome.

Don’t do this [my opinion]. :wink: It’s important to point out that Airtable provides ways to easily overcome this risk by scoping [with precision] what data is made accessible to the web app and its intended users. And you can do this if you take the steps suggested by @Luciano_Mammino and a bazillion other web development articles. Doing it yourself doesn’t make it a zero-risk proposition.

#3 is strangely absent from this entire thread.

I raised this point in a few passages above, but I think it was swept aside in the heat of the debate. When it comes to building web apps with sensitive data, most developers wrap the app in a very solid security context. It’s a matter of developer preference and largely influenced by business requirements, but it typically exists in every web app that contains pathways to sensitive information. I tend to use Firebase for this security layer, but there are many ways to secure web apps in a manner that makes them almost impervious to access by unintended nefarious actors.

Bottom Line

To exploit #2, you have to get past #3.

If I’ve missed something or there are design patterns that I failed to expose that make this molehill into a mountain that deserves this many thread updates, please enlighten us all.

You are free to do that, but it is tantamount to advising your company to jettison a platform for reasons that have nothing to do with the platform itself. From Oracle to Airtable, these risks are generally the same.

Leaving Airtable, for this reason, is like jumping into the escape pods because your perfectly good spaceship has only downloaded the soundtrack for Guardians of the Galaxy #1, and you’re tired of Spirit in the Sky.

#33

I’ve also raised my tray table and returned my seat to its full upright position.

I tangentially referred to this when I pointed out that the API request comes with the credentials/API Key of a valid user. The API has done its job of ensuring that the request came from a valid user. It is not the API’s responsibility to second-guess an authenticated and authorized request. It is the responsibility of the API Key holder to make sure that any use of his/her key is surrounded by the proper security. Unfortunately, many API Key holders don’t understand this.

I think the number of posts in this thread has more to do with what you and I find interesting than anything else. Ben has not returned to the conversation. If neither of us had replied to this thread, it might have died an obscure death. Alas, here I am being entertained by your posts when I could be doing something more productive, but less fun.

#34

Is that true? I thought Airtable’s API was limited to a single key/token for the workspace that the developer would use in a web app, thus acting purely as a proxy for all users of the web app against all assets of the account’s workspace. Is there a way to build a web app that authenticates a specific Airtable user and, in so doing, grants that user access to Airtable data in that user’s context? I didn’t think there was, but I could be wrong.

What I’m referring to in #3 is an entirely different security layer that is wrapped around the API security layer.

If you’re going to expose Airtable data to the open interwebs, an authentication layer likey exists (or should exist) well above the API security layer. It is this aspect of usual and customary web apps that is absent from @Luciano_Mammino’s conversation. Only after getting past this higher layer are you in a position to exploit an injection feature that presumably already has a valid Airtable API key with which to execute the query.

You are safety-minded indeed. I’ll bet you’ve not once argued with a flight attendant about their process. :wink: I have and I’ll share the story over on the Opensiders Slack channel.

#35

I’m referring to that one proxy API key. Typically it would be either the API key or the developer, or of the developer’s client.

It is possible for someone to write a web app that has the user input their own API key and then use that API key. But that would be a rare case. One of my co-workers actually did this with a non-Airtable script that needed to access the Airtable REST API. The script asked the user for the API key as it’s first input.

#36

Ah, okay. And so sorry for drifting off topic here in @Luciano_Mammino’s thread.

This is what I thought the API security constraints were. I was thinking maybe they recently advanced toward a more granular authentication model (as rumoured). In any case, the diagram below is how I believe most developers create a secure web applications layer when accessing Airtable with sensitive information. Of course, the Firebase features are interchangeable with many implementation approaches. In this diagram, the Airtable API is deep within the app. This doesn’t eliminate the risk of an injection attack (should the app include a dynamic filterByFormula design choice), but it does limit such attacks only to known and authenticated users (i.e., an inside job) that can be easily spotted in logs.

image
image1712×1306 159 KB

I tend to use Firebase for web apps because I prefer the real-time ability to cache-forward Airtable data, thus avoiding the REST API altogether. It has the benefit of employing Airtable in a support role as the backend without limiting scalability and all while eliminating any risk of an injection attack and offering <= 250ms response for any query.

While I doubt many Airtable low-coders are up for this type of design, it’s important to note that any low-coder with some HTML and javascript experience can build this and deploy 100% in the free tier, which I believe is up to 20,000 Firebase events per day.

BTW - I use this identical architecture with Coda (example of a map app driven by Coda data proxied into Firebase).

image
image1984×1314 218 KB

2 Likes
#37

I’ve seen this exact behavior before, both here and elsewhere. Someone new joins, minutes (sometimes seconds) later posts a scathing rebuke of the company with a threat to leave, and never comes back. Either that or something spammy, possibly with a suspicious link. Regardless of the content, my gut feeling is that there are bots out there designed specifically for these behaviors, but why someone would go to the effort of doing this—either for real or via a bot—is beyond me. Some apparently like to sow seeds of discontent wherever possible. :person_shrugging: