• Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

SQL Operations in Java

Joined
Jun 30, 2008
Messages
1,145 (0.20/day)
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
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

Moderato®™
Staff member
Joined
Apr 16, 2009
Messages
8,193 (1.50/day)
Location
Charleston, SC
System Name Tower of Power / Sechs
Processor i7 14700K / i7 5820k @ 4.5ghz
Motherboard ASUS ROG Strix Z690-A Gaming WiFi D4 / X99S GAMING 7
Cooling CM MasterLiquid ML360 Mirror ARGB Close-Loop AIO / CORSAIR Hydro Series H100i Extreme
Memory CORSAIR Vengeance LPX 32GB (2 x 16GB) DDR4 3600 / G.Skill DDR4 2800 16GB 4x4GB
Video Card(s) ASUS TUF Gaming GeForce RTX 4070 Ti / ASUS TUF Gaming GeForce RTX 3070 V2 OC Edition
Storage 4x Samsung 980 Pro 1TB M.2, 2x Crucial 1TB SSD / Samsung 870 PRO 500GB M.2
Display(s) Samsung 32" Odyssy G5 Gaming 144hz 1440p, ViewSonic 32" 72hz 1440p / 2x ViewSonic 32" 72hz 1440p
Case Phantek "400A" / Phanteks “Enthoo Pro series”
Audio Device(s) Realtek ALC4080 / Azalia Realtek ALC1150
Power Supply Corsair RM Series RM750 / Corsair CXM CX600M
Mouse Glorious Gaming Model D Wireless / Razer DeathAdder Chroma
Keyboard Glorious GMMK with box-white switches / Keychron K6 pro with blue swithes
VR HMD Quest 3 (128gb) + Rift S + HTC Vive + DK1
Software Windows 11 Pro x64 / Windows 10 Pro x64
Benchmark Scores Yes
Have you loaded the JDBC driver, and successfully connected to your database?
 
Joined
Feb 26, 2008
Messages
4,876 (0.83/day)
Location
Joplin, Mo
System Name Ultrabeast GX2
Processor Intel Core 2 Duo E8500 @ 4.0GHZ 24/7
Motherboard Gigabit P35-DS3L
Cooling Rosewill RX24, Dual Slot Vid, Fan control
Memory 2x1gb 1066mhz@850MHZ DDR2
Video Card(s) 9800GX2 @ 690/1040
Storage 750/250/250/200 all WD 7200
Display(s) 24" DCLCD 2ms 1200p
Case Apevia
Audio Device(s) 7.1 Digital on-board, 5.1 digital hooked up
Power Supply 700W RAIDMAXXX SLI
Software winXP Pro
Benchmark Scores 17749 3DM06
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

Moderato®™
Staff member
Joined
Apr 16, 2009
Messages
8,193 (1.50/day)
Location
Charleston, SC
System Name Tower of Power / Sechs
Processor i7 14700K / i7 5820k @ 4.5ghz
Motherboard ASUS ROG Strix Z690-A Gaming WiFi D4 / X99S GAMING 7
Cooling CM MasterLiquid ML360 Mirror ARGB Close-Loop AIO / CORSAIR Hydro Series H100i Extreme
Memory CORSAIR Vengeance LPX 32GB (2 x 16GB) DDR4 3600 / G.Skill DDR4 2800 16GB 4x4GB
Video Card(s) ASUS TUF Gaming GeForce RTX 4070 Ti / ASUS TUF Gaming GeForce RTX 3070 V2 OC Edition
Storage 4x Samsung 980 Pro 1TB M.2, 2x Crucial 1TB SSD / Samsung 870 PRO 500GB M.2
Display(s) Samsung 32" Odyssy G5 Gaming 144hz 1440p, ViewSonic 32" 72hz 1440p / 2x ViewSonic 32" 72hz 1440p
Case Phantek "400A" / Phanteks “Enthoo Pro series”
Audio Device(s) Realtek ALC4080 / Azalia Realtek ALC1150
Power Supply Corsair RM Series RM750 / Corsair CXM CX600M
Mouse Glorious Gaming Model D Wireless / Razer DeathAdder Chroma
Keyboard Glorious GMMK with box-white switches / Keychron K6 pro with blue swithes
VR HMD Quest 3 (128gb) + Rift S + HTC Vive + DK1
Software Windows 11 Pro x64 / Windows 10 Pro x64
Benchmark Scores Yes
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:

Code:
"[color=blue]SELECT[/color] * [color=blue]FROM[/color] UserTable [color=blue]WHERE[/color] userID = [color=red]'[/color]" + UserInput + "[color=red]'[/color];"
 
Last edited:

Aquinus

Resident Wat-man
Joined
Jan 28, 2012
Messages
13,147 (2.96/day)
Location
Concord, NH, USA
System Name Apollo
Processor Intel Core i9 9880H
Motherboard Some proprietary Apple thing.
Memory 64GB DDR4-2667
Video Card(s) AMD Radeon Pro 5600M, 8GB HBM2
Storage 1TB Apple NVMe, 4TB External
Display(s) Laptop @ 3072x1920 + 2x LG 5k Ultrafine TB3 displays
Case MacBook Pro (16", 2019)
Audio Device(s) AirPods Pro, Sennheiser HD 380s w/ FIIO Alpen 2, or Logitech 2.1 Speakers
Power Supply 96w Power Adapter
Mouse Logitech MX Master 3
Keyboard Logitech G915, GL Clicky
Software MacOS 12.1
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.
 
Joined
Jan 2, 2012
Messages
1,079 (0.24/day)
Location
Indonesia
Processor AMD Ryzen 7 5700X
Motherboard ASUS STRIX X570-E
Cooling NOCTUA NH-U12A
Memory G.Skill FlareX 32 GB (4 x 8 GB) DDR4-3200
Video Card(s) ASUS RTX 4070 DUAL
Storage 1 TB WD Black SN850X | 2 TB WD Blue SN570 | 10 TB WD Purple Pro
Display(s) LG 32QP880N 32"
Case Fractal Design Define R5 Black
Power Supply Seasonic Focus Gold 750W
Mouse Pulsar X2
Keyboard KIRA EXS
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:
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

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.
 
Joined
Jun 30, 2008
Messages
1,145 (0.20/day)
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
Thanks for the responses. Wasn't expecting so many to reply :laugh:

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.

Code:
 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:
Code:
java.sql.SQLException: Can not issue SELECT via executeUpdate()

Edit: Just noticed .executeUpdate only allows for INSERT, DELETE, and UPDATE.
 
Last edited:
Joined
Aug 10, 2007
Messages
4,267 (0.70/day)
Location
Sanford, FL, USA
Processor Intel i5-6600
Motherboard ASRock H170M-ITX
Cooling Cooler Master Geminii S524
Memory G.Skill DDR4-2133 16GB (8GB x 2)
Video Card(s) Gigabyte R9-380X 4GB
Storage Samsung 950 EVO 250GB (mSATA)
Display(s) LG 29UM69G-B 2560x1080 IPS
Case Lian Li PC-Q25
Audio Device(s) Realtek ALC892
Power Supply Seasonic SS-460FL2
Mouse Logitech G700s
Keyboard Logitech G110
Software Windows 10 Pro
Important is that you clean those input strings.

Code:
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).
 
Joined
Jan 2, 2012
Messages
1,079 (0.24/day)
Location
Indonesia
Processor AMD Ryzen 7 5700X
Motherboard ASUS STRIX X570-E
Cooling NOCTUA NH-U12A
Memory G.Skill FlareX 32 GB (4 x 8 GB) DDR4-3200
Video Card(s) ASUS RTX 4070 DUAL
Storage 1 TB WD Black SN850X | 2 TB WD Blue SN570 | 10 TB WD Purple Pro
Display(s) LG 32QP880N 32"
Case Fractal Design Define R5 Black
Power Supply Seasonic Focus Gold 750W
Mouse Pulsar X2
Keyboard KIRA EXS
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.
 
Joined
Jun 30, 2008
Messages
1,145 (0.20/day)
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
Important is that you clean those input strings.

Code:
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!
:toast:
 
Last edited:
Joined
Aug 10, 2007
Messages
4,267 (0.70/day)
Location
Sanford, FL, USA
Processor Intel i5-6600
Motherboard ASRock H170M-ITX
Cooling Cooler Master Geminii S524
Memory G.Skill DDR4-2133 16GB (8GB x 2)
Video Card(s) Gigabyte R9-380X 4GB
Storage Samsung 950 EVO 250GB (mSATA)
Display(s) LG 29UM69G-B 2560x1080 IPS
Case Lian Li PC-Q25
Audio Device(s) Realtek ALC892
Power Supply Seasonic SS-460FL2
Mouse Logitech G700s
Keyboard Logitech G110
Software Windows 10 Pro
Last edited:
Joined
Jun 30, 2008
Messages
1,145 (0.20/day)
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
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()

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

lol @ the cartoon strip. :laugh:
 
Top