CODEWITHSUNDEEP
javasql
Svenmarim
Svenmarim

Reputation: 3735

SQL query sees the value as a column name

I am trying to make a connection to a database and then run an INSERT INTO query, but when the code runs, i get the error: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'BLUE'.

As you can see in my code below, i give "BLUE" as an value instead of an column name. Does anyone knows what i am doing wrong? p.s. color is an Enum, all the other values are doubles.

String query = "INSERT INTO [oval] " +
               "(anchorX, anchorY, width, height, weight, color) VALUES " +
               "(" + drawingItem.getAnchor().getX() +
               ", " + drawingItem.getAnchor().getY() +
               ", " + drawingItem.getWidth() +
               ", " + drawingItem.getHeight() +
               ", " + ((Oval) drawingItem).getWeight() +
               ", " + drawingItem.getColor().toString() + ")";

initConnection();
Statement myStmt = con.createStatement();
rowsAffected = myStmt.executeUpdate(query);
closeConnection();

EDIT ANSWER:

String query = "INSERT INTO [oval] VALUES (?,?,?,?,?,?)";

initConnection();
PreparedStatement myPrepStmt = con.prepareStatement(query);
myPrepStmt.setDouble(1, drawingItem.getAnchor().getX());
myPrepStmt.setDouble(2, drawingItem.getAnchor().getY());
myPrepStmt.setDouble(3, drawingItem.getWidth());
myPrepStmt.setDouble(4, drawingItem.getHeight());
myPrepStmt.setDouble(5, ((Oval)drawingItem).getWeight());
myPrepStmt.setString(6, drawingItem.getColor().toString());
rowsAffected = myPrepStmt.executeUpdate();
closeConnection();

Upvotes: 1

Views: 337

Answers (2)

Alexey Soshin
Alexey Soshin

Reputation: 17721

Correct way would be:

String query = "INSERT INTO [oval] " +
               "(anchorX, anchorY, width, height, weight, color) VALUES " +
               "(?, ?, ?, ?, ?, ?)";

initConnection();
int i = 1;
Statement myStmt = con.prepareStatement(query);
myStmt.setInt(i++, drawingItem.getAnchor().getX());
myStmt.setInt(i++, drawingItem.getAnchor().getY());
myStmt.setString(i++, drawingItem.getWidth());
myStmt.setString(i++, drawingItem.getHeight());
myStmt.setFloat(i++, ((Oval) drawingItem).getWeight());
myStmt.setString(i++, drawingItem.getColor().toString());
rowsAffected = myStmt.executeUpdate();

Upvotes: 1

JFPicard
JFPicard

Reputation: 5168

As suggested, use parametrized query to prevent SQL injection. As for the problem in hand, you must use single quote to each string values.

Ex:

"('" + drawingItem.getAnchor().getX() +
"', '" +

Upvotes: 4

Related Questions