It says in the documentation of RDS proxy that the connection is automaticaly pinned when the application uses a prepared statement:
Prepared statements cause the proxy to pin the session. This rule applies whether the prepared statement uses SQL text or the binary protocol. (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.html)
How am I supposed to protect my application against SQL injections while using RDS proxy? I am using this service to make the connection with the database faster in my microservices so I want the connection to be reused.
I had the same problem. I used RDS Proxy for the Postgresql RDS. To connect to RDS Proxy I used gem 'pg' (project on ruby).
At first, I disabled some initial queries to the database when the connection establish (like set timezone and etc).
And the problem with a prepared statement. The rds proxy make the session pinned if it sees a query like below:
SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
So if rds proxy sees that query the session will be pinned. But if you make the query in that way the session will no be pinned:
SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
So when I change my library code a little bit I solved this problem. And also it will really help if you enable logs for the rds proxy (Advanced configuration). After enabling you can see why your session is pinned in the Cloud Watch Metrics.
I tried to append ?binary_parameters=yes or &binary_parameters=yes to the connection string.
i.e.
postgres://user:password@rds-proxy.proxy-dgi349gjv95j.us-east-1.rds.amazonaws.com:5432/db_name?binary_parameters=yes
and I saw a drop on the pinned prepared_statements.
I haven't followed that solution yet as I am still investigating if RDS proxy is still the best option for our use case.