# SQL Operations in Java



## Wozzer (Dec 20, 2012)

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 (Dec 20, 2012)

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


----------



## 3870x2 (Dec 20, 2012)

Mindweaver said:


> 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 (Dec 20, 2012)

3870x2 said:


> 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:


```
"[color=blue]SELECT[/color] * [color=blue]FROM[/color] UserTable [color=blue]WHERE[/color] userID = [color=red]'[/color]" + UserInput + "[color=red]'[/color];"
```


----------



## Aquinus (Dec 20, 2012)

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 (Dec 20, 2012)

Wozzer said:


> 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 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 :


```
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



Aquinus said:


> 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 (Dec 20, 2012)

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.


----------



## Disparia (Dec 20, 2012)

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 (Dec 20, 2012)

Wozzer said:


> 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 (Dec 20, 2012)

Jizzler said:


> Important is that you clean those input strings.
> 
> 
> ```
> ...



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!


----------



## Disparia (Dec 20, 2012)

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/docs/api/java/sql/PreparedStatement.html#execute()


----------



## Wozzer (Dec 20, 2012)

Jizzler said:


> 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.
> 
> ...



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

lol @ the cartoon strip.


----------

