SQL Like Query Error

Question

I have a ResultSet with a sql select query :

ResultSet rst = DB.search("select '"+col+"' from stud where '"+col+"' like '" + S3 + "%'");

In here col = FName(FName is a column);

Here's how FName gets assigned to col :

private void column(){
    switch (search_fields.getSelectedItem().toString()) {
        case "FName":
            col = "FName";
            break;
        case "MName":
            col="MName";
            break;
        case "LName":
            col="LName";
            break;
        case "DOB":
            col="DOB";
            break;
        case "Address":
            col="Address";
            break;
        case "MotherTP":
            col="MotherTP";
            break;
        case "FatherTP":
            col="FatherTP";
            break;
        case "School":
            col="School";
            break;
        case "Grade":
            col="Garde";
            break;
        case "Email":
            col="Email";
            break;
    }
}

Search_field is a combobox. There is no error but when I type a First Name(FName) the name of the column FName gets returned.

Here is the Whole Code :

private JTextField txtComboItemName;
private String S3;
private boolean  bbb;
private void ComboItemSearch() {
    bbb = false;
    txtComboItemName = (JTextField) search_txt.getEditor().getEditorComponent();
    txtComboItemName.addKeyListener(new KeyAdapter() {
        @Override
        public void keyReleased(KeyEvent evt) {
            if (!(
                evt.getKeyCode() == KeyEvent.VK_DOWN ||
                evt.getKeyCode() == KeyEvent.VK_UP ||
                evt.getKeyCode() == KeyEvent.VK_LEFT ||
                evt.getKeyCode() == KeyEvent.VK_RIGHT ||
                evt.getKeyCode() == KeyEvent.VK_ENTER)) {
                try {
                    S3 = txtComboItemName.getText();

                    ResultSet rst = DB.search("select '"+col+"' from stud where '"+col+"' like '" + S3 + "%'");
                    System.out.println("col:"+ col);
                    boolean b = rst.next();

                    boolean bb = false;
                    if (b) {
                        search_txt.removeAllItems();
                        bb = true;
                    }
                    while (b) {
                        if (rst.getString(col).startsWith(S3)) {
                            search_txt.addItem(rst.getString(1));
                        }

                        b = rst.next();
                    }

                    search_txt.setSelectedItem(S3);
                    txtComboItemName.setCaretPosition((search_txt.getSelectedItem() + "").length());
                    search_txt.showPopup();
                    int i = search_txt.getItemCount();

                    if (i > search_txt.getMaximumRowCount()) {
                        search_txt.setMaximumRowCount(1000);
                    } else {
                        search_txt.setMaximumRowCount(i);
                    }
                    bbb = true;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

            } else if (
                evt.getKeyCode() == KeyEvent.VK_ENTER && 
                bbb == true && evt.getKeyCode() == KeyEvent.VK_BACK_SPACE) {

                boolean bIT = false;

                String Sr123 = (String) search_txt.getSelectedItem();
                try {
                    ResultSet Rst23 = DB.search("select '"+search_fields.getSelectedItem().toString()+"' from stud");

                    while (Rst23.next()) {
                        if (Sr123.equals(Rst23.getString(search_fields.getSelectedItem().toString()))) {
                            bIT = true;
                            break;
                        } else {
                            bIT = false;
                        }
                    }
                    bbb = false;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    });
}

well instead of returning the FirstName of a Student it gives me the name of the column:"FName" — Nevindu Batagoda
– Nevindu Batagoda, Commented Jun 13, 2014 at 5:24
Try not quoting the column name DB.search("select "+col+" from stud where "+col+" like '" + S3 + "%'") for example... — MadProgrammer
– MadProgrammer, Commented Jun 13, 2014 at 5:25

user2864740 · Accepted Answer · 2014-06-13 05:42:04Z

At least one problem is the query generated will be as:

select 'COL' from stud where 'COL' like ..

When it should look like

select COL from stud where COL like ..
-- or whatever is appropriate for the database (also note selecting into
-- a well-known column in this second case)
select [COL] as result from stud where [COL] like ..

That is, the column names are incorrectly quoted as strings, and not used as identifiers in SQL.

There are other issues, SQL Injection - as the value supplied to LIKE should be bound by a placeholder, and an over complexity of code, and possibly more.

Consider these additional notes:

List<String> allowedNames = Arrays.asList<String>("FName", ..);

// Ensures the name is valid, or throws an Exception;
// it could also return a normalized name or a boolean, but an
// Exception is the quickest way to ensure "fail fast".
private void assertSearchableColumn(string colName) {
    if (!allowedNames.contains(colName)) {
        throw new RuntimeException("Invalid column");
    }
}

// Then before a particular column is replaced in the SQL command, but there
// is no need to have function that merely sets the global variable.
String col = search_fields.getSelectedItem().toString();
assertSearchableColumn(col);

// Only replace columns, note that the columns are *not* quoted as strings
// in the resulting SQL, and that ? represents "a placeholder".
String sql = String.format("select %s from stud where %s like ?", col, col);

// And then bind the SQL with the appropriate value to use with LIKE.
// (I have no idea what "DB" is or how/if it supports placeholders, however..
//  but if it does not already, it *should* support placeholders
//  or else it is too easy for SQL Injection, accidental or otherwise.)

Collectives™ on Stack Overflow

SQL Like Query Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related