Airtable/airtable.js

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:

parts.push(`${encodeURIComponent(key)}=${encodeURIComponent(value)}`);

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