Preventing SQL Injection in Athena queries in the JS SDK

0

Using the v3 js sdk Athena Client, I'm trying to safely execute a query that is constructed from user input. I want to avoid doing anything like this:

const queryString = `SELECT * from mytable where field = ${userSuppliedValue}`;

It seems like the proper solution to this would be using a prepared statement.

The SDK provides a way to create prepared statements like this:

await client.send(
  new CreatePreparedStatementCommand({
    Description: 'My example query',
    QueryStatement: 'SELECT * FROM mytable WHERE ?',
    StatementName: 'MyPreparedStatement',
    WorkGroup: 'primary',
      }),
  )

So, there should be a way to execute this prepared statement, providing user input safely to replace the ? parameter.

However, I can't find any way in the SDK to execute the statement, except to build a raw query:

const data = await client.send(
  new StartQueryExecutionCommand({
    QueryString: `EXECUTE MyPreparedStatement USING ${userSuppliedValue}`,
    ResultConfiguration: {
      OutputLocation:
            's3://my-example-bucket',
    },
  }),
);

Since this still involves building a raw query string with user input, it seems to leave me just as vulnerable to SQL injection as if I had not used a prepared statement.

The documentation on prepared statements says:

Prepared statements enable Athena queries to take parameters directly and help to prevent SQL injection attacks.

Is there some other programmatic way to execute prepared statements that I'm overlooking?

質問済み 2年前1071ビュー
1回答
0

The approach you are following seems appropriate. The only way to prevent SQL injection in your use-case would be Programmatically limit (handle it in your code) the user to enter alpha-numeric characters and prevent the code from passing ', ", -- and \ to the query.

Have a good day !!!

AWS
サポートエンジニア
Arun
回答済み 2年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ