Enhance reusability and safety utilizing Amazon Athena parameterized queries

Amazon Athena is a serverless interactive question service that makes it simple to investigate knowledge in Amazon Easy Storage Service (Amazon S3) utilizing commonplace SQL, and also you solely pay for the quantity of information scanned by your queries. Should you use SQL to investigate your small business every day, you might end up repeatedly working the identical queries, or related queries with minor changes. Athena parameterized queries allow you to put together statements you’ll be able to reuse with totally different argument values you present at run time. Athena parameterized queries additionally present a layer of safety towards SQL injection assaults, and masks the question string in AWS CloudTrail for workloads with delicate knowledge.

This publish reveals you how you can create and run parameterized queries in Athena. This publish supplies an instance of how Athena parameterized queries shield towards SQL injection, and reveals the CloudTrail occasions with the masked question string. Lastly, the publish critiques features associated to managing Athena ready statements. If you wish to comply with alongside, this publish supplies steps to arrange the parts with a pattern dataset; alternatively, you should use your individual dataset.

Reusability

Athena ready statements let you run and reuse queries inside your Athena workgroup. By decoupling the queries from the code, you’ll be able to replace your ready statements and your purposes unbiased from each other. If an information lake has schema updates, it may require question updates. If a number of purposes share the identical Athena workgroup and are utilizing related queries, you’ll be able to create a brand new question or replace the prevailing question to serve a number of use circumstances, with out every software being required to regulate related queries in their very own supply code. Parameterized queries are at the moment supported for SELECT, INSERT INTO, CTAS, and UNLOAD statements. For essentially the most present checklist, check with Concerns and Limitations in Querying with Ready Statements.

Safety

Athena ready statements present a layer of safety towards SQL injection. If you’re utilizing Athena behind an software interface, free textual content inputs inherently current a SQL injection risk vector which, if left unmitigated, may end in knowledge exfiltration. When the parameterized question is run, Athena interprets the arguments as literal values, not as executable instructions nor SQL fragments like SQL operators.

When utilizing Athena, CloudTrail captures all Athena API calls as audit occasions to offer a file of actions taken by an AWS consumer, function, or AWS service. Prospects with delicate knowledge of their knowledge lakes, equivalent to personally identifiable data (PII), have informed us they don’t need question strings of their CloudTrail occasion historical past for compliance causes. When working parameterized queries, the question string is masked with HIDDEN_DUE_TO_SECURITY_REASONS within the CloudTrail occasion, so that you don’t present protected knowledge inside your log streams.

Answer overview

This publish paperwork the steps utilizing the general public Amazon.com buyer critiques dataset; nevertheless, you’ll be able to comply with related steps to make use of your individual dataset.

The instance question is to discover a product’s 4-star (out of 5 stars) critiques voted as essentially the most useful by different prospects. The intent behind the question is to seek out question outcomes that point out constructive product suggestions. The intent is to validate the suggestions and get useful suggestions included into the product roadmap. The product used on this use case is the Amazon Smile eGift Card.

Stipulations

As a prerequisite, you want a foundational understanding of SQL syntax, in addition to a foundational understanding the next AWS providers:

This publish assumes you’ve:

Deploy assets for the instance dataset

Should you’re utilizing the instance dataset, comply with the steps on this part. The information is in an S3 bucket in an AWS-managed AWS account. You must create Athena and AWS Glue assets to get began.

This publish supplies a CloudFormation template that deploys the next assets in your AWS account:

• AthenaWorkGroup – An Athena workgroup in your dataset and ready statements. On the console, this workgroup is known as PreparedStatementsWG.
• GlueDatabase – A database within the AWS Glue Information Catalog for desk metadata. The database is known as athena_prepared_statements.
• GlueTableAmazonReviews – An exterior desk with Amazon.com buyer critiques within the Information Catalog.

The next diagram reveals how the assets work together when the question runs.

To deploy the CloudFormation template, comply with these steps:

1. Navigate to this publish’s GitHub repository.
2. Clone the repository or copy the CloudFormation template athena-prepared-statements.yaml.
3. On the AWS CloudFormation console, select Create stack.
4. Choose Add a template file and select Select file.
5. Add athena-prepared-statements.yaml, then select Subsequent.
6. On the Specify stack particulars web page, enter the stack identify athena-prepared-statements-blog.
7. For S3QueryResultsBucketName, enter your S3 bucket identify.
8. Should you go away AthenaWorkGroupName as default, the Athena workgroup is known as PreparedStatementsWG. Should you change the worth, the Athena workgroup identify have to be distinctive in your AWS Area.
9. Select Subsequent.
10. On the Configure stack choices web page, select Subsequent.
11. On the Overview web page, select Create stack.

The script takes lower than a minute to run and alter to a CREATE_COMPLETE state. Should you deploy the stack twice in the identical AWS account and Area, the AWS Glue database, desk, or Athena workgroup might exist already, and the method fails with a message indicating that the useful resource already exists in one other template.

For least-privilege authorization for deployment of the CloudFormation template, you’ll be able to create an AWS CloudFormation service function with the next IAM coverage actions. To do that, you should create an IAM coverage and IAM function, and select this function when configuring stack choices.

{
  "Model": "2012-10-17",
  "Assertion": [
    {
      "Effect": "Allow",
      "Action": [
        "glue:CreateDatabase"
      ],
      "Useful resource": [
        "arn:${Partition}:glue:${Region}:${Account}:catalog",
        "arn:${Partition}:glue:${Region}:${Account}:database/athena_prepared_statements"
      ]
    },
    {
      "Impact": "Enable",
      "Motion": [
        "glue:DeleteDatabase"
      ],
      "Useful resource": [
        "arn:${Partition}:glue:${Region}:${Account}:catalog",
        "arn:${Partition}:glue:${Region}:${Account}:database/athena_prepared_statements",
        "arn:${Partition}:glue:${Region}:${Account}:table/athena_prepared_statements/*",
        "arn:${Partition}:glue:${Region}:${Account}:userDefinedFunction/athena_prepared_statements/*"
      ]
    },
    {
      "Impact": "Enable",
      "Motion": [
        "glue:CreateTable"
      ],
      "Useful resource": [
        "arn:${Partition}:glue:${Region}:${Account}:catalog",
        "arn:${Partition}:glue:${Region}:${Account}:database/athena_prepared_statements",
        "arn:${Partition}:glue:${Region}:${Account}:table/athena_prepared_statements/amazon_reviews_parquet"
      ]
    },
    {
      "Impact": "Enable",
      "Motion": [
        "glue:DeleteTable"
      ],
      "Useful resource": [
        "arn:${Partition}:glue:${Region}:${Account}:catalog",
        "arn:${Partition}:glue:${Region}:${Account}:database/athena_prepared_statements",
        "arn:${Partition}:glue:${Region}:${Account}:table/athena_prepared_statements/amazon_reviews_parquet"
      ]
    },
    {
      "Impact": "Enable",
      "Motion": [
        "athena:CreateWorkGroup",
        "athena:DeleteWorkGroup",
        "athena:GetWorkGroup"
      ],
      "Useful resource": "arn:${Partition}:athena:${Area}:${Account}:workgroup/PreparedStatementsWG"
    }
  ]
}

For authorization for the IAM principal working the CloudFormation template and following alongside, this publish was examined with the next AWS managed insurance policies and the client managed coverage beneath.

AWS managed insurance policies:

• AmazonAthenaFullAccess
• AWSCloudTrailReadOnlyAccess
• AWSCloudFormationFullAccess

Buyer managed coverage:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "ViewS3BucketsWithoutErrors",
            "Effect": "Allow",
            "Action": [
                "s3:ListAllMyBuckets"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Sid": "InteractWithMyBucketAndDataSetBucket",
            "Impact": "Enable",
            "Motion": [
                "s3:GetObject",
                "s3:GetBucketLocation",
                "s3:ListBucket",
                "s3:PutObject"
            ],
            "Useful resource": [
                "arn:aws:s3:::${my-bucket-name}*",
                "arn:aws:s3:::amazon-reviews-pds*"
            ]
        },
        {
            "Sid": "UploadCloudFormationTemplate",
            "Impact": "Enable",
            "Motion": "s3:PutObject",
            "Useful resource": "arn:aws:s3:::cf-template*"
        },
        {
            "Sid": "CleanUpResults",
            "Impact": "Enable",
            "Motion": [
                "s3:DeleteObject"
            ],
            "Useful resource": [
                "arn:aws:s3:::${my-bucket-name}/results*"
            ]
        },
        {
            "Sid": "ListRolesForCloudFormationDeployment",
            "Impact": "Enable",
            "Motion": [
                "iam:ListRoles"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Sid": "IAMRoleForCloudFormationDeployment",
            "Impact": "Enable",
            "Motion": [
                "iam:PassRole"
            ],
            "Useful resource": [
                "arn:${Partition}:iam::${Account}:role/${role-name}"
            ],
            "Situation": {
                "StringEquals": {
                    "iam:PassedToService": "cloudformation.amazonaws.com"
                }
            }
        }
    ]
}

Partition the instance dataset

The CloudFormation template created an exterior desk pointing at a dataset of over 130 million buyer critiques from Amazon.com. Partitioning knowledge improves question efficiency and reduces price by limiting the quantity of information scanned by every question. The exterior desk for this dataset has Hive-compatible partitions. The MSCK REPAIR TABLE SQL assertion scans the prefix paths within the S3 bucket and updates the metadata within the Information Catalog with the partition metadata. To entry the dataset, the exterior desk’s partitions have to be up to date.

After you deploy the CloudFormation template, full the next steps:

1. On the Athena console, select Question editor within the navigation pane.
2. For Information Supply, enter AwsDataCatalog.
3. For Database, enter athena_prepared_statements.
4. On the Workgroup drop-down menu, select PreparedStatementsWG.
5. Select Acknowledge to substantiate.
6. Within the question editor pane, run the next SQL assertion in your exterior desk:

MSCK REPAIR TABLE athena_prepared_statements.amazon_reviews_parquet;

This question takes roughly 15 seconds to run when examined in us-east-1.

7. Run the next question to checklist the out there partitions. The instance dataset has partitions primarily based on product_category.

SHOW PARTITIONS athena_prepared_statements.amazon_reviews_parquet;

8. Run a SELECT assertion to output a pattern of information out there within the desk:

SELECT * FROM athena_prepared_statements.amazon_reviews_parquet restrict 10;

Create ready statements

To make use of Athena parameterized queries, first you run the PREPARE SQL assertion and specify your positional parameters, denoted by query marks. The Athena ready assertion is saved with a reputation you specify.

Run the next PREPARE assertion within the Athena question editor. This instance question, named product_helpful_reviews, supplies buyer critiques with three parameters for a specified product ID, star score supplied by the reviewer, and minimal variety of useful votes supplied to the evaluation by different Amazon.com prospects.

PREPARE product_helpful_reviews FROM
SELECT product_id, product_title, star_rating, helpful_votes, review_headline, review_body
FROM amazon_reviews_parquet WHERE product_id = ? AND star_rating = ? AND helpful_votes > ?
ORDER BY helpful_votes DESC
LIMIT 10;

You could possibly additionally use the CreatePreparedStatement API or SDK. For instance, to create your ready assertion from AWS CLI, run the next command:

aws athena create-prepared-statement 
--statement-name "product_helpful_reviews" 
--query-statement "SELECT product_id, product_title, star_rating, helpful_votes, review_headline, review_body FROM amazon_reviews_parquet WHERE product_id = ? AND star_rating = ? AND helpful_votes > ? ORDER BY helpful_votes DESC LIMIT 10;" 
--work-group PreparedStatementsWG 
--region area

For extra data on creating ready statements, check with SQL Statements in Querying with Ready Statements.

Run parameterized queries

You possibly can run a parameterized question towards the ready assertion with the EXECUTE SQL assertion and a USING clause. The USING clause specifies the argument values for the ready assertion’s parameters.

Run the next EXECUTE assertion within the Athena question editor. The ready assertion created within the earlier part is run with parameters to output 4-star critiques for the Amazon Smile eGift Card product ID with at the very least 10 useful votes.

EXECUTE product_helpful_reviews USING 'BT00DDVMVQ', 4, 10;

Should you obtain the message PreparedStatement product_helpful_reviews was not present in workGroup major, be sure to chosen the PreparedStatementsWG workgroup.

For extra data on working parameterized queries, check with SQL Statements in Querying with Ready Statements.

Masks question string knowledge in CloudTrail occasions utilizing parameterized queries

It’s possible you’ll wish to use parameterized queries to redact delicate knowledge from the question string seen in CloudTrail occasions. For instance, you could have columns containing PII as parameters, which you don’t need seen in logs. Athena routinely masks question strings from CloudTrail occasions for EXECUTE statements, changing the question string with the worth HIDDEN_DUE_TO_SECURITY_REASONS. This helps you keep away from displaying protected knowledge in your log streams.

To entry the CloudTrail occasion for the question, full the next steps:

1. Navigate to the Occasion historical past web page on the CloudTrail console.
2. On the drop-down menu, select Occasion identify.
3. Seek for StartQueryExecution occasions.

CloudTrail occasion information for parameterized queries embrace a queryString worth redacted with HIDDEN_DUE_TO_SECURITY_REASONS. The question string is seen within the Athena workgroup’s question historical past. You possibly can management entry by utilizing least-privilege IAM insurance policies to Athena, the AWS Glue Information Catalog, and the Amazon S3 question output location in your workgroup settings. For extra data on viewing current queries, check with Viewing Current Queries. For extra data on IAM insurance policies, check with Actions, assets, and situation keys for AWS providers.

Layer of safety for SQL injection

On this part, you’re proven an instance of a SQL injection assault, and the way ready statements can shield towards the identical assault. We use the Athena console to invoke the StartQueryExecution API towards a desk named customers with three rows.

SQL injection is an try and insert malicious SQL code into requests to vary the assertion and extract knowledge out of your dataset’s tables. With out Athena parameterized queries, if you happen to’re querying a dataset immediately or appending consumer enter to a SQL question, and customers can append SQL fragments, the dataset could also be susceptible to SQL injection assaults which return unauthorized knowledge within the outcome set.

This publish reveals an instance of inserting a SQL fragment in a malicious method. Within the instance, an OR situation which is able to all the time return true (equivalent to OR 1=1) is appended to the WHERE clause. The identical instance question is proven with Athena parameterized queries, and the question fails as a result of it accommodates an invalid parameter worth, for the reason that parameter worth is anticipated to be an integer however accommodates the characters “OR”. If the parameter was primarily based on a String column, then the identical SQL injection try would outcome within the question returning no outcomes as a result of the positional argument is interpreted as a literal parameter worth.

Athena supplies an extra layer of protection towards multi-statement SQL injection assaults. Trying to carry out SQL injection with an executable command (equivalent to DROP) leads to a failed question with Athena offering an error Just one sql assertion is allowed, as a result of Athena solely accepts one executable command per SQL assertion submission.

Though Athena ready statements present a layer of safety towards SQL injection assaults, different precautions present further layers of protection. Athena ready statements will be part of your defense-in-depth technique. For extra data on layers of safety, check with Amazon Athena Safety.

SQL injection instance

The meant use of the SELECT question within the instance is to obtain a small set of values. Nonetheless, an attacker can manipulate the enter to append malicious SQL code. For instance, an attacker can enter a worth of 1 OR 1=1, which appends a real situation to the WHERE clause and returns all information within the desk:

SELECT * FROM customers WHERE id = 1 OR 1=1;

By appending malicious SQL code, the attacker can retrieve all rows of the customers desk, as proven within the following screenshot.

SQL injection try with a ready assertion

If we create ready statements with the identical question from the earlier instance, the executable command is handed as a literal argument for the parameter’s worth. If a consumer tries to move further SQL, they obtain a syntax error as a result of the WHERE clause relies on ID, which expects an integer worth.

1. Create a ready assertion utilizing the identical question towards the customers desk:

PREPARE get_user FROM SELECT * FROM customers WHERE id = ?

2. Set the parameter to a legit worth:

EXECUTE get_user USING 1

The anticipated outcome returns, as proven within the following screenshot.

3. Now, try and move a malicious worth:

EXECUTE get_user USING 1 OR 1=1

Operating this ready assertion produces a syntax error, as a result of an integer worth is anticipated, but it surely receives an invalid integer worth of 1 OR 1=1. The question and syntax error are proven within the following screenshot.

Working with ready statements

This part describes administrative features to make it simpler to work with ready statements.

Listing all ready statements in my AWS account

To checklist all ready statements in an Athena workgroup from the AWS Command Line Interface (AWS CLI), you’ll be able to run the next command:

aws athena list-prepared-statements --work-group workgroup_name --region region_name

If following the instance above, the command will return the next response.

{
  "PreparedStatements": [
    {
      "StatementName": "product_helpful_reviews",
      "LastModifiedTime": "2022-01-14T15:33:07.935000+00:00"
    }
  ]
}

To checklist all out there ready statements in your AWS account, you should use the AWS APIs. This publish supplies a pattern script utilizing the AWS SDK for Python (Boto3) to loop by all Areas in your account, and supply the ready statements per Athena workgroup.

Ensure you have AWS credentials the place you propose to run the Python script. For extra data, check with Credentials.

Clone the GitHub repo or copy the Python script list-prepared-statements.py from the repo and run the script:

python3 list-prepared-statements.py

Change <my-profile-name> along with your AWS profile identify when it prompts you, or go away empty to make use of default native credentials.

Enter the AWS CLI profile identify or go away clean if utilizing occasion profile: <my-profile-name>

The next textual content is the output of the script. If following alongside, the response returns solely the product_helpful_reviews ready assertion.

eu-north-1:
ap-south-1:
eu-west-3:
eu-west-2:
eu-west-1:
ap-northeast-3:
ap-northeast-2:
ap-northeast-1:
sa-east-1:
ca-central-1:
ap-southeast-1:
ap-southeast-2:
eu-central-1:
us-east-1:
        athena-v2-wg: my_select
        PreparedStatementsWG: get_user
        PreparedStatementsWG: get_contacts_by_company
        PreparedStatementsWG: product_helpful_reviews
        PreparedStatementsWG: count_stars
        PreparedStatementsWG: helpful_reviews
        PreparedStatementsWG: get_product_info
        PreparedStatementsWG: check_avg_stars_of_category
        PreparedStatementsWG: my_select_v1
        PreparedStatementsWG: my_select_v2
us-east-2:
us-west-1:
us-west-2:

Replace ready statements

You have got just a few choices for updating ready statements. It’s possible you’ll wish to do that to optimize your question efficiency, change the values you choose, or for a number of different causes.

1. Rerun the PREPARE assertion with the adjustments within the Athena question editor or towards the StartQueryExecution API.
2. Use the UpdatePreparedStatement API by way of the AWS CLI or SDK.

You need to use this API so as to add an outline to your ready statements or replace your queries. To replace your question assertion by way of this technique, you should present the assertion identify, workgroup identify, up to date question assertion, and optionally a brand new description. For extra details about the UpdatePreparedStatement API, check with update-prepared-statement.

It’s possible you’ll wish to roll out variations of your question. To take care of backward-compatibility for customers, you may create a brand new ready assertion with a distinct identify. For instance, the ready assertion may have a model quantity in its identify (equivalent to my_select_v1 and my_select_v2). When essential, you may talk adjustments to groups who depend on the ready assertion, and later deallocate the outdated ready assertion variations.

Delete ready statements

To delete a ready assertion, you should use the next question syntax when towards the StartQueryExecution API, or from inside the Athena question editor:

DEALLOCATE PREPARE product_helpful_reviews

You could possibly additionally use the DeletePreparedStatement API or SDK. For instance, to delete your ready assertion from AWS CLI, run the next command:

aws athena delete-prepared-statement --statement-name product_helpful_reviews --work-group PreparedStatementsWG --region area

Clear up

Should you adopted together with this publish, you created a number of parts which will incur prices. To keep away from future expenses, take away the assets with the next steps:

1. Delete the S3 bucket’s outcomes prefix created after you run a question in your workgroup.

With the default template, it’s named <S3QueryResultsBucketName>/athena-results. Use warning on this step. Except you might be utilizing versioning in your S3 bucket, deleting S3 objects can’t be undone.

2. Delete the Athena ready statements within the PreparedStatementsWG

You possibly can comply with the steps within the Delete ready statements part of this publish utilizing both the DEALLOCATE PREPARE assertion or delete-prepared-statement API for every ready assertion you created.

3. To take away the CloudFormation stack, choose the stack on the AWS CloudFormation console, select Delete, and make sure.

Conclusion

Athena parameterized queries make it simple to decouple your code base out of your queries by offering a strategy to retailer frequent queries inside your Athena workgroup. This publish supplied details about how Athena parameterized queries can enhance your code reusability and knowledge lake safety. We confirmed how one can arrange a pattern knowledge lake and begin utilizing parameterized queries in the present day. We additionally supplied an instance of the protections parameterized queries affords, and detailed further administrative features.

You will get began with Athena ready statements by way of the Athena console, the AWS CLI, or the AWS SDK. To be taught extra about Athena, check with the Amazon Athena Person Information.

Thanks for studying this publish! In case you have questions on Athena parameterized queries, don’t hesitate to go away a remark within the feedback part.

Concerning the Authors

Blayze Stefaniak is a Senior Options Architect at AWS who works with public sector, federal monetary, and healthcare organizations. Blayze relies out of Pittsburgh. He’s captivated with breaking down complicated conditions into one thing sensible and actionable. His pursuits embrace synthetic intelligence, distributed methods, and Excel system gymnastics. Blayze holds a B.S.B.A. in Accounting and B.S. in Info Methods from Clarion College of Pennsylvania. In his spare time, you will discover Blayze listening to Star Wars audiobooks, making an attempt to make his canines chuckle, and doubtless speaking on mute.

Daniel Tatarkin is a Options Architect at Amazon Internet Companies (AWS) supporting Federal Monetary organizations. He’s captivated with massive knowledge analytics and serverless applied sciences. Exterior of labor, he enjoys studying about private finance, espresso, and making an attempt out new programming languages for enjoyable.