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 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南