Derby

Quick Setup a Project Using JDBC & Derby DB/Java DB

Categories: Database • Java; Tagged with: Derby • Java • JDBC • Maven; @ May 8th, 2014 0:03

Set up Maven dependences

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derbyclient</artifactId>
<version>10.10.2.0</version>
</dependency>

<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.10.2.0</version>
</dependency>

Connect to the DB using JDBC

This method will print out all Tables; // before you do anything with the Database, there should be some System tables already.

package com.liguoliang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCTest {

	private static final String DB_URL = "jdbc:derby:tempDBForTest;create=true";	
	public static void main(String[] args) {
	    Connection conn = null ;
	    try {
	        Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	        conn = DriverManager.getConnection(DB_URL);
	        
	        String sql = "SELECT TABLENAME FROM SYS.SYSTABLES";
			PreparedStatement ps = conn.prepareStatement(sql);
	        
	        ResultSet rs = ps.executeQuery();
	        while (rs.next()) {
				System.out.println("Rs: " + rs.getString(1));
			}
	    } catch (SQLException se) {
	         se.printStackTrace();
	    } catch(ClassNotFoundException e){
	        System.out.println("JDBC Driver not found in CLASSPATH") ;
	    }finally {
	        if(conn != null){
	            try{
	                conn.close() ;
	            } catch(SQLException se){
	                se.printStackTrace();
	            }
	        }
	    }
	}

}

Use Eclipse to manage your Derby

Switch to ‘Database Development’ perspective, you may create/manage Derby DB/table.

JDBC Statement SQL Injection

Categories: Java; Tagged with: Derby • Java • JDBC; @ May 7th, 2014 23:54

Here is an example to inject dirty SQL using JDBC Statement:

package com.liguoliang.j2ee;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import org.junit.Before;
import org.junit.Test;

public class TestJDBC {

	private static final String DB_URL = "jdbc:derby:C:\\Users\\Guoliang\\MyDB;create=true";
	
	Connection conn = null ;
	
	@Before
	public void before() throws ClassNotFoundException, SQLException {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	    conn = DriverManager.getConnection(DB_URL);
	}
	
	@Test
	public void testSQLInjection() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password" + new Date().toString();
		
		Statement statement = conn.createStatement();
		String sql = "SELECT * FROM USERS WHERE USER_NAME = '" + userName + "' AND PASSWORD = '" + password + "'";
		System.out.println(sql);
		ResultSet rs = statement.executeQuery(sql);
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			System.out.println(" > User Id: " + userId); // Will print all user id;
		}
		assertTrue(userId != -1);
	}
	
	@Test
	public void testPreparedStatement() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password";
		
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERS WHERE USER_NAME = ? AND PASSWORD = ?");
		ps.setString(1, userName);
		ps.setString(2, password);
		
		ResultSet rs = ps.executeQuery();
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			fail("User Id: " + userId);
		}
		
		System.out.println("userId: " + userId);
	}

}

I set one of the paramters to : [guoliang ‘a’ = ‘a], the I got the SQL:

SELECT * FROM USERS WHERE USER_NAME = 'guoliang' OR 'a' = 'a' AND PASSWORD = 'wrong-passwordWed May 07 23:47:11 CST 2014'

This means, the SQL will return all records in this table.

However we can use prepared statement to solve this issue.

SQLs used by this example:

CREATE TABLE USERS (ID INT, USER_NAME VARCHAR(20), PASSWORD VARCHAR(20));
INSERT INTO USERS (ID, USER_NAME, PASSWORD) VALUES (1, 'guoliang', 'password_abc');

Derby 不能修改列类型 CANNOT Alert Column Data type

Categories: Database; Tagged with: Derby; @ October 6th, 2010 16:03

Derby(10.5)中不能通过Alert修改Column的DataType, 可能需要重建Column才能实现DataType的修改.

(more…)

DB2 Derby Paged Select SQL分页查询语句

Categories: Database; Tagged with: DB2 • Derby • Paged Select • SQL; @ October 5th, 2010 21:50

DB2 Paged Select SQL 分页:

分页举例:
SELECT * FROM (
SELECT DEPT.ID, DEPT.MGRNO, ROWNUMBER() OVER (ORDER BY DEPT.ID) AS NS FROM DEPT WHERE DEPT.MGRNO > 0
) AS TEMP WHERE TEMP.NS BETWEEN 1 and 5;

See:

1. API: http://www.ibm.com/developerworks/data/library/techarticle/0307balani/0307balani.html
2. db2分页sql语法
3. db2排序rownumber函数讨论
4. Oracle, DB2 及 MySQL 分页查询写法

Derby Paged Select SQL 分页语句:

SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

See: Derby 10.4之后开始支持 http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html

Derby: Create auto-increment Column 创建自增Column

Categories: Database; Tagged with: Derby; @ September 16th, 2010 16:36

Similar to Microsoft SQL Server, Derby supports IDENTITY columns to help you creating auto-incremented sequence values for primary key columns. Derby offers two variations of IDENTITY columns:

“GENERATED ALWAYS AS IDENTITY” – Derby always provides auto-incremented sequence values to this column. You are not allowed to specify your own values. 由Derby负责提供自增的主键值, 该值不允许插入.
“GENERATED BY DEFAULT AS IDENTITY” – Derby provides auto-incremented sequence values to this as default only when you are not providing values. 仅在未提供主键值时, 由Derby负责插入.
相比BY ALWARYS, BY DEFAULT可Insert主键值.

我们将通过上述方法增加自增Column.

(more…)

Older Posts