Hi
All,
Welcome
to Java-recent.
In
this post we will discuss about Prepared Statement-used
to execute sql queries from Java.
Lets
get into more details.
Prepared
Statement :-
is
an interface from java.sql.PreparedStatement
is
used to execute queries,set values in a query
Prepared
statement queries are precompiled and the fetch plan will be
stored in cache,so for subsequent requests only execution will
happen
They
are faster than Statement queries because Statement queries will get
compiled every time
Used
in case there is repetetion of a query
Prepared
statements can be parameterized,parameterization of query values is
done by using '?' - place holder
in setXXX() method
Prepared
Statement prevents SQL injection
Syntax
:-There are two ways of using prepared statement
1.PreparedStatement
preparedStatement = connection.prepareStatement("select
* from COMPANY where sid="+"'"+emailId+"'");
2.
PreparedStatement preparedStatement =
connection.prepareStatement("select
* from COMPANY where sid=? AND name=?");
preparedStatement.setInt(1,
101);
preparedStatement.setString(2,
"Google");
setXXX()
-- takes two parameters first one tells position of the value to be
placed,
this
starts from 1 .
Second
parameter is the respective value to be passed for ?-place holder
The
first type of declaration will not prevent SQL injections because we
are hardcoding the where clause with a variable.
Second
type of declaration will prevent SQL inection because all the
parameters passed will be escaped by JDBC
Example
Code:- There is a webpage which
takes user name as input and pass it into servlet
Servlet retrives emailId as per
user name from DB
Design.java
@WebServlet("/Design")
public
class
Design extends
HttpServlet {
private
static
final
long
serialVersionUID
= 1L;
protected
void
doGet(HttpServletRequest request, HttpServletResponse response)
throws
ServletException, IOException {
String
uName=request.getParameter("name");
Connection conn=null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//Creating connection object
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","admin");
String query="select mail from
COMPANY where name="+"'"+uName+"'";
PreparedStatement statement =conn.prepareStatement("select
mail from COMPANY where name=?1");
statement.setString(1, uName);
System.out.println("query--------"+query);
ResultSet rs= statement.executeQuery(); //Executing
query
//List
ls=(List)
rs;
//System.out.println(ls);
PrintWriter
out=response.getWriter();
out.println("<head><body>");
while(rs.next())
{
String
emailID= rs.getString("MAIL");
out.println("<h4>"+emailID+"</h4><br>");
}
out.println("</body></head>");
}catch
(ClassNotFoundException e1) {
//
TODO
Auto-generated catch block
e1.printStackTrace();
}
catch
(SQLException e) {
//
TODO
Auto-generated catch block
e.printStackTrace();
}
finally
{
try
{
conn.close();
}
catch
(SQLException e) {
//
TODO
Auto-generated catch block
e.printStackTrace();
}
}
}
Submit.html
<form action="Design">
Enter name <input
name="name"/><br/>
<input type="submit"
value="click here to get details"/>
</form>
Here we have used place holder
for passing values '?'
conn.prepareStatement("select
mail from COMPANY where name=?1");
statement.setString(1, uName);
Case1 :- when we enter value as
Google in the form and submit
Case2
:- Now we will provide some special characters in the form as
'
OR '1'='1
Now
the resultset will be empty,because the statement ResultSet
rs= PreparedStatement.executeQuery(); will remove the escape
characters. So unlike in previous post SQLinjection
it
will not return entire results.
Happy
Learning
Please
provide your valuable comments on this article and share it across
your network.
