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년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인