Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Java Prepared Statements

Posted on 2008-11-02
8
Medium Priority
?
1,085 Views
Last Modified: 2012-05-05
I'm trying to create a dynamic SQL query using Java.  I've been told that prepared statements are the way to go since normal SQL statements can be a security risk.

My understanding of prepared statements is that I can create a statement like:

String sql = "SELECT * FROM myTable T WHERE T.attribute = ?";
PreparedStatement stmt = myconnection.prepareStatement(sql);
Then I can set ? at run time using
pstmt.setString(1, myVariable);

My question is centered around what is fair game for the ? replacement.  I've only ever seen it used in the WHERE clause (or in the values for an insert or update).  For example, can I choose the table in the FROM clause or selection criteria in the SELECT at run time?

I'm trying to understand how flexible prepared statements are.  

Thanks for any comments!
0
Comment
Question by:Zodan
8 Comments
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22863338
yes..
you can set the id in the runtime by modifying the below code...or if the type is decided at runtime, you can use setObject or getObject functions of PreparedStatement...


try {
  Connection con = DriverManager.getConnection(url);
  String select = "SELECT * FROM customer_tbl WHERE customer_id = ?";
  PreparedStatement ps = con.prepareStatement(select);
  ResultSet rs = null;
  for(int i=0;i<10;i++) {
//  PreparedStatement Example
    ps.setString(1,""+i);
    rs = ps.executeQuery();
  }
} catch (SQLException sqle) {sqle.printStackTrace();}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22863348
You can't parameterize the table name i'm afraid. But you can set parameters at run time
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22863350
An example of using setObject...

Best regards...
import java.sql.*;
 
public class PreparedStatementSetObject{
  public static void main(String[] args) {
    System.out.println("Prepared Statement Set Array Example!");
    Connection con = null;
    try{
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
      try{
        PreparedStatement prest = con.prepareStatement("insert emp_sal values(?,?)");
        prest.setObject(1,"Sushil");
        prest.setObject(2,15000);
        int n = prest.executeUpdate();
        System.out.println(n + " Record is added in the table.");
        con.close();
      }
      catch (SQLException s){
        System.out.println("SQL statement is not executed!");
      }
    }
    catch (Exception e){
      e.printStackTrace();
    }
  }
} 

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Accepted Solution

by:
jazzIIIlove earned 2000 total points
ID: 22863386
Hi there;

and no for table name
You can only use the ? for "data" places. Not table name and not field/column names as far as i know...

Best regards...
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22864072
if the table name is only known at runtime, you cannot use binding parameters.

You may try this

String sql = "SELECT * FROM " + myTable + " T WHERE T.attribute = " + value;
PreparedStatement stmt = myconnection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
...
0
 
LVL 5

Expert Comment

by:Triguna
ID: 22864847
If you still didnt find an answer from the above post,
Go through the following link. You will definitely find an answer for all your queries.
http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=1
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
0
 

Author Closing Comment

by:Zodan
ID: 31512540
Thank you for the code snips and explanation
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22887872
jazzIIIlove, can you tell me why you accepted a comment that repeated one i made?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question