Search filter for Jtable

Question

I have my set Appointments Frame for doctors and inside it have Patient Records and of course the doctor and then i have a search field so whenever i'm finding some doctors i will just have to search their name, there's is no error but the problem is whenever the search field is empty the jtable is filled all of the User including the admin Nurse recep etc.

and this is my code

try{
        String like = DoctorSearch.getText();
        String sql = "Select ID,First_Name,[M.I],Last_Name from User_Table\n" +
                     "where Role_ID = 3 AND Last_Name LIKE '"+like+"%' or First_Name LIKE '"+like+"%'";
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        DoctorNames.setModel(DbUtils.resultSetToTableModel(rs));
    }catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    }finally {
        try {
            rs.close();
            pst.close();
        }catch(Exception e){

        }
    }

But when i remove the

First_Name LIKE '"+like+"%'

it is doing right what can i do to make it right?

only the Last name of the Doctor i know tomorrow my defense the panel will ask if they want to search first name like? i hope you can help me — Sieccc, Oct 14 '18 at 14:44

Tim · Answer 1 · 2018-10-14T18:53:59.440

This is all to do with the precedence of operators. Let's say that you're searching for "James" The actual SQL code you're trying to execute is this:

Select ID,First_Name,[M.I],Last_Name from User_Table
where Role_ID = 3 AND Last_Name LIKE 'James'
or First_Name LIKE 'James'

Now because of the precedence of SQL operators, the WHERE section gets treated as:

WHERE (Role_ID = 3 AND Last_Name LIKE 'James')
OR (First_Name LIKE 'James')

So you'll get all the doctors whose last names are 'James', and everyone (including nurses) whose first names are 'James'. And when your search field is empty, you'll get everyone whose first names are LIKE '', which is everyone.

In order to get the Role_ID = 3 bit to apply in both cases, you simply need brackets, like this:

Select ID,First_Name,[M.I],Last_Name from User_Table
where Role_ID = 3 AND (Last_Name LIKE 'James'
or First_Name LIKE 'James')

So your code will look like this:

    String sql = "Select ID,First_Name,[M.I],Last_Name from User_Table\n" +
                 "where Role_ID = 3 AND ( Last_Name LIKE '"+like+"%' or First_Name LIKE '"+like+"%')";

However...

You're also doing something really bad here. You're setting yourself up for what's called an SQL injection attack. Have a read up on this, and how you should provide a ? in the SQL where you want your value, and then substitute it in with something like setString() after you have called conn.prepareStatement(sql). This is very, very important.

Overall, you'll probably end up with something like:

    String sql = "Select ID,First_Name,[M.I],Last_Name from User_Table\n" +
                 "where Role_ID = 3 AND (Last_Name LIKE ? or First_Name LIKE ?)";
    pst = conn.prepareStatement(sql);
    pst.setString(1, like);
    pst.setString(2, like);
    rs = pst.executeQuery();

Search filter for Jtable

1 Answers1

However...