1

I want to apply parameter binding to the dynamic native query where the column name which i am fetching i will be passing that dynamically from the UI.(say emp_id in this case).

Following is the sample java code snippet, 

org.hibernate.Query queryMain;
String fetchColumn="emp_id";
String query;
query="select e.:id from Employee e";
queryMain = (org.hibernate.Query) em.createNativeQuery(query).unwrap(org.hibernate.Query.class);
queryMain.setParameter("id", fetchColumn);

But when i execute this code i am getting sql syntax error exception. When i print the sql query i am getting as follows

select 'emp_id' from Employee

Since the column was set in string literals it is throwing the sql syntax exception. Can someone help me on this please. Thanks in advance!

greenhorn
  • 594
  • 6
  • 19
  • 1
    This syntax is used to bind parameters, not column names. If your column name is dynamic, I think you need to build it using a StringBuilder (or something else, taking care of SQL injection) – HBo May 02 '18 at 08:40
  • @HBo yes thanks for your reply, in my case table name is also dynamic and i got same string literal for table name also, so how to deal that one? – greenhorn May 02 '18 at 08:42
  • Unless there's a better solution I don't know of, you need to do it yourself. I'll draft a small example – HBo May 02 '18 at 08:44

1 Answers1

1

As I told you in my comment, the whole point of setParameter is to bind parameter values and prevent SQL injection. You can't use it to pass litterals because they will be surrounded with quotes. To build a query dynamically, you can do something like this:

StringBuilder sb = new StringBuilder();
String fetchColumn = "emp_id"; //take care of SQL injection if necessary
String tableName = Employee.class.getSimpleClassName();
sb.append("select ").append(fetchColumn)
    .append("from ").append(tableName);
String query = sb.toString();
// rest of your code here
HBo
  • 635
  • 6
  • 16
  • the fetchColumn variable will be getting dynamically from UI so instead of "mp_id" if the user sends "emp_id, emp_addr" it will fetch the address column also hence sql injection may happen right? – greenhorn May 02 '18 at 09:20
  • Absolutely. If you don't control the string's value, you need to make sure that nothing can happen -> escape every special char, a 'classic' way – HBo May 02 '18 at 09:22
  • Thanks a lot for your technical support. – greenhorn May 02 '18 at 09:25
  • Always a pleasure :) – HBo May 02 '18 at 09:26