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?

preguntada hace 2 años1071 visualizaciones
1 Respuesta
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
INGENIERO DE SOPORTE
Arun
respondido hace 2 años

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas