How to set variables into a JDBC statement?

Question

It works when i try to insert variables for example :

String insertStr="INSERT INTO  table1(username1,password1) VALUES(\"john\",\"password\")";

but unable to insert using variable

String a=username.getText();
String b=password.getText();

try {
    Class.forName("com.mysql.jdbc.Driver");  
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/java_db1","root","");  

    Statement stmt=con.createStatement();        
    String insertStr="INSERT INTO  table1(username1,password1) VALUES(a,b);";
    stmt.executeUpdate(insertStr);
} catch (Exception e) { }

String insertStr="INSERT INTO table1(username1,password1) VALUES("+strA+","+strB+");"; — mBogaz
– mBogaz, Commented Jan 9, 2018 at 13:48

Youcef LAIDANI · Accepted Answer · 2021-06-03 15:16:29Z

3

Use [PreparedStatement][1] instead of your way, because your way can be a victim of SQL Injection or Syntax errors :

String insertStr = "INSERT INTO  table1(username1,password1) VALUES(?, ?)";
try (PreparedStatement pst = con.prepareStatement(insertStr)) {
    pst.setString(1, a);
    pst.setString(2, b);
    pst.executeUpdate();
}

For reason of security I don't suggest to get password with getText(), instead use getPassword(), so you can use :

pst.setString(1, username.getText());
pst.setString(2, new String(passwordField.getPassword()));

Take a look at this :

edited Jun 3, 2021 at 15:16

answered Jan 9, 2018 at 13:50

Youcef LAIDANI

60.2k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

joshpetit Over a year ago

There's a small syntax error on line 2, the colon shouldn't be there on the try with resources. Thanks for the answer :)

Youcef LAIDANI Over a year ago

Thank you for the comment @joshpetit, what did you mean I don't get you.

joshpetit Over a year ago

there's a *semi-colon here: try (PreparedStatement pst = con.prepareStatement(insertStr);) after insertStr.

joshpetit Over a year ago

Also this is likely a separate post, but is there a way to use the same variable for multiple fields? I.e use the same variable for both question marks in the prepared statement?

Youcef LAIDANI Over a year ago

@joshpetit Yes it is unnecessary semicolon, you can edit my question to let your trace behind :) and about your question, yes you can use pst.setString(1, sameThing); pst.setString(2, sameThing);

|

maio290 · Accepted Answer · 2018-01-09 13:49:00Z

0

Since you are inserting "a" and "b" as String, not their variable values.

String insertStr="INSERT INTO  table1(username1,password1) VALUES("+a+","+b+");";

should do it, but I would recommend to use a prepared statement here: https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

answered Jan 9, 2018 at 13:49

maio290

6,8081 gold badge27 silver badges41 bronze badges

2 Comments

Travis Stevens Over a year ago

You shouldn't use variable directly in a SQL String as this as this is vulnerable to DB Injection attack.

maio290 Over a year ago

@TravisStevens That's why I recommended prepared statements...

Tim Weber · Accepted Answer · 2018-01-09 13:59:14Z

The most common way to insert variable values into sql is to use the PreparedStatement Object

With this object, you can add variable values into a SQL Query without fearing of SQL injection. Here an example of PreparedStatement :

//[Connection initialized before]
String insertStr="INSERT INTO  table1(username1,password1) VALUES(?,?);";
PreparedStatement myInsert = myConnectionVariable.prepareStatement(insertStr); // Your db will prepare this sql query
myInsert.setString(1, a); //depending on type you want to insert , you have to specify the position of your argument inserted (starting at 1)
myInsert.setString(2, b); // Here we set the 2nd '?' with your String b
myInsert.executeUpdate(); // It will returns the number of affected row (Works for UPDATE,INSERT,DELETE,etc...)
//You can use executeQuery() function of PreparedStatement for your SELECT queries

This is safer than using String concatenation like this : VALUES("+a+","+b+");

Take a look at Java Doc for more information ;)

Collectives™ on Stack Overflow

How to set variables into a JDBC statement?

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related