Search code examples
javaparametersinlineibatis

ibatis inline parameter with # in order by clause

below is my ibatis map configuration,

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap 
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.nik.Contact">
  select * from contact
  <dynamic prepend="where ">
        salary like '%'
    <isNotNull  property="orderby" >
        order by #orderby#, #orderby2#
    </isNotNull>
    </dynamic>
</select>
</sqlMap>

this is my pojo

package com.nik;

public class Contact {
      private String firstName; 
      private String lastName; 
      private String email; 
      private String salary;
      private String mobile;
      private String orderby;
      private String orderby2;
      private int id;

      public Contact() {}

      public Contact(
      String firstName,
        String lastName,
        String email) {
      this.firstName = firstName;
      this.lastName = lastName;
      this.email = email;
      }

      public String getEmail() {
      return email;
      }
      public void setEmail(String email) {
      this.email = email;
      }
      public String getFirstName() {
      return firstName;
      }
      public void setFirstName(String firstName) {
      this.firstName = firstName;
      }
      public int getId() {
      return id;
      }
      public void setId(int id) {
      this.id = id;
      }
      public String getLastName() {
      return lastName;
      }
      public void setLastName(String lastName) {
      this.lastName = lastName;
      }

    public String getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getOrderby() {
        return orderby;
    }

    public void setOrderby(String orderby) {
        this.orderby = orderby;
    }

    public String getOrderby2() {
        return orderby2;
    }

    public void setOrderby2(String orderby2) {
        this.orderby2 = orderby2;
    } 
    }

This is my test class,

package com.nik;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.sql.SQLException;
import java.util.*;

import org.apache.log4j.Logger;

public class IbatisExample{
  public static void main(String[] args)
  throws IOException,SQLException{
    // get a logger instance named "com.foo"
       Logger  logger = Logger.getLogger("com.nik");
  Reader reader = Resources.getResourceAsReader("ibatis-config.xml");
  SqlMapClient sqlMap = 
  SqlMapClientBuilder.buildSqlMapClient(reader);
  //Output all contacts
  System.out.println("All Contacts");
  Contact c1 = new Contact();
  c1.setOrderby("salary");
  c1.setOrderby2("mobile");
  List<Contact> contacts = (List<Contact>)
  sqlMap.queryForList("Contact.getAll",c1);
  Contact contact = null;
  for (Contact c : contacts) {
  System.out.print("  " + c.getId());
  System.out.print("  " + c.getFirstName());
  System.out.print("  " + c.getLastName());
  System.out.print("  " + c.getEmail());
  System.out.print("  " + c.getSalary());
  System.out.print("  " + c.getMobile());
  contact = c; 
  System.out.println("");
  }  
  }
}

The problem here is the order by clause has no effect...

here us output

All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement:    select * from contact   where      salary like '%'        order by ?, ?     
DEBUG [main] - {pstm-100001} Parameters: [salary, mobile]
DEBUG [main] - {pstm-100001} Types: [java.lang.String, java.lang.String]
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - Returned connection 71786792 to pool.
  1  abc    111  abc@hyahoo.com  5000  400
  2  def   222  def@yahoo.com  2000  100
  3  xyz  333  xyz@yahoo.com  3000  300

If I change the "#" with "$" in map config (e.g. order by $orderby$, $orderby2$) then it works,

All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement:    select * from contact   where      salary like '%'        order by salary, mobile     
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - Returned connection 71786792 to pool.
  2  def   222  def@yahoo.com  2000  100
  3  xyz  333  xyz@yahoo.com  3000  300
  1  abc  111  abc@hyahoo.com  5000  400

any clue on why inline parameter with # are not working in order by?? I can not use $ as this is as security risk as per fortify 360 :(

Solution

  • not an elegant solution but I used this as didnt want to do code change,

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap 
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.nik.Contact" parameterClass="java.util.Map">
  select * from contact where salary like '%'

        <dynamic prepend="order by">
        <isEqual 
             property="orderby" 
             compareValue="salary">
            salary,
        </isEqual>
        <isEqual 
             property="orderby" 
             compareValue="mobile">
            mobile,
        </isEqual>
        <isEqual 
             property="orderby2" 
             compareValue="salary">
            salary
        </isEqual>
        <isEqual 
             property="orderby2" 
             compareValue="mobile">
            mobile
        </isEqual>
        </dynamic>
</select>
</sqlMap>