Demo :rocket:
Context: I am building an invite-only website where users can access the website only if they have a valid invite code (which is passed as part of the URL in a query string parameter).
Now, an attacker might assume I am using some database to validate the code and they might try to forge several malicious invite codes to try to perform a query injection that matches an arbitrary record in the database and gives them access…
Eventually, they come up with the following code: ' >= 0 & ', they encode it and use it in the URL, resulting in the following URL: https://secret-pizza-party-fgpypfb66-lmammino.vercel.app/?code=%27%20%3E%3D%200%20%26%20%27
…and they got access without having to know an invite code!
Now, this is a silly example, but imagine if someone builds something similar to allow their customers to access sensitive information (invoices, medical records, shipment details, list of purchased products, etc)…
You should get a feeling for how serious this could end up being for a company using an AirTable table as a backend database…
I have personally witnessed companies losing thousands of customers, getting sued, and losing a significant amount of money and credibility on something like this!
Why is this happening?
I am storing all my invite data in an Airtable table that contains the following records:
code,name,favouriteColor,weapon
14b25700-fe5b-45e8-a9be-4863b6239fcf,Leonardo,blue,Twin Katana
ce7d5886-2166-4aee-8038-548f68b9739d,Michelangelo,orange,Nunchaku
ef7ab7b7-33d5-43b9-ad73-e73bb8fd8e77,Raffaello,red.Twin Sai
b0cbb4a4-8a31-4bc1-bee9-d6fe39c1a6b3,Donatello,purple,Bo
When the user accesses the page:
- The frontend application extracts the
code from the query string
- It makes a request to a backend API to get that code validated
- The backend, in turn, makes a request to the Airtable table using the JavaScript SDK (WEAK LINK)
- If the code can be found in the table, then the entire record is returned to the frontend and the user will be able to visualise the page
- If not, the users will see an error
The backend is the weak link here, because of the way it queries the Airtable table:
The code looks like this:
const airtable = new Airtable({ apiKey: process.env.AIRTABLE_API_KEY })
const base = airtable.base(process.env.AIRTABLE_BASE_ID)
// get an invite by invite code (promisified)
export function getInvite (inviteCode) {
return new Promise((resolve, reject) => {
base('invites')
// runs a query on the `invites` table
.select({
filterByFormula: `{invite} = '${inviteCode}'`, // <-- ⚠️ PROBLEM!,
maxRecords: 1
})
// reads the first page of results
.firstPage((err, records) => {
if (err) {
// propagate errors
console.error(err)
return reject(err)
}
// if the record could not be found
// we consider it an error
if (!records || records.length === 0) {
return reject(new Error('Invite not found'))
}
// otherwise we create an invite object from the first record
// (there should be only one with the give code) and return it
const result = {
code: String(records[0].fields.invite),
name: String(records[0].fields.name),
favouriteColor: String(records[0].fields.favouriteColor),
weapon: String(records[0].fields.weapon)
}
resolve(result)
})
})
}
The problem is that, in this code, we are constructing a formula for the filterByFormula by doing a simple string interpolation and this interpolation uses unsanitized user-provided data!
When an attacker uses the code ' >= 0 & ' we end up creating the following formula for the filterByFormula field
{invite} = '' >= 0 & ''
Which is effectively an expression that evaluates to TRUE for every single record.
Any arbitrary record (depending of the scan order of the table) will be selected and returned to the frontend!
The solution
AFAIK, unfortunately, Airtable does not provide an “official” way of dealing with this, nor the SDK offers alternative ways to build formulas (e.g. a builder pattern or something else that would serve as a more secure way to create a formula containing user input).
So it’s up to the developer to do the following:
- Realise that this kind of vulnerability is possible
- Write their own function to sanitize user input before embedding it into a formula (very hard to do correctly and comprehensively!)
What I am suggesting
- Airtable should make very very clear that this is possible everywhere where injections can happen!
- Airtable should provide some official ways to escape user input
At that point, as a developer I should be able to update my code and do something like this:
import Airtable, {escape} from 'airtable'
// ...
const formula = `{invite} = ${escape(inviteCode)}`
// ...
or something like:
import Airtable, {formula} from 'airtable'
// ...
const myFormula = formula.field('invite').eq(inviteCode).toString() // this should internally take care of sanitising any input
// ...
I hope this helps
