sql INSERT using variables

[Fred Hamilton]

OK, I'm getting my feet wet with JDBC, and things are going well, with one or two exceptions. such as...

http://java.sun.com/products/jdbc/reference/codesamples/index.html#1

This link provides a downloadable zip file which contains many examples of java sql code. For some reason which is not yet clear to me, Sun has seen fit to make every last example of the INSERT statement work with values that are hard coded into the program.

I have poked around on the net looking for sample code, and just about every clear cut example I found has also worked with values that are hard coded into the program. ( via some kind of statement string );

I suppose this experience should tell me something. Anyways, If anyone can point me to some sample code where the INSERT statement uses variables instead of hard coded values, I would be most grateful.

It should be a no-brainer to make the transition from hard coded values to variables, but being a guy who learns well by example, I'd still like to see one.

regards.

[Jeanne Boyarsky]

Fred,
I think what it tells you is that the examples are old.

New code should use PreparedStatements (with variables) rather than Statements (with hard coding) unless there is a really good reason not to. Hard coding is vulnerable to SQL injection where a hacker can run malicious code against the database. PreparedStatements protect you against this.

The JDBC tutorial has an example.

[Fred Hamilton]

Jeanne Boyarsky wrote:Fred,
I think what it tells you is that the examples are old.

New code should use PreparedStatements (with variables) rather than Statements (with hard coding) unless there is a really good reason not to. Hard coding is vulnerable to SQL injection where a hacker can run malicious code against the database. PreparedStatements protect you against this.

The JDBC tutorial has an example.

Well, I looked through that page with a fine tooth comb, and every other page in the Sun Tutorial on JDBC, and didn't find an insert that used variables. But thanks anyways.

Actually, I'm not sure why I had a problem before, Something like the following should work, though I haven't had a chance to try yet.

instead of

String insertStr = "INSERT INTO users VALUES ('Hamilton', '[e-mail]');

the following should work, assuming I haven't made any syntax errors. Seems a strange way to have to do things though.

String name = "Hamilton";
String email = "[e-mail]";

String insertStr = "INSERT INTO users VALUES (
			+"'" +name +"', "
			+"'" +email +"')";

Anyways, this'll have to do until/unless I find something better.

[Paul Sturrock]

The page Jeanne pointed you at is worth re-reading. Not an example of an insert statement true, but there is an example there with an update statement which is pretty simmilar is it not?

[James Gregory]

From the link already given, one example is:

PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate():

Your solution using string concatenation may be vulnerable to SQL injection, depending on where it gets used.

[Fred Hamilton]

James Gregory wrote:From the link already given, one example is:

PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate():

Your solution using string concatenation may be vulnerable to SQL injection, depending on where it gets used.

Duly noted Paul and James. I'm just playing around at home, but definitely I want to learn about secure practices. I'll take a closer look at that tutorial page. It seems to make sense but right now it's not clear, but I'm sure it will become so with more effort.

I had planned on using my concatenated insertStr as follows...

Connection con = DriverManager.getConnection(url+dbName,userName,password);
Statement stmt = con.createStatement();
stmt.executeUpdate( insertStr );

anyways, this all works well enough, but if their are security concerns I guess I'll have to come up with something better, especially if this all makes the transition off of my home PC.

[Paul Sturrock]

Its worth making the effort. Security concerns aside, PreparedStatements will also remove the work necessary to escape special characters and side step data formatting issues so you will probably save yourself effort in the log run.