Special character are not allowed in the migration scripts

[CodeGTDLearn]

0) GitHub project link related with the error that causes this issue: https://github.com/PauloPortfolio/springwebflux-tdd-devdojo

1) The Special character "$" are not being accepted for the migration process.

2) For example: This INSERT statement is completely normal, and it is normal processed for the r2dbc-migrate BECAUSE we have a 'plaintext' password (DEVDOJO): INSERT INTO userspasswords (nameuser, username, password, authorities) VALUES ('demetria', 'demetria', 'devdojo', 'ROLE_USER');

3) HOWEVER, when I change the field 'PASSWORD' from PLAIN-TEXT 'DEVDOJO' to ENCRYTED PASSWORD 'DEVDOJO' ({bcrypt}$2a$10$lBm1Qy45bR/fNT5i5OUBseNPcONtZs10earjLZ773qq.byhK/yKmS), the specials characteres are not allowed, specially "$", for instance: INSERT INTO userspasswords (nameuser, username, password, authorities) VALUES ('demetria', 'demetria', '{bcrypt}$2a$10$lBm1Qy45bR/fNT5i5OUBseNPcONtZs10earjLZ773qq.byhK/yKmS', 'ROLE_USER');

     THIS FINAL ERROR HAPPENS:------------------------>>>

By 'Writing metadata version 3' 1 rows updated web-api_1 | 2020-08-04 20:54:43.971 INFO 7 --- [tor-tcp-epoll-1] n.n.r2dbc.migrate.core.R2dbcMigrate : Applying MigrationInfo{version=4, description='insert in table userpasswords', splitByLine=false, transactional=true} web-api_1 | 2020-08-04 20:54:43.977 INFO 7 --- [tor-tcp-epoll-1] n.n.r2dbc.migrate.core.R2dbcMigrate : By 'Releasing lock after error' 1 rows updated web-api_1 | 2020-08-04 20:54:43.980 WARN 7 --- [ main] onfigReactiveWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'animeController': Unsatisfied dependency expressed through field 'service'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'animeService': Unsatisfied dependency expressed through field 'repo'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'animeRepository' defined in academy.devdojo.webflux.repository.AnimeRepository defined in @EnableR2dbcRepositories declared on R2dbcRepositoriesAutoConfigureRegistrar.EnableR2dbcRepositoriesConfiguration: Cannot resolve reference to bean 'r2dbcDatabaseClient' while setting bean property 'databaseClient'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.data.r2dbc.R2dbcDataAutoConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'r2dbcMigrate' defined in class path resource [name/nkonev/r2dbc/migrate/autoconfigure/R2dbcMigrateAutoConfiguration.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Statement 'INSERT INTO userspasswords (nameuser, username, password, authorities) web-api_1 | VALUES ('paulo', web-api_1 | 'paulo', web-api_1 | '{bcrypt}$2a$10$lBm1Qy45bR/fNT5i5OUBseNPcONtZs10earjLZ773qq.byhK/yKmS', web-api_1 | 'ROLE_ADMIN,ROLE_USER'); web-api_1 | web-api_1 | INSERT INTO userspasswords (nameuser, username, password, authorities) web-api_1 | VALUES ('demetria', web-api_1 | 'demetria', web-api_1 | 'devdojo', web-api_1 | 'ROLE_USER');' cannot be created. This is often due to the presence of both multiple statements and parameters at the same time. web-api_1 | 2020-08-04 20:54:43.991 INFO 7 --- [ main] ConditionEvaluationReportLoggingListener : web-api_1 | web-api_1 | Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled. web-api_1 | 2020-08-04 20:54:43.996 ERROR 7 --- [ main] o.s.boot.SpringApplication : Application run failed

[nkonev]

@PauloPortfolio Link is not available:

[nkonev]

Do you use Postgresql ?

[CodeGTDLearn]

@PauloPortfolio Link is not available:

Sorry, the repository is public now.

[CodeGTDLearn]

Do you use Postgresql ?

Yep, I am using Postgres.

[nkonev]

@PauloPortfolio This library doesn't do any assumptions about migration files content. It passes theirs content "as is" straight to the driver. See at driver's page:

PostgreSQL uses index parameters that are prefixed with $.

You need to escape your special symbols. Check here.

~I've created commit in my example.~

I've created other commit with Unicode-escaped string. U& prefix at the beginning of the string and \0024 instead $.

INSERT INTO customer(first_name, last_name)
VALUES
(U&'{bcrypt}\00242a\002410\0024lBm1Qy45bR/fNT5i5OUBseNPcONtZs10earjLZ773qq.byhK/yKmS', 'Unicode escape as workaround');

Its output:

2020-08-05 02:32:05.618  INFO 107944 --- [tor-tcp-epoll-2] n.n.r.m.example.CustomerApplication      : Output: Customer{id=5, firstName='{bcrypt}$2a$10$lBm1Qy45bR/fNT5i5OUBseNPcONtZs10earjLZ773qq.byhK/yKmS', lastName='Unicode escape as workaround'}

Seems problem is here. The driver tries to determine statement type. Could you please create issue in the r2dbc-postgresql repo ?

[nkonev]

https://github.com/pgjdbc/r2dbc-postgresql/issues/312