JDBC Part2
JDBC Part2
JDBC Part2
Introduction
SQL injection is a code injection technique, used to attack data-driven applications, in which
nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database
contents to the attacker).
In this section we will talk about that and its relation with JDBC Statement.
Examples
Statement & SQL Injection evil
Note in this example we will use PostgreSQL DBMS, but you can use any DBMS
We will use a database bd_test witch contain a Schema: sch_test and two tables users and test :
1
if (result.next()) {
System.out.println("id = " + result.getInt("id") + " | username = "
+ result.getString("username") + " | password = " +
result.getString("password"));
}else{
System.out.println("Login not correct");
}
2
DROP DATABASE
The worst is to drop the database
3
PreparedStatement
Remarks
A PreparedStatement declares the statement before it is executed, and allows for placeholders for
parameters. This allows the statement to be prepared (and optimized) once on the server, and
then reused with different sets of parameters.
The added benefit of the parameter placeholders, is that it provides protection against SQL
injection. This is achieved either by sending the parameter values separately, or because the
driver escapes values correctly as needed.
Examples
Setting parameters for PreparedStatement
Placeholders in the query string need to be set by using the set* methods:
String sql = "SELECT * FROM EMP WHERE JOB = ? AND SAL > ?";
Special cases
Setting NULL value:
Setting a null value can not be accomplished using for example the setInt and setLong methods,
as these use primitive types (int and long) instead of objects (Integer and Long), and would cause
a NullPointerException to be thrown:
4
Setting LOBs
This example shows how to create a prepared statement with an insert statement with
parameters, set values to those parameters and then executing the statement.
insert.executeUpdate();
}
The question marks (?) in the insert statement are the parameter placeholders. They are positional
parameters that are later referenced (using a 1-based index) using the setXXX methods to set
values to those parameters.
The use of try-with-resources ensures that the statement is closed and any resources in use for
that statement are released.
5
Java CallableStatement Interface
CallableStatement interface is used to call the stored procedures and functions.
We can have business logic on the database by the use of stored procedures and functions that will make
the performance better because these are precompiled.
Suppose you need the get the age of the employee based on the date of birth, you may create a function
that receives date as the input and returns age of the employee as the output.
must not have the return type. must have the return type.
We can call functions from the Procedure cannot be called from function.
procedure.
Procedure supports input and output Function supports only input parameter.
parameters.
Exception handling using try/catch Exception handling using try/catch can't be used in user
block can be used in stored procedures. defined functions.
6
1. CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");
In this example, we are going to call the stored procedure INSERTR that receives id and name as the
parameter and inserts it into the table user420. Note that you need to create the user420 table as well to
run this application.
1. import java.sql.*;
2. public class Proc {
3. public static void main(String[] args) throws Exception{
4.
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6. Connection con=DriverManager.getConnection(
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
8.
7
9. CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");
10. stmt.setInt(1,1011);
11. stmt.setString(2,"Amit");
12. stmt.execute();
13.
14. System.out.println("success");
15. }
16. }
Now check the table in the database, value is inserted in the user420 table.
The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB etc.
1. import java.sql.*;
2.
8
3. public class FuncSum {
4. public static void main(String[] args) throws Exception{
5.
6. Class.forName("oracle.jdbc.driver.OracleDriver");
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");
11. stmt.setInt(2,10);
12. stmt.setInt(3,43);
13. stmt.registerOutParameter(1,Types.INTEGER);
14. stmt.execute();
15.
16. System.out.println(stmt.getInt(1));
17.
18. }
19. }
Output: 53
9
ResultSet
Introduction
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is
positioned before the first row. The next method moves the cursor to the next row, and because it
returns false when there are no more rows in the ResultSet object, it can be used in a while loop to
iterate through the result se
Examples
ResultSet
10
if (result.next()) {
//yes result not empty
}
if (result.next()) {
//get int from your result set
result.getInt("id");
//get string from your result set
result.getString("username");
//get boolean from your result set
result.getBoolean("validation");
//get double from your result set
result.getDouble("price");
}
ResultSetMetaData
Introduction
As we all know Metadata mean data about data.
To fetch metadata of a table like total number of column, column name, column type etc. ,
ResultSetMetaData interface is useful because it provides methods to get metadata from the
ResultSet object.
Examples
ResultSetMetaData
11
import java.sql.*;
class Rsmd {
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
12
Statement batching
Introduction
Statement batching is either executing multiple statements as one unit (with a normal
java.sql.Statement), or a single statement with multiple sets of parameter values (with a
java.sql.PreparedStatement).
Remarks
Statement batching allows a program to collect related statement, or in the case of prepared
statements related parameter value sets, and send them to the database server as a single
execute.
The benefits of statement batching can include improved performance. If and how these
performance benefits are achieved depends on the driver and database support, but they include:
Examples
Batch insertion using PreparedStatement
Batch execution using java.sql.PreparedStatement allows you to execute a single DML statement
with multiple sets of values for its parameters.
This example demonstrates how to prepare an insert statement and use it to insert multiple rows
in a batch.
13
Batch execution using Statement
Batch execution using java.sql.Statement allows you to execute multiple DML statements (update,
insert, delete) at once. This is achieved by creating a single statement object, adding the
statements to execute, and then execute the batch as one.
Note:
statement.executeBatch(); will return int[] to hold returned values, you can execute your batch like
this :
14