AWS Aurora MySQL prepared statement

Question

I have an insert query that I want to execute using the JavaScript V3 AWS client, against an AWS Aurora MySQL Serverless database. I am using the Data API of the database. I got the @aws-sdk/client-rds-data set up in my code and I can connect and execute arbitrary SQL queries using the ExecuteStatementCommand.

What I would like to know is that how I make SQL prepared statements and execute them. I have an INSERT query whose values are user provided. I cannot just concatenate those values into the SQL query as it would create an SQL injection vulnerability.

Unfortunatelly, I couldn't find how to make prepared statements and execute them in the @aws-sdk/client-rds-data package's documentation.

If somebody knows how to do it, could that someone please explain. Big thanks in advance!

Bill_Karwin_20860 · Answer

I'm not a user of the AWS SDK for client-rds-data, but I'm inferring the following from the documentation, and my own knowledge of MySQL.

I see that interface ExecuteSqlCommandInput has a property sqlStatements, which allows multiple SQL statements separated by semicolons. This precludes the use of query parameters, because in MySQL you can't use prepare() on a string that includes multiple SQL statements.

Whereas ExecuteStatementCommandInput has a property sql (a single statement) and a property parameters which is an array of scalar parameters (i.e. each scalar corresponds to one parameter placeholder in the sql string). This should allow you to run a parameterized SQL query.

Re your comment: When you said you wanted to use prepared statements to avoid SQL injection vulnerabilities, I assumed you understood how prepared statements protect against those vulnerabilities.

In fact, using prepared statements alone is not a defense. You have to separate dynamic inputs from your SQL query by using query parameters. It just happens that using query parameters requires using prepared statements, so people say "use prepared statements" to defend against SQL injection, when they should say "use query parameters, which implies you must use prepared statements."

Tutorials about using SQL with query parameters are abundant. Here's one for Node.js: https://www.veracode.com/blog/secure-development/how-prevent-sql-injection-nodejs

The calling convention for the AWS SDK is different, but the concept is the same. I have only found reference documentation for AWS SDK, no code examples or task-oriented documentation. This is disappointing but unfortunately typical for AWS.

AWS Aurora MySQL prepared statement

1 Answers