@Dominik_Bosnjak’s Javascript solution… (link) I can’t tell if it’s correct or not. It looks like interpolation, which would make it a security vulnerability, but it’s doing it in a template literal tagged with write, so JavaScript will pass it to the write function as a parameter (docs). The write function here could be escaping it properly. I don’t know what that function is, @Dominik_Bosnjak, can you link to the docs for that function so we can see if it escapes the inputs, and if so, how it does this?
@Peter_Coles in the code in your blog (link), you replace \n with \\n, which is incorrect (see my test below), same for replacing \t with \\t, and single quotes should not be escaped either. It also needs to escape backslashes.
Here is the test i ran, I tried all the variations that people suggested or implied (eg 2 character newline, escaping single quotes, etc) and they did not work, I played with them until they did work and then used that to build the escape function you see:
require 'uri'
require 'rack/utils'
def escape(str)
# This puts a backslash before backslashes and double quotes
str.gsub(/[\\"]/, "\\\\\\&")
end
base = ENV.fetch 'AIRTABLE_BASE'
key = ENV.fetch 'AIRTABLE_API_KEY'
uri = URI "https://api.airtable.com/v0/#{base}/testing%20escape%20functions"
[ "abc def",
"abc\ndef",
"abc\tdef",
'abc "def" ghi',
"abc 'def' ghi",
"abc \\ def",
].each do |str|
puts "===== #{str.inspect} -> #{escape(str).inspect} ====="
uri.query = Rack::Utils.build_query filterByFormula: %({Value}="#{escape(str)}")
system "curl", uri.to_s, "-s", "-H", "Authorization: Bearer #{key}"
puts
puts
end
Here are the results:
$ ruby test_airtable.rb
===== "abc def" -> "abc def" =====
{"records":[{"id":"recBibipUDVVydD0K","fields":{"Value":"abc def"},"createdTime":"2021-09-25T14:42:13.000Z"}]}
===== "abc\ndef" -> "abc\ndef" =====
{"records":[{"id":"recUzj6267kQDoS1b","fields":{"Value":"abc\ndef"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
===== "abc\tdef" -> "abc\tdef" =====
{"records":[{"id":"rec1pMhx7dsFRSPO9","fields":{"Value":"abc\tdef"},"createdTime":"2021-09-25T15:01:59.000Z"}]}
===== "abc \"def\" ghi" -> "abc \\\"def\\\" ghi" =====
{"records":[{"id":"recBINRpQ8W99yOfa","fields":{"Value":"abc \"def\" ghi"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
===== "abc 'def' ghi" -> "abc 'def' ghi" =====
{"records":[{"id":"recx8KmvWuDkhEPPT","fields":{"Value":"abc 'def' ghi"},"createdTime":"2021-09-25T15:04:59.000Z"}]}
===== "abc \\ def" -> "abc \\\\ def" =====
{"records":[{"id":"recMLYuYH3bNzH3X9","fields":{"Value":"abc \\ def"},"createdTime":"2021-09-25T13:56:38.000Z"}]}
I think Airtable really needs to publish an official escape function that we can all reference. It’s alarming that we’re here trying to piece it together. In 2021, injection attacks have been the number 3 security vulnerability in web applications, but I’m pretty sure it was #1 last year (I can’t link it because this forum software is not allowing me to have more than 3 links, but you can find my source by searching “OWASP top 10”).
It scares me how little this is discussed and how often I see interpolation proposed. As devs, we need to train our spider-sense to trigger on interpolation into structured strings like queries and JSON, and also to trigger on interpolation of user data in general, and the combination of those 2 should ring all the alarm bells, because that’s how you get your databases stolen and your companies ransomed.
