Inserting variables with SQL in Java

Question

I'm writing a webpage that takes input from a form, sends it through cgi to a java file, inserts the input into a database through sql and then prints out the database. I'm having trouble inserting into the database using variables though, and I was wondering if anyone would be able to help me out.

String a1Insert = (String)form.get("a1");
 String a2Insert = (String)form.get("a2");

This is where I get my variables form the form (just believe that it works, there's a bunch more back end but I've used this before and I know it's getting the variables correctly).

 String dbURL = "jdbc:derby://blah.blahblah.ca:CSE2014;user=blah;password=blarg";
  Connection conn = DriverManager.getConnection(dbURL);
  Statement stmt = conn.createStatement();
  stmt.executeUpdate("set schema course");
 stmt.executeUpdate("INSERT INTO MEMBER VALUES (a1Insert, a2Insert)"); 
 stmt.close();

This is where I try to insert into the databse. It give me the error:

Column 'A1INSERT' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A1INSERT' is not a column in the target table.

If anyone has any ideas that would be lovely ^.^ Thanks

There is good information for you at docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html and at mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record — Dawood ibn Kareem
– Dawood ibn Kareem, Commented Apr 7, 2014 at 20:33
If you get the syntax right -- stmt.executeUpdate("INSERT INTO MEMBER VALUES ('" + a1Insert + "', '" + a2Insert + "')"); -- then it will technically work, but would leave you open to SQL injection attacks if somebody can manipulate a1Insert/s2Insert and set them to malicious values. So instead, you should follow the method wit PreparedStatement that Nathan mentions. — Neil Coffey
– Neil Coffey, Commented Apr 8, 2014 at 14:06
@NeilCoffey except that the correct SQL specifies the column names in the insert statement. So would need to be ... into member (colname1, colname2) values (value1, value2) — jwenting
– jwenting, Commented Apr 8, 2014 at 14:52
@jwenting In principle you can leave out the column names if the values match up. — Neil Coffey
– Neil Coffey, Commented Apr 9, 2014 at 1:14
@NeilCoffey and there can be no confusion as to the column order you implied... I've had databases give errors leaving them out on inserting all columns with ambiguous column order (e.g. a table with 2 columns, both varchar). — jwenting
– jwenting, Commented Apr 9, 2014 at 6:39

Nathan Hughes · Accepted Answer · 2014-04-10 13:25:37Z

java.sql.Statement doesn't support parameters, switching to java.sql.PreparedStatement will allow you to set parameters. Replace the parameter names in your SQL with ?, and call the setter methods on the prepared statement to assign a value to each parameter. This will look something like

String sql = "INSERT INTO MEMBER VALUES (?, ?)";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, "a1");
stmt.setString(2, "a2");
stmt.executeUpdate();

That will execute the SQL

INSERT INTO MEMBER VALUES ('a1', 'a2')

Notice the parameter indexes start from 1, not 0. Also notice I didn't have to put quotes on the strings, the PreparedStatement did it for me.

Alternatively you could keep using Statement and create your SQL string in Java code, but that introduces the possibility of SQL injection attacks. Using PreparedStatement to set parameters avoids that issue by taking care of handling quotes for you; if it finds a quote in the parameter value it will escape it, so that it will not affect the SQL statement it is included in.

Oracle has a tutorial here.

Collectives™ on Stack Overflow

Inserting variables with SQL in Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related