Cursors: Retrieving ResultSet by executing DB Procedure or Functions

Usually we get one or more variable returned values from procedure or function.

But if we want to execute and SQL query as part of procedure/function and to get those results in JDBC program, then we need to use SYS_REFCURSOR type as return type.

Procedure:

create or replace procedure getEmps(emps OUT SYS_REFCURSOR, sal IN number)
as
BEGIN
open emps for
select employee_id from employees where salary < sal;
END getEmps;
/

Function:

create or replace function getEmployees(sal IN number) return SYS_REFCURSOR

as

Employees SYS_REFCURSOR;

BEGIN

open Employees for

select employee_id from employees where salaray < sal;

return Employees;

END getEmployees;

/

Program:

package com;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class CallableSt2 {

Connection con = null;

CallableSt2() {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "HR", "HR");

} catch(Exception e) {

e.printStackTrace();

}

}

public void testProcCursor() throws Exception {

CallableStatement cst = con.prepareCall("{call getEmps(?,?)}");

cst.setFloat(2,  4000.0f);

cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cst.execute();

ResultSet rs = (ResultSet) cst.getObject(1);

while(rs.next()) {

System.out.println(rs.getInt(1));

}

}

public void testFuncCursor() throws Exception {

CallableStatement cst = con.prepareCall("{?=call getEmployees(?)}");

cst.setFloat(2, 4000.0f);

cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cst.execute();

ResultSet rs = (ResultSet) cst.getObject(1);

while(rs.next()) {

System.out.println(rs.getInt(1));

}

}

static public void main(String[] args) throws Exception {

CallableSt2 test = new CallableSt2();

test.testProcCursor();

test.testFuncCursor();

}

}

Executing database Procedures and functions: CallableStatement

Procedure:
 create or replace procedure getSalary(id IN number, sal OUT number)
 as
 --Global variables goes here
 BEGIN
 select salary into sal from employees where employee_id=id;
 END getSalary;
 /

Function:
create or replace function getAverageSal(id1 IN number, id2 IN number) return number
as
sal1 number;
sal2 number;
BEGIN
  select salary into sal1 from employees where employee_id=id1;
  select salary into sal2 from employees where employee_id=id2;
  
  return (sal1+sal2)/2;
END getAverageSal;
/

JDBC Program:
package com;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

public class CallableStTest {
Connection con = null;
String dbDriver = "oracle.jdbc.driver.OracleDriver",
dbUrl = "jdbc:oracle:thin:@localhost:1521:xe", dbUser = "HR",
dbPwd = "HR";

public CallableStTest() throws Exception {
Class.forName(dbDriver);

con = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
}

public void callProcedure() {
CallableStatement cst = null;
ResultSet rs = null;

try {
cst = con.prepareCall("{call getSalary(?, ?)}");

cst.setInt(1, 106);
cst.registerOutParameter(2, Types.FLOAT);

cst.execute();

System.out.println(cst.getFloat(2));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (cst != null)
cst.close();
if (rs != null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

public void callFunction() {
CallableStatement cst = null;
ResultSet rs = null;

try {
cst = con.prepareCall("{?=call GETAVERAGESAL(?,?)}");

cst.setFloat(2, 100);
cst.setFloat(3, 101);

cst.registerOutParameter(1, Types.FLOAT);

cst.execute();

System.out.println(cst.getFloat(1));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (cst != null)
cst.close();
if (rs != null)
rs.close();
if(con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

static public void main(String[] args) throws Exception {
CallableStTest test = new CallableStTest();

test.callProcedure();
test.callFunction();
}
}

SQL Injection with JDBC Statement interface object

SQL Injection is an hacking technique, in which we bypass the where conditions in sql query.

Lets say there is a table called user_cred, with two columns username and password.
Currently table contains only one column, with values user1/Welcome1

Now,
select * from user_cred where username='user1' and password='Welcome1';
This will give one row.

If any of the field username/password is given wrong, then we wont get any results.
But if we know the username and without knowing password also, we can retrieve the results here:

Hack 1: Username is known and password is not known
select * from user_cred where username='user1'-- and password='NoPassword';

The key point here is that in SQL, -- starts a comment. So whatever is there after these two characters, it will not be executed. So the query results in one result.

Hack 2: Username and password, both are not known.
select * from user_cred where username='nouser' or 1=1-- and password='NoPassword';

Here we are passing and extra where condition 1=1, which is always true and the password condition is bypassed. So even though the username is wrong, we are able to get the results.

SQL Injection: We can hack Statement object:

public void sqlInjStatement(String username, String password) {
Statement st = null;
ResultSet rs = null;

try {
st = con.createStatement();
rs = st.executeQuery("select * from user_cred where username='"
+ username + "' and password='" + password + "'");

if (rs != null && rs.next())
System.out.println("SQL Injection with Statement: User("
+ username + "/" + password + ") is valid");
else
System.out.println("SQL Injection with Statement: User("
+ username + "/" + password + ") is wrong");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
if (rs != null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

Testing:

test.sqlInjStatement("user1", "Welcome1");
test.sqlInjStatement("user1", "Welcome2");
test.sqlInjStatement("user1' and 1=1--", "NoPassword");
test.sqlInjStatement("nouser' or 1=1--", "NoPassword");
test.sqlInjStatement("user1'--", "Welcome2");

Output:

SQL Injection with Statement: User(user1/Welcome1) is valid
SQL Injection with Statement: User(user1/Welcome2) is wrong
SQL Injection with Statement: User(user1' and 1=1--/NoPassword) is valid
SQL Injection with Statement: User(nouser' or 1=1--/NoPassword) is valid
SQL Injection with Statement: User(user1'--/Welcome2) is valid

PreparedStatement wont allow above:

public void sqlInjPrepStatement(String username, String password) {

PreparedStatement pst = null;

ResultSet rs = null;

try {

pst = con.prepareStatement("select * from user_cred where username=? and password=?");

pst.setString(1, username);

pst.setString(2, password);

rs = pst.executeQuery();

if (rs != null && rs.next())

System.out.println("SQL Injection with PreparedStatement: User("

+ username + "/" + password + ") is valid");

else

System.out.println("SQL Injection with PreparedStatement: User("

+ username + "/" + password + ") is wrong");

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (pst != null)

pst.close();

if (rs != null) {

rs.close();

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

Testing:

test.sqlInjPrepStatement("user1", "Welcome1");
test.sqlInjPrepStatement("user1", "Welcome2");
test.sqlInjPrepStatement("user1' and 1=1--", "NoPassword");
test.sqlInjPrepStatement("nouser' or 1=1--", "NoPassword");
test.sqlInjPrepStatement("user1'--", "Welcome2");

Output:

SQL Injection with PreparedStatement: User(user1/Welcome1) is valid
SQL Injection with PreparedStatement: User(user1/Welcome2) is wrong
SQL Injection with PreparedStatement: User(user1' and 1=1--/NoPassword) is wrong
SQL Injection with PreparedStatement: User(nouser' or 1=1--/NoPassword) is wrong
SQL Injection with PreparedStatement: User(user1'--/Welcome2) is wrong

JDBC Basic Information

Driver URL format:

mainProtocol:subProtocol:dbName

- here mainProtocol should be always "jdbc"
- subProtocol may be varied depending on the database


JDBC Driver Names

DB Type	Driver Name	DB URL Format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/ databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname: port Number/databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver
Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port Number:databaseName

How to know the type of ResultSet?

Call getType() on ResultSet object.

It will return either of following integer values:

1. 1003    TYPE_FORWARD_ONLY

Can traverse in only forward direction

Not sensitive to the Database updates during the records traversal

2. 1004    TYPE_SCROLL_INSENSITIVE

Can traverse in both forward and reverse directions

Not sensitive to the Database updates during the records traversal

3. 1005    TYPE_SCROLL_SENSITIVE

Can traverse in both forward and reverse directions

Sensitive to the Database updates during the records traversal

Oracle driver(oracle.jdbc.driver.OracleDriver) does not support TYPE_SCROLL_SENSITIVE

How to know whether a ResultSet is read only or updatable?
Call getConcurrency() on ResultSet object, which will return either of the following two values:

1. 1007    CONCUR_READ_ONLY
ResultSet cannot be updatable

2. 1008    CONCUR_UPDATABLE
ResultSet is updatable