Michael Quinn Michael Quinn - 6 months ago 19
SQL Question

SQL Login Verification - Checking user Type

The Program below currently checks if the users login details are correct. It then goes into the next section which checks if the user is an admin or not. Currently I am trying to work this out by all normal users are listed in my database as 'user_admin' [null] whilst admins will be listed as 1 in the database. I've looked around and no one seems to of asked something related to this in the method that I am doing it.

If you have a look in my code you can see that a normal user should be pointed towards the UserPanel whilst the Admin should be pointed towards the AdminPanel. This is due to my program is set so people can view there accounts whilst Admins can edit the accounts.

This is for a collage project I'm working on hence the simplacity of the program.

JLabel lblNewLabel = new JLabel("");
lblNewLabel.setIcon(new ImageIcon(PanelLogin.class.getResource("/image/Login.png")));
lblNewLabel.setBounds(118, 115, 100, 26);
lblNewLabel.addMouseListener(new MouseAdapter() {
@Override
public void mouseEntered(MouseEvent e) {
lblNewLabel.setIcon(new ImageIcon(PanelLogin.class.getResource("/image/Loginv2.png")));
}
@Override
public void mouseExited(MouseEvent e) {
lblNewLabel.setIcon(new ImageIcon(PanelLogin.class.getResource("/image/Login.png")));
}
@Override
public void mouseClicked(MouseEvent e) {
if (LoginAttempts < 3 ) {
try {
String Host = "removed";
String Name = "removed";
String Pass = "removed";

Connection conn = DriverManager.getConnection( Host, Name, Pass );
PreparedStatement pst = conn.prepareStatement("SELECT `user_name`, `user_pass` FROM `table_1` WHERE `user_name` = ? AND `user_pass` = ?");
pst.setString(1, textID.getText());
pst.setString(2, String.valueOf(passwordField.getPassword()));
ResultSet Result = pst.executeQuery();
if (Result.next()) {
String user =Result.getString("user_name");
try {
PreparedStatement pst2 = conn.prepareStatement("SELECT `user_admin` FROM `table_1` WHERE `user_name` = ?");
pst2.setString(1, user);
if (Result.next()) {
frmLotusLogin.dispose();
new UserPanel(user).frame.setVisible(true);
}
else {
frmLotusLogin.dispose();
new AdminPanelMain(user).frmLotusSecurity.setVisible(true);
}

}
catch (Exception exc){

}
}
else {
JOptionPane.showMessageDialog(null, "Incorrect Username/Password");
LoginAttempts = LoginAttempts + 1;
}
}
catch (Exception ex) {
System.out.println(ex);
JOptionPane.showMessageDialog(null, "An error occurred. Your Username/Password could be incorrect, "
+ "If error contiues to appear please contact support! Error Number: L1");
}
}
else {
JOptionPane.showMessageDialog(null, "You Have Entered The Wrong Password Too Many Times, You are now locked out!");
}
}
});


Thanks in advance to anyone who spots what I've done wrong or need to change in order for it to correctly verify. If anyone requires any further information as to what is going on please do not hesitate to ask.

After First suggestion

try {
String Host = "jdbc:mysql://sql8.freemysqlhosting.net:3306/sql8114921";
String Name = "sql8114921";
String Pass = "PPQrXGMRy8";

Connection conn = DriverManager.getConnection( Host, Name, Pass );
PreparedStatement pst = conn.prepareStatement("SELECT `user_name`, `user_pass` FROM `table_1` WHERE `user_name` = ? AND `user_pass` = ?");
pst.setString(1, textID.getText());
pst.setString(2, String.valueOf(passwordField.getPassword()));
ResultSet Result = pst.executeQuery();
if (Result.next()) {
String user = Result.getString("user_name");
try {
PreparedStatement pst2 = conn.prepareStatement("SELECT `user_admin` FROM `table_1` WHERE `user_name` = ?");
pst2.setString(1, user);
ResultSet Result2 = pst2.executeQuery(); // added
if (Result2.next()) { // modified
System.out.println("Test");
frmLotusLogin.dispose();
new UserPanel(user).frame.setVisible(true);
} else {
frmLotusLogin.dispose();
new AdminPanelMain(user).frmLotusSecurity.setVisible(true);
}

} catch (Exception exc) {

// do something here !

}
}
else {
JOptionPane.showMessageDialog(null, "Incorrect Username/Password");
LoginAttempts = LoginAttempts + 1;
}
}
catch (Exception ex) {
System.out.println(ex);
JOptionPane.showMessageDialog(null, "An error occurred. Your Username/Password could be incorrect, "
+ "If error contiues to appear please contact support! Error Number: L1");
}
}
else {
JOptionPane.showMessageDialog(null, "You Have Entered The Wrong Password Too Many Times, You are now locked out!");
}
}
});


Here is the view of the table I'm using

This is what my table currently looks like you can see what I'm doing to state if a user is an admin or not

Answer

You never execute the second statement, and of course neither use its result (because you still use the first resultset instead).

Also, never ignore exceptions when you catch them, unless you are sure that it isn't important at all.

Finally, you should close the statements/connections when you are done with them (I didn't add this part to the below code).

As a side note, you may probably avoid using multiple queries, by using JOIN clause on a single one : java jdbc accessing multiple resultsets

        ResultSet Result = pst.executeQuery();
        if (Result.next()) {
            String user = Result.getString("user_name");
            try {
                PreparedStatement pst2 = conn
                        .prepareStatement("SELECT `user_admin` FROM `table_1` WHERE `user_name` = ? AND `user_admin` = 1");

                pst2.setString(1, user);
                ResultSet Result2 = pst2.executeQuery();  // added
                if (Result2.next()) { // modified
                    frmLotusLogin.dispose();
                    new AdminPanel(user).frame.setVisible(true);
                } else {
                    frmLotusLogin.dispose();
                    new MainUserPanel(user).frmLotusSecurity.setVisible(true);
                }

            } catch (Exception exc) {

              // do something here !

            }