Preventing SQL Injection in Athena queries in the JS SDK
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?
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 !!!
Relevant questions
How to use JS SDK V3 to getItem from DAX: "@aws-sdk/client-dax" instead of "amazon-dax-client"
Accepted Answerasked 5 months agoV3 JS SDK Kinesis Client getting ERR_HTTP2_INVALID_SESSION error
asked 2 months agoCan I use the ID of my saved query to start query execution in Athena SDK?
asked a month agoClient AuthN in IoT Core using client certificate with aws-sdk v3
asked 22 days agoCan i execute an Athena saved query from lambda?
Accepted AnswerAthena Query timeout
asked 2 months agoPreventing SQL Injection in Athena queries in the JS SDK
asked 22 days agoINVALID_INPUT in Athena Query
asked 5 months agoaws-sdk/clients/cognitoidentityserviceprovider getUser() with v3 aws-sdk
Accepted Answerasked 21 days agoWhen querying athena using aws sdk, is it necessary to save results in s3?
asked 8 days ago