Is there any way to do a prepared statement with a placeholder in flyway?

It appears if I write this code, for example with Spring Boot

CREATE ROLE application WITH LOGIN PASSWORD '${password}';

and then set

FLYWAY_PLACEHOLDERS_PASSWORD="' DROP table -- "

before starting the migration, flyway would execute the injection, as the quoting provided is in the string. Is there a way I can make this a prepared statement, or is there a quoting function I can use to ensure that the value is properly quoted?

Solution

A small improvement over the existing query:

DO $$
DECLARE
    the_password TEXT := '${password}';
BEGIN
    EXECUTE format('CREATE ROLE application WITH LOGIN PASSWORD %L', the_password);
END
$$

Using format with %L will perform correct escaping for the query. Statements that don't close out the entire DECLARE/BEGIN/END block and the dollar quoted string will result in a parsing error.

This doesn't eliminate all possibilities of injection, but keep in mind the context here. This is a database migration system. I assume that it's not being executed using arbitrary input from untrusted users. It should only be executed by highly trusted users who have access to credentials with the needed privileges to create roles, create/drop tables, etc. As such, this is more an exercise in idiot-proofing, rather than a security mechanism. This query prevents accidents from causing damage; anything that breaks out of this is most likely malicious and would be more easily accomplished if the user bypassed the deployment system entirely.