使用参数化查询
您可以使用 Athena 参数化查询在执行时使用不同的参数值重新运行同一个查询,从而帮助防止 SQL 注入攻击。在 Athena 中,参数化查询可以在任何 DML 查询或 SQL 预准备语句中采用执行参数的形式。
-
带有执行参数的查询可以在单个步骤中完成,而不是特定于工作组。您可以在任何 DML 查询中为要参数化的值放置问号。运行查询时,按顺序声明执行参数的值。参数的声明和参数的赋值可以在同一查询中完成,但单独进行。与预准备语句不同,您可以在提交带有执行参数的查询时选择工作组。
-
预准备语句需要两个单独的 SQL 语句:
PREPARE
和EXECUTE
。首先,在PREPARE
语句中定义参数。然后,运行为您定义的参数提供值的EXECUTE
语句。预准备语句特定于工作组;您不能在预准备语句所属的工作组的上下文以外运行它们。
注意事项和限制
-
参数化查询仅在 Athena 引擎版本 2 及更高版本中受支持。有关 Athena 引擎版本的更多信息,请参阅 Athena 引擎版本控制。
-
目前,参数化查询仅在
SELECT
、INSERT INTO
、CTAS
和UNLOAD
语句中受支持。 -
在参数化查询中,参数是位置参数,并用
?
指示。参数按其在查询中的顺序赋值。命名参数不受支持。 -
目前,
?
参数只能放在WHERE
子句中。不支持SELECT ? FROM table
之类的语法。 -
问号参数不能放在双引号或单引号以内(即
'?'
和"?"
不是有效的语法)。 -
要将 SQL 执行参数视为字符串,它们必须用单引号而不是双引号括起来。
-
如有必要,您可以在为参数化术语输入值时使用
CAST
函数。例如,如果您有一列date
类型在查询中参数化,并且想要查询日期2014-07-05
,则为参数值输入CAST('2014-07-05' AS DATE)
将返回结果。 -
预准备语句特定于工作组,预准备语句名称在工作组中必须唯一。
-
预准备语句的 IAM 权限是必需的。有关更多信息,请参阅 允许访问预编译语句。
-
在 Athena 控制台中使用执行参数的查询最多只能有 25 个问号。
使用执行参数查询
您可以在任何 DML 查询中使用问号占位符创建参数化查询,而无需先创建预准备语句。要运行这些查询,您可以使用 Athena 控制台,或者使用 Amazon CLI 或 AWS SDK,并在 execution-parameters
参数中声明变量。
在 Athena 控制台中运行具有执行参数的查询
在 Athena 控制台中运行具有执行参数(问号)的参数化查询时,系统会按照问号在查询中出现的顺序提示您输入值。
要运行具有执行参数的查询
-
请在 Athena 编辑器中输入带有问号占位符的查询,如以下示例所示。
SELECT * FROM "my_database"."my_table" WHERE year = ? and month= ? and day= ?
-
选择运行。
-
在 Enter parameters(输入参数)对话框中,按查询中每个问号的顺序输入值。
-
输入完参数后,选择 Run(运行)。编辑器会显示您输入的参数值的查询结果。
在这种情况下,您可以执行下列操作之一:
-
为同一个查询输入不同的参数值,然后再次选择 Run(运行)。
-
要立即清除输入的所有值,请选择 Clear(清除)。
-
要直接编辑查询(例如,添加或删除问号),请关闭 Enter parameters(输入参数)对话框。
-
要保存参数化查询以供后续使用,请选择 Save(保存)或者 Save as(另存为),然后为查询命名。有关使用已保存查询的更多信息,请参阅 使用已保存的查询。
为方便起见,Enter parameters(输入参数)对话框会记住先前为查询输入的值,只要您在查询编辑器中使用同一选项卡。
使用 Amazon CLI 运行具有执行参数的查询
要使用 Amazon CLI 运行具有执行参数的查询,请使用 start-query-execution
命令并在 query-string
参数中提供参数化查询。然后,在 execution-parameters
参数中,提供执行参数的值。以下示例对此方法进行了说明。
aws athena start-query-execution --query-string "SELECT * FROM table WHERE x = ? AND y = ?" --query-execution-context "Database"="default" --result-configuration "OutputLocation"="s3://DOC-EXAMPLE-BUCKET;/..." --execution-parameters "1" "2"
使用预准备语句进行查询
您可以使用预编译语句重复执行具有不同查询参数的同一查询。预准备语句包含参数占位符,其值在执行时提供。
注意
工作组中的最大预处理语句数为 1000。
SQL 语句
您可以使用 PREPARE
、EXECUTE
和 DEALLOCATE
PREPARE
SQL 语句在 Athena 控制台查询编辑器中运行参数化查询。
-
要指定通常会使用文字值的参数,请在
PREPARE
语句中使用问号。 -
若要在运行查询时将参数替换为值,请在
EXECUTE
语句中使用USING
子句。 -
要从工作组中的预准备语句中删除预准备语句,请使用
DEALLOCATE PREPARE
语句。
以下各部分提供了有关这些语句的其他详细信息。
PREPARE
准备要在之后运行的语句。预准备语句将以您指定的名称保存在当前工作组中。语句可以包含参数来代替要在查询运行时替换的文字。要替换为值的参数用问号表示。
语法
PREPARE
statement_name
FROMstatement
下表介绍了这些参数。
参数 | 描述 |
---|---|
statement_name |
要执行的预准备语句的名称。此名称在工作组范围内必须唯一。 |
语句 |
SELECT 、CTAS 或 INSERT
INTO 查询。 |
PREPARE 示例
以下示例将演示如何使用 PREPARE
语句。问号表示在运行查询时由 EXECUTE
语句提供的值。
PREPARE my_select1 FROM SELECT * FROM nation
PREPARE my_select2 FROM SELECT * FROM "my_database"."my_table" WHERE year = ?
PREPARE my_select3 FROM SELECT order FROM orders WHERE productid = ? and quantity < ?
PREPARE my_insert FROM INSERT INTO cities_usa (city, state) SELECT city, state FROM cities_world WHERE country = ?
PREPARE my_unload FROM UNLOAD (SELECT * FROM table1 WHERE productid < ?) TO 's3://DOC-EXAMPLE-BUCKET/' WITH (format='PARQUET')
EXECUTE
运行预准备语句。参数的值在 USING
子句中指定。
语法
EXECUTE
statement_name
[USINGvalue1
[ ,value2
, ... ] ]
statement_name
是预准备语句的名称。value1
和 value2
是要为语句中的参数指定的值。
EXECUTE 示例
以下示例将运行 my_select1
预准备语句,该语句不包含任何参数。
EXECUTE my_select1
以下示例将运行 my_select2
预准备语句,其中包含一个参数。
EXECUTE my_select2 USING 2012
以下示例将运行 my_select3
预准备语句,它使用两个参数。
EXECUTE my_select3 USING 346078, 12
以下示例为预准备语句 my_insert
中的参数提供字符串值。
EXECUTE my_insert USING 'usa'
以下示例为预准备语句 my_unload
中的 productid
参数提供数字值。
EXECUTE my_unload USING 12
DEALLOCATE PREPARE
从当前工作组中的预准备语句列表中删除具有指定名称的预准备语句。
语法
DEALLOCATE PREPARE
statement_name
statement_name
是要删除的预准备语句的名称。
示例
以下示例从当前工作组中删除 my_select1
预准备语句。
DEALLOCATE PREPARE my_select1
在 Athena 控制台中执行不带 USING 子句的预准备语句
如果在查询编辑器中使用语法 EXECUTE
prepared_stament
运行现有预准备语句,Athena 会打开 Enter parameters(输入参数)对话框,这样您就可以输入通常出现在 EXECUTE
... USING
语句的 USING
子句中的值。
使用 Enter parameters(输入参数)对话框运行预准备语句
-
在查询编辑器中,使用语法
EXECUTE
prepared_stament
而不是EXECUTE prepared_statement USING
Value1
,
Value2
...
。 -
选择运行。将显示 Enter parameters(输入参数)对话框。
-
按照 Execution parameters(执行参数)对话框中的顺序输入值。由于查询的原始文本不可见,因此必须记住每个位置参数的含义或使预准备语句可供参考。
-
选择运行。
使用 Amazon CLI 创建预准备语句
要使用 Amazon CLI 创建预准备语句,您可以使用以下 athena
命令中的一个:
-
使用
create-prepared-statement
命令并提供包含执行参数的查询语句。 -
使用
start-query-execution
命令并提供使用PREPARE
语法的查询字符串。
使用 create-prepared-statement
在 create-prepared-statement
命令中,定义 query-statement
参数中的查询文本,如以下示例所示。
aws athena create-prepared-statement --statement-name PreparedStatement1 --query-statement "SELECT * FROM table WHERE x = ?" --work-group athena-engine-v2
使用 start-query-execution 和 PREPARE 语法
使用 start-query-execution
命令。在 query-string
参数中放置 PREPARE
语句,如以下示例所示:
aws athena start-query-execution --query-string "PREPARE PreparedStatement1 FROM SELECT * FROM table WHERE x = ?" --query-execution-context '{"Database": "default"}' --result-configuration '{"OutputLocation": "s3://DOC-EXAMPLE-BUCKET/..."}'
使用 Amazon CLI 执行预准备语句
要使用 Amazon CLI 执行预准备语句,您可以使用以下方法之一为参数提供值:
-
使用
execution-parameters
参数。 -
使用
query-string
参数中的EXECUTE ... USING
SQL 语法。
使用 execution-parameters 参数
在此方法中,您可以使用 start-query-execution
命令并提供 query-string
参数现有预准备语句的名称。然后,在 execution-parameters
参数中,提供执行参数的值。以下示例说明了这一方法:
aws athena start-query-execution --query-string "Execute PreparedStatement1" --query-execution-context "Database"="default" --result-configuration "OutputLocation"="s3://DOC-EXAMPLE-BUCKET/..." --execution-parameters "1" "2"
使用 EXECUTE... 使用 SQL 语法
要使用 EXECUTE ... USING
语法运行现有的预准备语句,您可以使用 start-query-execution
命令并将预准备语句的名称和参数值都放在 query-string
参数中,如以下示例所示:
aws athena start-query-execution --query-string "EXECUTE PreparedStatement1 USING 1" --query-execution-context '{"Database": "default"}' --result-configuration '{"OutputLocation": "s3://DOC-EXAMPLE-BUCKET/..."}'
列出预准备语句列表
要列出特定工作组的预准备语句,可以使用 Athena list-prepared-statements--work-group
参数是必需的。
aws athena list-prepared-statements --work-group primary
其他 资源
请参阅 Amazon 大数据博客中的以下相关文章。