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, Jan 09 '18 at 13:48

Youcef LAIDANI · Accepted Answer · 2021-06-03T15:16:29.470

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 03 '21 at 15:16

answered Jan 09 '18 at 13:50

Youcef LAIDANI

55,661
15
90
140

There's a small syntax error on line 2, the colon shouldn't be there on the try with resources. Thanks for the answer :) – joshpetit Jun 03 '21 at 11:21
Thank you for the comment @joshpetit, what did you mean I don't get you. – Youcef LAIDANI Jun 03 '21 at 11:34
there's a *semi-colon here: `try (PreparedStatement pst = con.prepareStatement(insertStr);)` after insertStr. – joshpetit Jun 03 '21 at 13:45
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? – joshpetit Jun 03 '21 at 13:45
@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);` – Youcef LAIDANI Jun 03 '21 at 14:39
ok I see that, I was wondering if it would be possible to do that but without calling it multiple times, but I can just do a loop, all works the same! – joshpetit Jun 04 '21 at 02:23

score 0 · Answer 2 · answered Jan 09 '18 at 13:49

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 09 '18 at 13:49

maio290

6,440
1
21
38

1

You shouldn't use variable directly in a SQL String as this as this is vulnerable to DB Injection attack. – Travis Stevens Jun 21 '20 at 16:48
@TravisStevens That's why I recommended prepared statements... – maio290 Jun 22 '20 at 07:35

score 0 · Answer 3 · answered Jan 09 '18 at 13:59

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 ;)

How to set variables into a JDBC statement?

3 Answers3