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.
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');
Download the Oracle 11 XE 32 bit and install it.
No problem for me ( Win7 64)
Conn / as sysdba
passw system
input new password.
[http://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/]
select * from v$version
mvn install:install-file -Dfile=C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0 -Dpackaging=jar
then add modify your pom:
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0</version> </dependency>
[http://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/]
public static void main(String[] args) throws Throwable {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = null;
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","root","root");
System.out.println(connection.getMetaData().getDatabaseMajorVersion());
connection.close();
}
JDBCTemplate simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results.
http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/core/JdbcTemplate.html
Here are two methods, the 2nd one is using JDBCTemplate from Srping, you can see the code are more simple and clear:
/** * LoadUsers using JDBC. * @param dataSource * @return */ public ListloadUserJDBC(DataSource dataSource) { List users = new ArrayList (); Connection conn = null; try { conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement("SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER"); ResultSet rs = preparedStatement.executeQuery(); while (rs.next()) { User user = new User(); users.add(user); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); System.out.println(user); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return users; } /** * Load users using Spring JDBCTemplate. * @param jdbcTemplate * @return */ public java.util.List doJDBCTempQuery(JdbcTemplate jdbcTemplate) { String queryUsers = "SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER"; java.util.List users = jdbcTemplate.query(queryUsers, new ParameterizedRowMapper () { @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User user = new User(); user.setId(rs.getInt(1)); user.setName(rs.getString(4)); return user; } }); return users; }
And here is a example to define the template bean:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/mini-liguoliang.com" />
<property name="username" value="root" />
<property name="password" value="pw" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<constructor-arg ref="dataSource"></constructor-arg>
</bean>
需求: (使用JDBC) 在SQL中增加时间限制
简单错误的写法:
SimpleDateFormat df = new SimpleDateFormat(“yyyy-MM-dd”);
String dateNow = df.format(new Date(System.currentTimeMillis())); //”2010-11-19″; //new Date(System.currentTimeMillis()).toString();
然后将dateNow加入到SQL中. maybe可工作, 但不能保证数据库平台迁移后仍可继续工作.
正确的解决方法, 使用PreparedStatement设定SQL参数:
StringBuilder sb = new StringBuilder("SELECT oue FROM OUEnroll oue WHERE oue.user_ID = ");
sb.append(user.getUser_ID()).append(" AND (oue.dateStart IS NULL OR ").append("?2")...
// 省略若干
select.setParameter(1, user.getUser_ID());
select.setParameter(2, new Date(System.currentTimeMillis()));
JDBC会根据具体的数据库Driver处理Date并加入到SQL中.
// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.