Issue with Regex parsing in FilterByFormula Params
jinocenc opened this issue · 0 comments
Discovery
Encountered this issue while trying to use this regex to compare hostnames of a URL inside a Base.
(String literal after being properly encoded behaves as expected when submitted as normal HTTP query parameter)
// ...
filterByFormula:
`REGEX_EXTRACT({Website},'^(?:https?:\\/\\/)?(?:[^./]+\\.)?([^/]+\\.[^./]+)')='duck.com'`,
//...
/**
* Returns:
* AirtableError {
* error: 'INVALID_FILTER_BY_FORMULA',
* message: 'The formula for filtering records is invalid: Invalid formula. Please check your formula text.',
* statusCode: 422
* }
*/
Issue
Formula functions are used and passed by the airtable.js client as an HTTP query param. Regex functions such as REGEX_EXTRACT() that are used as a value for the FilterByFormula field in CRUD operations are not passed correctly to the Airtable endpoint due to the limits of the encodeURIComponent() method:
Additional parsing is required to properly translate regex to the endpoint.
The Issue can be fixed by extending the default URI encoding characters to include ["!", "'", "(", ")"]
.replace(/[!'()*]/g, function(c) {
return '%' + c.charCodeAt(0).toString(16);
});
parts.push(`${encodeURIComponent(key)}=${encodeURIComponent(value)}`)
.
.
.
return parts.join('&').replace(/%20/g, '+').replace(/[!'()*]/g, function(c) {
return '%' + c.charCodeAt(0).toString(16);
});
Comment
- Not aware of any side effects to this change
- Regex may not be complete
- If there exists a better way to go about executing regex formulas I'd love to know
**EDIT**
moved .replace() function to return statement