1

I have the following connection, statement and executeUpdate

Connection con = DBConnPool.getInstance().getConnection();
Statement stmt = con.createStatement();

//String str1 = "update node set compareflag=0, personalid=NULL where ipaddress='192.168.150.213'";

String str1 = "update node set compareflag=0, personalid=NULL where ipaddress='var3.getIpAddress()'";
                            stmt.executeUpdate(str1);

The commented out String line works perfectly, the other one ignores the value returned by var3.getIpAddress() even though that variable does contain the correct data which I use in other areas of my code.

Do I have to create a separate variable first and then equate it to var3.getIpAddress() ?

Any thoughts appreciated, it's probably insufficient " or " in the wrong place.

Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249
Ralph
  • 115
  • 1
  • 13
  • Start [here](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). You can't invoke Java methods from a portion of `String` literal. – Mena Oct 08 '18 at 15:50

2 Answers2

3

You should use PreparedStatement to set parameter for safe.

PreparedStatement pstmt = con.prepareStatement("update node set compareflag=0, personalid=NULL where ipaddress=?");
pstmt.setString(1,var3.getIpAddress());
pstmt.executeUpdate();
Huy Nguyen
  • 1,931
  • 1
  • 11
  • 11
  • OK but you may consider using variables also for the other columns being updated. – Robert Kock Oct 08 '18 at 16:00
  • The last line is incorrect. `pstmt.executeUpdate(str1);` invokes the method from [`Statement.executeUpdate(String)`](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeUpdate-java.lang.String-) (where `str1` is a query and the previously bound query and parameter are ignored). – Elliott Frisch Oct 08 '18 at 16:02
  • @ElliottFrisch Even stronger `executeUpdate(String)` on a `PreparedStatement` should always throw a `SQLException`, that is required by the JDBC specification. – Mark Rotteveel Oct 08 '18 at 17:59
3

Prefer a PreparedStatement with a bind parameter. Dynamically building a query leaves you vulnerable to SQL Injection attacks. PreparedStatement (when used correctly) is immune to SQL Injection. It also makes the code easier to read and reason about. For example,

Connection con = DBConnPool.getInstance().getConnection();
String qry = "update node set compareflag=0, personalid=NULL where ipaddress=?";
PreparedStatement stmt = con.prepareStatement(qry);
stmt.setString(1, var3.getIpAddress());
stmt.executeUpdate();
Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249