aspose file tools*
The moose likes JDBC and the fly likes How to construct the SQL statement ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to construct the SQL statement ? " Watch "How to construct the SQL statement ? " New topic
Author

How to construct the SQL statement ?

Tien Thinh Nguyen
Greenhorn

Joined: Aug 21, 2005
Posts: 2
Dear all,
I am a fresh JSP leaner, i would like to know how to construct the SQL statement in JSP.
I have no problem with the database connection, but a statement problem. Let's say, i have a table (created in MS-Access) ABC, with 2 fields, ID (Number data type), Name (Text data type). How can i construct a statment to find a Name based on Id, which is captured from a HTML form ?
Thank you for your help
I am looking for your help ! thank you in advance !
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410



If you're just starting out, I highly recommend finding a good book or tutorial on JSP/Servlets (and if you haven't already, Java).
Use the search tool on this and/or the servlets forum with the keyword "book" and you'll find plendy of recommendations.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61654
    
  67

I would be remiss if I did not point out that performing database access directly within a JSP page is generally considered to be an extremely poor practice.

Moreover, how you construct a SQL statement really has nothing to do with JSP technology, so I have moved this to the JDBC forum.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Tien Thinh Nguyen
Greenhorn

Joined: Aug 21, 2005
Posts: 2
Thank you very much for your all recommendation !
However, i encouter a problem "Data type mismatch in criteria expression." when i follow your instruction ! Please help me with this problem ! Thank you very much !
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14347
    
  22

Ben Souther wrote:

Do not generate SQL statements by concatenating parameters like this!
A JSP with the above code has a security problem: it is vulnerable for SQL injection.

Think about what happens if I go to that page with the following value for id: 0' or '1'='1

If I'd surf to your JSP with an URL like this:
http://yourserver:/yourapp/yourjsp.jsp?id=0%27%20or%20%271%27%3D%271

(note, I have URL-encoded the special value for id).

What is your SQL statement going to look like? Like this:

SELECT * FROM t_person WHERE id = '0' or '1'='1'

Suddenly it returns all the rows in the table t_person.

Maybe that isn't a big problem with this SQL statement, but suppose you have another JSP page that's meant to delete a row from the database. If you would build the DELETE statement the same way as you did above, a hacker could let your JSP page delete the contents of a whole table by carefully choosing a value for the parameter.


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14347
    
  22

To prevent vulnerability to SQL injection, you should use PreparedStatement and use the set...(...) methods of class PreparedStatement to set the parameters. For example:
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
To prevent vulnerability to SQL injection, you should use PreparedStatement and use the set...(...) methods of class PreparedStatement to set the parameters.


This topic of sql injection comes up a bit. Can't sql injection also be avoided by doubling up on single quotes that are part of data



http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14347
    
  22

This topic of sql injection comes up a bit. Can't sql injection also be avoided by doubling up on single quotes that are part of data

Maybe, but then you're escaping the SQL by hand. Why wouldn't you want to let PreparedStatement take care of it automatically? Also, using PreparedStatement is potentially more efficient, since the driver and/or database can cache the SQL statement - if you're sending it a new statement everytime, it has to interpret the whole statement every time.

So there's no reason not to use PreparedStatment.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
So there's no reason not to use PreparedStatment.


There is one compelling reason to use Statements over PreparedStatements. If you use Statement you can abstract your jdbc calls (eliminate explicit use of Connections/PreparedStatements/ResultSets), and also abstract your query passed type.

For example the posted code below that uses PrepareStatement requires you to deal with Connections/PreparedStatements/ResultSets for EVERY unique query in your application. I have seem many developers not close connections or introduce other bugs due to writing such error prone code.

If you use Statments you can write code that abstracts the JDBC calls out. For example in my openSource project (fdsapi.com) I have a DataAcccess class. This class has a method that takes any SQL Query and converts it to an Object[][] (an ArrayList too) without the developer having to open Connections/Statments/ResultSets. The class below called the ResultSetConverter contains the resulting array.






Also, using PreparedStatement is potentially more efficient...



Whether you get performance gains from PreparedStatements/Statements is product/version dependent. Also in the web world most PreparedStatements are used by one request and then returned to the garbage collector so they may actually perform worse than Statements. Also, for Sybase I even compared Statement/PreparedStatement performance and saw NO performance difference even when the PreparedStatement was reused!

If you look at my live demo available at http://www.fdsapi.com you can run live queries and go to the JAMon stats page and look at performance. You can see with HSQLDB (my demo database) Statements are very fast also.

So I think those are some compelling reasons to consider Statements.
Derek Clegg
Greenhorn

Joined: Aug 22, 2005
Posts: 20
Originally posted by Tien Thinh Nguyen:
Thank you very much for your all recommendation !
However, i encouter a problem "Data type mismatch in criteria expression." when i follow your instruction ! Please help me with this problem ! Thank you very much !


Im guessing the 'data type mismatch' error is because your database id is in number format and the sample code stores its id as a String ie text.
 
wood burning stoves
 
subject: How to construct the SQL statement ?