aspose file tools*
The moose likes JDBC and the fly likes sql INSERT using variables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql INSERT using variables" Watch "sql INSERT using variables" New topic
Author

sql INSERT using variables

Fred Hamilton
Ranch Hand

Joined: May 13, 2009
Posts: 679
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Fred Hamilton
Ranch Hand

Joined: May 13, 2009
Posts: 679
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', 'fred@abc.com');

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


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

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
James Gregory
Greenhorn

Joined: Jun 17, 2009
Posts: 2
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
Ranch Hand

Joined: May 13, 2009
Posts: 679
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql INSERT using variables