iBatis – Encrypt Your Database Password
By admin on Jun 8, 2008 in Java, Programming
This is a recent requirement I got. I need to encrypt the database user name and password in the properties file that I used for iBatis.
Before this, I put my database configuration in a file called database.properties and referenced it in the iBatis SQL map configuration file directly.
E.g. database.properties file
driver=<JDBC driver>
jdbc.url=<JDBC connection string>
username=<database user name in plain text>
password=<database user password in plain text>
E.g. SQL map configuration file, sqlmap-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<!-- Always ensure to use the correct XML header as above! -->
<sqlMapConfig>
<!-- The properties (name=value) in the file specified here can be used placeholders in this config
file (e.g. ${driver}. The file is usually relative to the classpath and is optional. -->
<properties resource="database.properties"/>
<!-- These settings control SqlMap configuration details, primarily to do with transaction
management. They are all optional (see the Developer Guide for more). -->
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="1024"
maxSessions="128"
maxTransactions="32"
useStatementNamespaces="false"
/>
<!-- Type aliases allow you to use a shorter name for long fully qualified class names. -->
<!-- Configure a datasource to use with this SQL Map using SimpleDataSource.
Notice the use of the properties from the above resource -->
<transactionManager type="JDBC">
<dataSource type="DBCP">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
........
As you can see, in the SQL map configuration file, the properties file is referenced directly using the <properties> tag, and the property is accessed using the syntax ${property name}.
In my Java code, the SqlMapClient is built using SqlMapClientBuilder.buildSqlMapClient(java.io.Reader reader)
private static SqlMapClient sqlMap = null;
// Static initializer
static {
try {
String resource = "sqlmap-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
......
In order to use encrypted database user name and password, now I need to change the followings:
Database user name and password in database.properties now are encrypted. It can be using simple algorithm like ROT-13, or more advanced algorithms like DES or RSA.
driver=<JDBC driver>
jdbc.url.sdp=<JDBC connection string>
username=<Encrypted database user name>
password=<Encrypted database user password>
Next the reference to database.properties is removed from sqlmap-config.xml.
Since I removed the reference to database.properties from the SQL map configuration file, I need to use SqlMapClientBuilder.buildSqlMapClient(java.io.Reader reader, java.util.Properties props) to pass in the required properties at run time. It is during this time I can read the encrypted user name and password from database.properties, decrypt them accordingly and use them to construct SqlMapClient.
try {
Properties properties = new Properties();
properties.load(new FileInputStream("database.properties"));
String encryptedUsername = properties.getProperty("username");
String encryptedPassword = properties.getProperty("password");
String username = Utils.decrypt(encryptedUsername);
String password = Utils.decrypt(encryptedPassword);
properties.setProperty("username", username);
properties.setProperty("password", password);
String resource = "sqlmap-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader, properties);
......
Popularity: 2% [?]
kirankadarla | Jun 27, 2008 | Reply
Thank you, this is very useful.
One point I want to stress is
–> The reference to database.properties should be removed from your xml file. Other wise even if you have dummy values in your database.properties file or dummy values directly included in your xml file, the call to
SqlMapClientBuilder.buildSqlMapClient(reader, properties);
would never over ride the values
Regards
Kiran Kadarla
admin | Jun 27, 2008 | Reply
It is already mentioned there
Next the reference to database.properties is removed from sqlmap-config.xml.
Raghu | Nov 12, 2008 | Reply
Thank you for the information, it was very helpful.
Appreciate it !!!
Jeff | Apr 1, 2009 | Reply
Thanks for the info!
Helped me alot!
jerome bulanadi | Feb 9, 2010 | Reply
I like to share my solution. I had this Swing project that uses datasource properties. One problem is that any malicious user can view the datasource properties file and could maliciously connect to the database.
If the properties are hardcoded to the sqlmap-config.xml, the project can have less portability, and the xml can be viewed with any zip extractor. I thought of encryption then.
So here’s my solution.
You can simply extend a DataSource superclass and override the initialize(Map map) method.
This example extends the SimpleDataSourceFactory used the default SIMPLE datasource type.
The subclass
————————————-
package mycompany.datasource;
import com.ibatis.sqlmap.engine.datasource.SimpleDataSourceFactory;
// other imports
public class EncryptedSimpleDataSource extends SimpleDataSourceFactory{
@Override
public void initialize(Map map) {
Properties properties = new Properties();
properties.load(new FileInputStream(“database.properties”));
String driver = properties.getProperty(“driver”);
String url = properties.getProperty(“url”);
String encryptedUsername = properties.getProperty(“user”);
String encryptedPassword = properties.getProperty(“password”);
/* Put the plain text for driver and url */
map.put(“JDBC.Driver”, driver);
map.put(“JDBC.ConnectionURL”, url);
/* Decrypt the username and password, then put in map */
map.put(“JDBC.Username”, Utils.decrypt(encryptedUsername));
map.put(“JDBC.Password”, Utils.decrypt(encryptedPassword));
/* Initialize and let the rest be handle the super class */
super.initialize(map);
}
}
————————————-
The transaction manager of sqlmap-config.xml. Notice that the datasource type is the fully qualified class name of EncryptedSimpleDataSource. Also, the properties JDBC.* are set but blank values because the SIMPLE datasource requires these properties. The property values will be loaded by EncryptedSimpleDataSource
————————————-
The sqlmapclient initializer looks same as some getting started applications.
try {
String resource = “sqlmap-config.xml”;
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
……
The code works perfectly with my Swing project the uses Ibatis ORM.
jerome bulanadi | Feb 9, 2010 | Reply
Hey, where is my xml config in the comment?
I guess this site does not accept xml comments.
Oh, here’s the transaction manager of sqlmap-config.xml from the above comment.
<transactionManager type=”JDBC” commitRequired=”false”>
<dataSource type=”mycompany.datasource.EncryptedSimpleDataSource”>
<property name=”JDBC.Driver” value=”"/>
<property name=”JDBC.ConnectionURL” value=”"/>
<property name=”JDBC.Username” value=”"/>
<property name=”JDBC.Password” value=”"/>
</dataSource>
</transactionManager>