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

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则