Escape character should be added to PGSQL queries

[kopax]

Following #46, It appear the postgresql statement doesn't take care of escape character.

I have created a TEST table with double quote capital case :

CREATE TABLE "TEST" (
  "ID" SERIAL NOT NULL,
  "NAME" varchar(50) DEFAULT NULL,
  PRIMARY KEY ("ID")
);

I have the following error:

 Caused by: org.postgresql.util.PSQLException: ERROR: relation "test" does not exist

If I change from @Table(name = "TEST"). to @Table(name = "\"TEST\""), it work.

If we specify the default name for tables and column using an annotation, it should be automatically escaped.

[easybest]

why you create a table with name "TEST"
if you did need double quote , you should use @Table(name = "\"TEST\"")
or you can just create table use:

CREATE TABLE TEST (
  "ID" SERIAL NOT NULL,
  "NAME" varchar(50) DEFAULT NULL,
  PRIMARY KEY ("ID")
);

[kopax]

I wanted to have capital case table and column that's why.

This is my create statement :

CREATE TABLE "TEST" (
  "ID" SERIAL NOT NULL,
  "NAME" varchar(50) DEFAULT NULL,
  PRIMARY KEY ("ID")
);

In postgresql, the escape character is " (double-quote) in mysql, it is "`" (backtick)

I think the user input should always be escaped by dao framework.

Of course I can write @Table(name = "\"TEST\""), but a user would never write @table(name = "TEST") and expect to match the lower case table.

[easybest]

Column's name can be distinguished now , but table's table has not been achieved.

It will be completed in 1.0.6

[kopax]

Could this issue be reopened ?

The escaping is done on the table name but not on the column name, for example, I had this error :

Caused by: org.postgresql.util.PSQLException: ERROR: column "active" of relation "MANAGER" does not exist

To verify if it was the escaping again, I did change my class attributes to :

	@Version
	@NotNull
	@JdbcType(BIGINT)
	@Column(name = "VERSION")
	private Integer version;

	@CreatedDate
	@NotNull
	@JdbcType(DATE)
	@Column(name = "CREATION_DATETIME")
	private Date creationDateTime;

	@LastModifiedDate
	@NotNull
	@JdbcType(DATE)
	@Column(name = "MODIFICATION_DATETIME")
	private Date modificationDateTime;

	@JdbcType(BOOLEAN)
	@Column(name = "\"ACTIVE\"")
	@NotNull
	private Boolean active;

I have the following error :

Caused by: org.postgresql.util.PSQLException: ERROR: column "creation_datetime" of relation "MANAGER" does not exist

I have tried to create a wrapColumnName based on wrapTableName implementation but I got confused with all these idProperty.getColumnName() and property.getColumnName(), replacing all of these with the new wrapper didn't work.

[easybest]

I will resolve this problem

[kopax]

Is it scheduled in 1.0.7 or patch coming soon ?

[easybest]

coming soon

[kopax]

I am so sorry to ask that, but can we reopen this ?

Annotations used for creating queries like @ManyToMany, @OneToOne, @OneToMany also require escaping.

I have the following :

@JdbcType(BIGINT)
@OneToOne(referencedColumnName = "COMPANY_ID")
@NotNull
private Company company;

This will generate an invalide insert statement and throw error :

Application startup failed
java.lang.IllegalStateException: Failed to execute CommandLineRunner
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:803)
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:784)
	at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:771)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175)
	at com.Application.main(Application.java:49)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "company_id" of relation "CUSTOMER" does not exist
  Position: 150
### The error may involve com.domain.customerManagement.Customer._insert-Inline
### The error occurred while setting parameters
### SQL: insert into "CUSTOMER"("ACTIVE","CREATION_DATETIME","FIRST_NAME","LAST_NAME","MODIFICATION_DATETIME","PRINCIPAL_PHONE","PRINCIPAL_ADDRESS","VERSION",company_ID) values(?,?,?,?,?,?,?,?,?)
### Cause: org.postgresql.util.PSQLException: ERROR: column "company_id" of relation "CUSTOMER" does not exist
  Position: 150
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "company_id" of relation "CUSTOMER" does not exist
  Position: 150
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy93.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:279)
	at org.springframework.data.mybatis.repository.support.SqlSessionRepositorySupport.insert(SqlSessionRepositorySupport.java:101)
	at org.springframework.data.mybatis.repository.support.SimpleMybatisRepository.save(SimpleMybatisRepository.java:80)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:503)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:488)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy122.save(Unknown Source)
	at com.common.config.DatabaseLoader.run(DatabaseLoader.java:112)
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800)
	... 6 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "company_id" of relation "CUSTOMER" does not exist
  Position: 150
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:157)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
	... 32 common frames omitted
Closing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@2dd29a59: startup date [Fri Dec 23 23:35:55 ICT 2016]; root of context hierarchy
Unregistering JMX-exposed beans on shutdown
Disconnected from the target VM, address: '127.0.0.1:43211', transport: 'socket'

Process finished with exit code 1