16

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
Jugal Thakkar
  • 13,432
  • 4
  • 61
  • 79
  • did it work for you? – Alessandro Oliveira Aug 16 '18 at 01:17
  • FYI, Athena version 2 now supports prepared statements (https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html), with the possibility to limit the access via IAM. Unfortunately, it is not possible to create these prepared statements via Cloudformation (like for NamedQueries), but otherwise I would say this is the correct thing to use. – MikeGM Apr 13 '21 at 14:19
  • Prepared statements are now available in CloudFormation: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-athena-preparedstatement.html – Theo Oct 18 '21 at 16:04

1 Answers1

6

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.

Theo
  • 131,503
  • 21
  • 160
  • 205
  • Thanks for sharing your thoughts. I am equally concerned about users not being able to read data that they are not supposed to. – Jugal Thakkar Feb 04 '19 at 06:47
  • That is of course a good point, and while IAM covers the whole table aspect of that it does not make it possible to limit access to particular rows and/or columns. And to clarify: I'm not saying you shouldn't do proper escaping, you should definitely do that, just that it's different from traditional RDBMSs. – Theo Feb 04 '19 at 06:54
  • 2
    Is there any new developments on this? Their original blog post https://aws.amazon.com/blogs/mt/aws-cloudformation-features-update-support-for-amazon-athena-coverage-updates-for-s3-rds-kinesis-and-cloudwatch/ explicitly states: "Named queries can then be executed manually from the AWS Management Console, CLI or programmatically using API calls." but there seems to be no way to do this via the API. – antti Feb 13 '20 at 13:29
  • What could named queries be used for? – darw Oct 18 '21 at 15:45
  • @darw I assume you downvoted because this answer does not reflect that Athena now supports prepared statements. Please note that when it was written Athena did not. Named queries are still without any real use cases. – Theo Oct 18 '21 at 16:10
  • @Theo Thanks for attention. Do you happen to know an imaginary or contrived use case for named queries? – darw Oct 18 '21 at 18:17
  • 1
    I _think_ the idea from the beginning was for it to be a way to store queries for sharing within teams. There would be one or more people creating queries to be consumed by other team members, and named queries was a way to provide that. The closest thing to an explanation I've found is "Named queries allow you to map a query name to a query and then call the query multiple times referencing it by its name", but that's a stretch since you can't actually run named queries as such. Named queries are basically just a key/value store within Athena. – Theo Oct 18 '21 at 18:40
  • As of November 2022, can anybody by now explain what is the difference between named queries and prepared statements in athena? is the named query concept deprecated in favour of prepared statements? – Jonas Bausch Nov 10 '22 at 15:12