Sometime i had to put sensitive informations such as database’s username and password on a properties file. In order make it safer, i had to encrypted all the information i had on these properties files. In this simple example, im trying to encrypt database username, password and connection url for iBatis connection, and i use Jasypt library for encryption.
As usual, a simple database,
CREATE DATABASE 'test'
USE 'test'
CREATE TABLE `contoh` (
`nama` varchar(30) NOT NULL DEFAULT '',
`alamat` varchar(100) DEFAULT NULL,
PRIMARY KEY (`nama`)
)
insert into contoh (nama, alamat) values ('edwin', 'singapore');
insert into contoh (nama, alamat) values ('kamplenk', 'ciledug');
insert into contoh (nama, alamat) values ('nugie', 'pamulang');
insert into contoh (nama, alamat) values ('samsu', 'dago');
insert into contoh (nama, alamat) values ('tebek', 'karawaci');
and a POJO
package com.edw.bean;
public class Contoh {
private String nama;
private String alamat;
// other setter and getter
@Override
public String toString() {
return "Contoh{" + "nama=" + nama + ", alamat=" + alamat + '}';
}
}
A simple xml query, contoh.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="contoh" >
<resultMap id="result" class="com.edw.bean.Contoh">
<result property="nama" column="NAMA" columnIndex="1"/>
<result property="alamat" column="ALAMAT" columnIndex="2"/>
</resultMap>
<select id="selectAllContoh" resultMap="result">
select nama, alamat
from contoh
</select>
</sqlMap>
and a simple xml to load all my queries, i named it sqlmapconfig.xml. Please take a look at line 21 to 23, these are parameters from properties file.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings
useStatementNamespaces="true"
lazyLoadingEnabled="true"
enhancementEnabled="true"
maxSessions="20"
/>
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="SetAutoCommitAllowed" value="false"/>
<property name="DefaultAutoCommit" value="false"/>
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL" value="${JDBC.ConnectionURL}"/>
<property name="JDBC.Username" value="${JDBC.Username}"/>
<property name="JDBC.Password" value="${JDBC.Password}"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/edw/sqlmap/contoh.xml"/>
</sqlMapConfig>
this is my db.properties file, always make sure you had this “ENC( )” on your encrypted values.
JDBC.ConnectionURL=ENC(Q29EXRzvWOsaXiT3YcYjlEN7W4OBU2YulDTnfrHq8LtVL3nyr7ATpQ==) JDBC.Username=ENC(sM7XAh9750ZUzguilF6kmw==) JDBC.Password=ENC(GTr5QT97wgVcLNclT8NjClNYKOLeP6c1)
This is my java file, i use it to load db.properties and connecting it to fit parameters on sqlmapconfig.xml.
package com.edw.config;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.File;
import java.io.FileInputStream;
import java.io.Reader;
import java.util.Properties;
import org.jasypt.encryption.pbe.StandardPBEStringEncryptor;
import org.jasypt.properties.EncryptableProperties;
/**
*
* @author edw
*/
public class SqlMapConfig {
protected static final SqlMapClient sqlMap;
private static final String PWD = "SDHLKSHUWEHDKSLKLJKSALJDLKA00IUAY98273492JLKASJDLKASJDKLAJSD";
static {
try {
StandardPBEStringEncryptor encryptor = new StandardPBEStringEncryptor();
encryptor.setAlgorithm("PBEWithMD5AndDES");
encryptor.setPassword(PWD);
Properties properties = new EncryptableProperties(encryptor);
properties.load(new FileInputStream(new File("db.properties")));
Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/sqlmapconfig.xml");
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader,properties);
} catch (Exception e){
throw new RuntimeException("Fatal Error. Cause: " + e, e);
}
}
public static SqlMapClient getSqlMap() {
return sqlMap;
}
}
this is my Main class,
package com.edw.main;
import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import java.util.List;
import org.apache.log4j.Logger;
/**
*
* @author edw
*/
public class Main {
private Logger logger = Logger.getLogger(Main.class);
public Main() {
}
private void execute(){
try {
List<Contoh> contohs = SqlMapConfig.getSqlMap().queryForList("contoh.selectAllContoh");
for (Contoh contoh : contohs) {
logger.debug(contoh);
}
} catch (Exception ex) {
logger.error(ex.getMessage(),ex);
}
}
public static void main(String[] args) {
Main main = new Main();
main.execute();
}
}
this is what happen on my log window when im running this app
run:
2011-02-06 00:10:27,808 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:26 - Created connection 29524641.
2011-02-06 00:10:27,812 [Connection] DEBUG java.sql.Connection:26 - {conn-100000} Connection
2011-02-06 00:10:27,815 [Connection] DEBUG java.sql.Connection:26 - {conn-100000} Preparing Statement: select nama, alamat from contoh
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Executing Statement: select nama, alamat from contoh
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Parameters: []
2011-02-06 00:10:27,855 [PreparedStatement] DEBUG java.sql.PreparedStatement:26 - {pstm-100001} Types: []
2011-02-06 00:10:27,857 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} ResultSet
2011-02-06 00:10:27,873 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Header: [NAMA, ALAMAT]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [edwin, singapore]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [kamplenk, ciledug]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [nugie, pamulang]
2011-02-06 00:10:27,874 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [samsu, dago]
2011-02-06 00:10:27,875 [ResultSet] DEBUG java.sql.ResultSet:26 - {rset-100002} Result: [tebek, karawaci]
2011-02-06 00:10:27,876 [SimpleDataSource] DEBUG com.ibatis.common.jdbc.SimpleDataSource:26 - Returned connection 29524641 to pool.
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=edwin, alamat=singapore}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=kamplenk, alamat=ciledug}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=nugie, alamat=pamulang}
2011-02-06 00:10:27,876 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=samsu, alamat=dago}
2011-02-06 00:10:27,878 [Main] DEBUG com.edw.main.Main:27 - Contoh{nama=tebek, alamat=karawaci}
BUILD SUCCESSFUL (total time: 0 seconds)
Btw, im using this test class to generate my encrypted values,
package com.edw.test;
import org.jasypt.encryption.pbe.StandardPBEStringEncryptor;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
/**
*
* @author edw
*/
public class TestEncryption {
private static final String PWD = "SDHLKSHUWEHDKSLKLJKSALJDLKA00IUAY98273492JLKASJDLKASJDKLAJSD";
public TestEncryption() {
}
@BeforeClass
public static void setUpClass() throws Exception {
}
@AfterClass
public static void tearDownClass() throws Exception {
}
@Test
public void test() {
StandardPBEStringEncryptor encryptor = new StandardPBEStringEncryptor();
encryptor.setAlgorithm("PBEWithMD5AndDES");
encryptor.setPassword(PWD);
System.out.println(encryptor.encrypt("jdbc:mysql://localhost/test"));
System.out.println(encryptor.encrypt("root"));
System.out.println(encryptor.encrypt("password"));
}
}
and this is my NetBeans project structure
Well, i hope it can help others. Cheers (B)