• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Java Program - SQL query

 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a Java program that executes SQL queries. Below is an example of a query that I would like to execute. Is it ok if the SQL query below contains a variable? The variable is "movieTitle" and this can be set equal to a wide variety of movie titles. I have a method that sets the value of "movieTitle" to a certain value. Is the line of code below correct?


Callable statement = conn.prepareCall("SELECT PRODUCER FROM MOVIES_TABLE WHERE MOVIE_TITLE = movieTitle");
 
Saloon Keeper
Posts: 10705
86
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows ChatGPT
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you want something like this. May need to enclose the title in quotes though.

 
Bartender
Posts: 3648
16
Android Mac OS X Firefox Browser Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you using store procedures to return the result set? If not, a PreparedStatement is better. The CallableStatement is for calling store procedures.

 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

K. Tsang wrote:Are you using store procedures to return the result set? If not, a PreparedStatement is better.


Here's more info on why a PreparedStatement with bind variables is better.


...
As compared to executing SQL statements directly, prepared statements offer two main advantages:[1]

  • The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times. Not all optimization can be performed at the time the prepared statement is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.[2]
  • Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

  • ...


    I suspect there's probably still some super-hacker trick to get around the SQL-injection protection here, but it's a lot safer than using plain SQL strings.
     
    Sheriff
    Posts: 22783
    131
    Eclipse IDE Spring VI Editor Chrome Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    As long as the JDBC driver is correctly written, there should be no risk of SQL injection if you use PreparedStatement correctly. The warning on that page is about PreparedStatement not being used correctly.
     
    Alissa Horner
    Ranch Hand
    Posts: 53
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    K. Tsang wrote:Are you using store procedures to return the result set? If not, a PreparedStatement is better. The CallableStatement is for calling store procedures.



    I'm not using a stored procedure. I'll use a PreparedStatement.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    reply
      Bookmark Topic Watch Topic
    • New Topic