SQL Operations in Java

[Wozzer]

Hi all,

I'm trying to create an if statement that:
"When a user input equals ANY of the the data in column "userID", Then execute"

I'm aware you can use the .equals operation but not sure how you would link it to the column within the database.

Wozzer

[Mindweaver]

Have you loaded the JDBC driver, and successfully connected to your database?

[3870x2]

Originally Posted by Mindweaver

Have you loaded the JDBC driver, and successfully connected to your database?

It seems like something he would have already done if he is at this point.

If you haven't, this would be the place to start.

[Mindweaver]

Originally Posted by 3870x2

It seems like something he would have already done if he is at this point.

If you haven't, this would be the place to start.

It would seem, but I'd like to know for sure.

If it's just the Select statement then something like this:

"SELECT * FROM UserTable WHERE userID = '" + UserInput + "';"

[Aquinus]

Instead of telling how how you're trying to solve the problem, maybe you should describe what you're doing (for the project,) and where you're at so we can have an idea about what you're trying to do rather than how you're trying to approach it.

[okidna]

Originally Posted by Wozzer

Hi all,

I'm trying to create an if statement that:
"When a user input equals ANY of the the data in column "userID", Then execute"

I'm aware you can use the .equals operation but not sure how you would link it to the column within the database.

Wozzer

I only understand a little bit Java, so pardon me If I'm wrong, userInput is the user input (string variable), users is your table name, and I assume you already have your connection to your database :

PHP Code:

SQLConnection.setURL("insert_connection_string_here");
con=SQLConnection.getNewConnection();
Statement stmt = con.createStatement();

// execute your SQL statement here, for example :
String sqlCommand = "SELECT userID FROM users WHERE userID='"+userInput+"'";  
ResultSet rs = stmt.executeQuery(sqlCommand);

bool hasRows = false;
while(rs.next()){
  hasRows = true;
  // put the codes that you want to execute here
}

if(!hasRows)
{
  // do stuff when no rows/match present.
} 


Please CMIIW.

And

Originally Posted by Aquinus

Instead of telling how how you're trying to solve the problem, maybe you should describe what you're doing (for the project,) and where you're at so we can have an idea about what you're trying to do rather than how you're trying to approach it.

+1.

[Wozzer]

Thanks for the responses. Wasn't expecting so many to reply

To clarify - I have a java program. I only want users to access the software if they are registered.

I've set up a database (and connected to it!). I want to run a query which checks if the users ID and passwords ID match. If matched, permissions will be granted and the main menu will open.

 System.out.println("MySQL Connect Example.");
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "watson_users";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "";
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
            System.out.println("Connected to the database");

            Statement st = (Statement) conn.createStatement();

            String query_to_update = "SELECT * FROM `user_database` WHERE CollarID='" + collarIDField.getText() + "' AND Password='" + passwordField.getText() + "'";

            System.out.println("Query: " + query_to_update);

            int val = st.executeUpdate(query_to_update);
            System.out.println("We've got to this point");

            conn.close();
        } catch (InstantiationException | IllegalAccessException | SQLException ex) {
            Logger.getLogger(MetaDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(this, "Can't connect to Database");
        } catch (ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(this, "Can't connect to Database");
        }

Error:

java.sql.SQLException: Can not issue SELECT via executeUpdate()

Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.

[Jizzler]

Important is that you clean those input strings.

PreparedStatement selectUser = conn.prepareStatement("SELECT * FROM user_database WHERE CollarID= ? AND Password = ?");

selectUser.setString(1, collarIDField.getText());
selectUser.setString(2, passwordField.getText());

if ( selectUser.execute() ) {
	//get the data, welcome the user
} else {
	//tell them to get it right or bugger off
}

Now when I claim that my ID is "1; DROP TABLE user_database;" I'm not going to ruin your day.


(hope I got it right, only write JAVA when I have to).

[okidna]

Originally Posted by Wozzer

Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.

.executeUpdate() generally used for altering the databases. DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this.

Use .executeQuery() or .execute() if you want to use SELECT. Remember the output is in the form of ResultSet.

[Wozzer]

Originally Posted by Jizzler

Important is that you clean those input strings.

PreparedStatement selectUser = conn.prepareStatement("SELECT * FROM user_database WHERE CollarID= ? AND Password = ?");

selectUser.setString(1, collarIDField.getText());
selectUser.setString(2, passwordField.getText());

if ( selectUser.execute() ) {
	//get the data, welcome the user
} else {
	//tell them to get it right or bugger off
}

Now when I claim that my ID is "1; DROP TABLE user_database;" I'm not going to ruin your day.


(hope I got it right, only write JAVA when I have to).

Just to clarify - Is that to prevent SQL injection attacks, etc?

Thanks for all the replies. Working on it now.

Edit - Login seems to be working although I don't think it's checking the actual results as it's opening my program based on .execute
(IE - If anything is executed, do this).

How can I check the returned results from my database?
Thanks all!

[Jizzler]

Yup. http://xkcd.com/327/

Even if the likelihood of revealing information is near impossible, it's still a best practice. No matter what they put in it's either going to work else not - with the "not" being handled the same way every time. Otherwise, one may be able to cause an error that reveals something of interest.


-Edit-

excute(), getResultSet():

http://docs.oracle.com/javase/1.4.2/....html#execute()

[Wozzer]

Originally Posted by Jizzler

Yup. http://xkcd.com/327/

Even if the likelihood of revealing information is near impossible, it's still a best practice. No matter what they put in it's either going to work else not - with the "not" being handled the same way every time. Otherwise, one may be able to cause an error that reveals something of interest.


-Edit-

excute(), getResultSet():

http://docs.oracle.com/javase/1.4.2/....html#execute()

Will read up on the documentation later tonight. Many thanks Jizzler.

lol @ the cartoon strip.