Getting column name instead of values in prepared statement

Question

I am getting column name instead of values in prepared statement.

here is my piece of code:

string q="select ? from EMPLOYEE where salary > ?";

Preparedstatement pst = connectionobject.preparedstatement(q);
pst.setstring(1, "FIRST_NAME");
pst.setint(2, 10000);

When I print out the result in JTable it shows FIRST_NAME in all rows.

Yes, that is what your code is doing `select "FIRST_NAME" from ....` — Scary Wombat, Jan 26 '17 at 08:28
So your column `FIRST_NAME` is in capitals and salary is not? Can you do a describe on the table pls — Hamster, Jan 26 '17 at 08:29
You cannot use `PreparedStatement` to build a dynamic query, the query itself must be fixed and the _parameters_ of the query variable. I would suggest writing a DAO layer with a query for each attribute you want from the `EMPLOYEE` table. — Boris the Spider, Jan 26 '17 at 08:38

score 1 · Answer 1 · answered Jan 26 '17 at 08:31

1

Your preparedStatement must produce the query : select "FIRST_NAME" from EMPLOYEE where salary > 10000 instead of select FIRST_NAME from EMPLOYEE where salary > 10000.

So it returns the string "FIRST_NAME" for each row.

You could simply use a StringBuilder to replace the first '?' by your column_name.

answered Jan 26 '17 at 08:31

Matthieu Saleta

1,388
1
11
17

what if the column not exit, can this make a problem with SQL Injection ? – Youcef LAIDANI Jan 26 '17 at 09:07
@YCF_L : Yes, personally i would not parameterize the select to avoid SQL Injection, and select every column i would need. But I think it's the only way to do it this basic PreparedStatement. – Matthieu Saleta Jan 26 '17 at 09:19
So this way can also true `String att = "FIRST_NAME"; string q="select " + att + " from EMPLOYEE where salary>?"; Preparedstatement pst=connectionobject.preparedstatement(q); pst.setint(1,10000);` – Youcef LAIDANI Jan 26 '17 at 09:22
It will work but it's not efficient. You are doing 2 concatenation although you could write `select FIRSTNAME where ...` – Matthieu Saleta Jan 26 '17 at 09:27
It looks like you want to dynamize the display of the JTable. Then you could always select all columns and dynamize the display inside your Java code. – Matthieu Saleta Jan 26 '17 at 10:55

score 1 · Accepted Answer · edited May 23 '17 at 11:53

This is not possible, with your way so to solve your problem.

Change your code like this :

String att = "FIRST_NAME";

string q="select " + att + " from EMPLOYEE where salary>?";

Preparedstatement pst=connectionobject.preparedstatement(q);
pst.setint(1,10000);

IF YOU AFRAID THAT THE USER CAN MAKE AN SQL Injection than use this solution

You should to check if your column exist in your table or not :

SELECT * 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

Like so :

public boolean column_exist(String att) {
    boolean succes = false;
    CreerConnection con = new CreerConnection();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultat = null;
    try {
        connection = con.getConnection();

        statement = connection.prepareStatement("SELECT * \n"
                + "FROM information_schema.COLUMNS "
                + " WHERE"
                + " TABLE_SCHEMA = 'db_name'"
                + " AND TABLE_NAME = 'table_name'"
                + " AND COLUMN_NAME = ?");
        statement.setString(1, att);
        resultat = statement.executeQuery();

        if (resultat.next()) {
            succes = true;
        }

    } catch (SQLException e) {
        System.out.println("Exception = " + e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
            }
        }
    }
    return succes;
}

If the column exist then you can continue like so:

if(column_exist(att)){
   string q="select " + att + " from EMPLOYEE where salary>?";

   Preparedstatement pst=connectionobject.preparedstatement(q);
   pst.setint(1,10000); 
}

Learn more here :

MySQL, Check if a column exists in a table with SQL

Hope this can help you.

Getting column name instead of values in prepared statement

2 Answers2

IF YOU AFRAID THAT THE USER CAN MAKE AN SQL Injection than use this solution