Executing Named Queries in Athena

Question

We want to execute a parameterized query in Athena using the javascript sdk by aws.

Seems Athena's named query may be the way to do, but the documentation seems very cryptic to understand how to go about doing this.

It would be great if someone can help us do the following

• What is the recommended way to avoid sql injection in athena?
• Create a parameterized query like SELECT c FROM Country c WHERE c.name = :name
• Pass the name parameter's value
• Execute this query

Answer 1

Edit: this answer was written before Athena supported prepared statements.

Named queries is a weird feature of Athena that is not really useful for anything, unfortunately.

Athena does not support prepared statements like many RDBMSs. There are SQL libraries with support for doing parameter expansion client side – Sequel for Ruby is one I have experience with, unfortunately I can't give you a suggestion for JavaScript.

Escaping in Athena's SQL dialect isn't very complicated, however. In identifiers double quotes need to be escaped as two double quotes and in literal strings single quotes need to be escaped as single quotes. Other datatypes just need to be clean, e.g. only digits for integers.

Also, keep in mind that in Athena, the dangers of SQL injection are different than in an RDBMS: Athena can't delete your data. If you set up your IAM permissions correctly the user can't even drop tables, and even if you for some reason run queries with a user that is allowed to drop tables, tables are just metadata and can easily be set up again.