Skip to content

Are prepared statements supported on Aurora Serverless v2 MySQL data api?

0

We have been using data api with Aurora Serverless v1 MySQL for a while now.

Recently, we migrated to Serverless v2 as recommended by AWS, but now we're facing this issue wherever we have a query with one or more parameters that needs to be run through data api:

error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1; Error code: 1064; SQLState: 42000

There's not much to reproducing the issue... just spin up a serverless v2 MySQL instance and try to run any command with parameters. It will fail.

Is it not supported, or is this a bug? Need to know so I can plan next steps. Thank you.

asked 2 years ago301 views

1 Answer
0

Hi,

Do you use ExecuteSqlCommandInput or ExecuteStatementCommandInput to prepare and submit your statements ? If you don't use the latter, please, switch to it and try to replicate the example of https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/ExecuteStatementCommand/

Best,

Didier

EXPERT

answered 2 years ago

  • Hi Didier We always use the ExecuteStatement API operation. And we didn't change anything in the calls or the code sorrounding them. The only change, as I mentioned, was moving from serverless v1 to serverless v2.

    For now the only thing that is working for us is sanitizing the input ourselves and sending the raw SQL, since parameters don't seem to be working at all.

    Our engine version is 8.0.mysql_aurora.3.08.0 . If you can get any call at all working with parameters in this engine version, please let us know how you managed to do it. We tried many things but nothing seems to work.

  • So, apparently, it's a breaking change on MySql, not on the data API. We used parameterized queries for creating db users dynamically in our IaC. Apparently, MySQL 8 doesn't accept a '?' after an 'IDENTIFIED BY', even though this worked in 5.7. According to the documentation in mysql.com this should work, but there seems to be some obscure implementation detail that prevents this from working properly anymore.

    One would always like to parameterize values in queries, especially passwords... but anyway, you can disregard as it's not related to AWS.

    Hope to help others finding this issue.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.