1

I would like to check if columnName is in table T in database. I am using spring boot and mybatis. This checking should be done exactly once, to not do overhead in network transfer and time.

Why ?
In my mybatis SQL query I have SELECT * FROM T .. ORDER BY ${columnName}. You know that it is prone to SQL Injection. So I must check if columnName is proper column name in table.

Approach 1
Hard Code in app columns of table T.
Approach 2 

List <String> columnNames;
@PostConstruct
void initColumnNames() {
   columnNames = mapper.getColumnNames();
}

Approach 3
in DAO (or mayby validation class) ?: 

void someDaoMethod(String columnName) {
   if (columnNames == null) { //using approach 2 this if is unneccessary
      columnNames = mapper.getColumnNames();
   }
   //check if columName is allowed (= is in columnNames)
}

Can you help me solve this problem ? What approach should I choose ?

  • iBatis uses prepared statements, so as long as your using the # notation, the queries will be safe from sql injection. – Gregg Mar 06 '17 at 15:03
  • @Gregg you are wrong: http://stackoverflow.com/questions/12430208/using-a-prepared-statement-and-variable-bind-order-by-in-java-with-jdbc-driver –  Mar 06 '17 at 18:17
  • @HaskellFun I'm okay with being wrong, but unsure how that post suggests that. Also, seems there might be nicer ways to tell someone they are wrong. – Gregg Mar 06 '17 at 18:18
  • 2
    @Gregg, hold your horses. My English is too weak to understand that `you are wrong` is offensive. Direct translation to my mother tongue is ok. I am intrested in how this problem (stated by OP) is solved. –  Mar 06 '17 at 18:25
  • @HaskellFun Understood. Thanks for clarifying. – Gregg Mar 06 '17 at 18:26
  • Which RDBMS are you using?? – Pau Mar 07 '17 at 07:38
  • @Pau SQL Server 2012 –  Mar 07 '17 at 21:00

0 Answers0