Results 1 to 11 of 11
Like Tree2Likes
  • 2 Post By masijade

Thread: Statement and prepared statement

  1. #1
    Join Date
    Aug 2013
    Posts
    108
    Rep Power
    0

    Default Statement and prepared statement

    My question is why do they say we cannot pass dynamic values using just statement and need to use prepared statement? Below is a sample of the code which uses just statement with dynamic values.

    Java Code:
    String param1 = request.getParameter("parameter1");
    String param2 = request.getParameter("parameter2");
    String Update = "UPDATE table1 SET column1='"+param1+"'"+" WHERE column2 ='"+param2+"'";	
    stat = conn.createStatement();
    stat.executeUpdate(Update);

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Statement and prepared statement

    Because that is just BEGGING for sql syntax errors and SQL injection attacks, both of which PreparedStatement helps to guard against. Also, most DBs when given a statement first analyse the statement structure and then attempt to "optimize" the execution strategy for it. This is usually quick but it IS a delay, and doing your statements this way ensures that the DB does it EVERY time. Using a PreparedStatement, means that this happens once just submitting in varying values in the varying executions, and, if you use a Statement pool, you can even ensure that the same PreparedStatement is used over varying executions.

    Again, just because you CAN do something, does not mean that you SHOULD.

    In your example, what would happen if some entered
    Java Code:
    '; delete from table1 --
    as the value for "parameter1" or "parameter2"?

    Or, even something as innocent as
    Java Code:
    He's going over there.
    The first is an SQL Injection attack, and the second leads to an SQL syntax error.

    PreparedStatement, as I already said, would handle both of these and wind up attempting to update those columns with those exact values SAFELY.
    gimbal2 and SurfMan like this.

  3. #3
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,776
    Rep Power
    5

    Default Re: Statement and prepared statement

    Quote Originally Posted by masijade View Post
    Again, just because you CAN do something, does not mean that you SHOULD.
    I'm SO glad to finally see someone else say that. I have a standard extension: Everything is possible, that's why possibility it is not an argument to go do something.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  4. #4
    Join Date
    Aug 2013
    Posts
    108
    Rep Power
    0

    Default Re: Statement and prepared statement

    Ok agreed it is a good practice to use prepared statement instead of just statement. But look at what the documentations says:
    "Statement: Used to implement simple SQL statements with no parameters."

    Ref: Processing SQL Statements with JDBC (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

  5. #5
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,776
    Rep Power
    5

    Default Re: Statement and prepared statement

    Yeah sure if you have nothing dynamic in the query it does not matter if you use a statement or a prepared statement. But I always factor in human error and psychology. If you ALWAYS use PreparedStatement, there is no way you are mistakenly going to use a Statement where you shouldn't. Why even bother with it when you know that 99% of the queries you will fire are going to contain variables?
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  6. #6
    Join Date
    Aug 2013
    Posts
    108
    Rep Power
    0

    Default Re: Statement and prepared statement

    if you have nothing dynamic in the query it does not matter
    The values I used n the code are dynamic. The values of param1 and param2 are dynamic

  7. #7
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,776
    Rep Power
    5

    Default Re: Statement and prepared statement

    Yes, so PreparedStatement. I don't get what you want to achieve by repeating it.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  8. #8
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Statement and prepared statement

    Quote Originally Posted by suhaas.mohandos@gmail.com View Post
    Ok agreed it is a good practice to use prepared statement instead of just statement. But look at what the documentations says:
    "Statement: Used to implement simple SQL statements with no parameters."

    Ref: Processing SQL Statements with JDBC (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
    Because THAT IS what it is doing. By the time have finished concatenating your string and then create a Statement using that string there ARE no parameters.

    That the statement contains VALUES is irrelevant, it HAS NO parameters.

    Edit: It is ALSO irrelevant that you are "dynamically" building the statement query string (except for the concerns outlined earlier), the statement itself STILL contains NO parameters.
    Last edited by masijade; 01-30-2014 at 01:03 PM.

  9. #9
    Join Date
    Aug 2013
    Posts
    108
    Rep Power
    0

    Default Re: Statement and prepared statement

    Java Code:
    String Update = "UPDATE table1 SET column1='"+param1+"'"+" WHERE column2 ='"+param2+"'";
    The above is not a dynamic query? Then what is an example of a dynamic query?

    Apart from having some flaws the statement can do the same thing which a prepared statement can do. What is the need for prepared statement?

    Let me put it in another way. Except for the flaws mentioned in one of the above posts a statement is just like a prepared statement?

  10. #10
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Statement and prepared statement

    Quote Originally Posted by suhaas.mohandos@gmail.com View Post
    Java Code:
    String Update = "UPDATE table1 SET column1='"+param1+"'"+" WHERE column2 ='"+param2+"'";
    The above is not a dynamic query?
    No, it's not, according to the Statement object. You "dynamically" BUILD a static query string. The SQL statement HAS NO PARAMETERS, you BUILT the sql string USING "dynamic" input, but the SQL string is a STATIC SQL String containing NO PARAMETERS.

    Then what is an example of a dynamic query?
    Update table1 set column1 =:colum1var where column2 = :column2var

    represented in java as "Update table1 set column1 = ? where column2 = ?" using PreparedStatement

    Apart from having some flaws the statement can do the same thing which a prepared statement can do. What is the need for prepared statement?

    Let me put it in another way. Except for the flaws mentioned in one of the above posts a statement is just like a prepared statement?
    No, PreparedStatement is an EXTENSION of Statement. It can do EVERYTHING Statement can do (including executing static sql statements) with the added benefit of being able to do "dynamic" (as you like to call it) statements where you need only change the values of the placeholders while the DB analyses the statement only once, rather than with every execution, rather than having to define a whole new query statement, as well as the automatic escaping functions that greatly improves protection against SQL Injection attacks and SQL syntax errors due to user input.

  11. #11
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,776
    Rep Power
    5

    Default Re: Statement and prepared statement

    Jebus, this thread is an example of how to make your life as confusing as possible :/
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

Similar Threads

  1. SQL Prepared Statement with date
    By Dcalladi in forum New To Java
    Replies: 3
    Last Post: 02-18-2012, 03:37 PM
  2. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  3. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 09:49 AM
  4. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM
  5. Statement or Prepared Statement ?
    By paty in forum JDBC
    Replies: 3
    Last Post: 08-01-2007, 04:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •