Developping database layer with Grails is pretty fun, I like the Closure Criteria notation.
One of my need was to query some Postgresql database columns, columns being crypted. I had to extend GORM Hibernate mapping and query to be able to work with crypting. Grails 2.3.5, Hibernate 4.1.
Hibernate UserType :
Then apply this type to any Domain property :
From now, the encryption/decryption will be automatically applied on Grails save and find. Magic !
In you BootStrap.groovy, add a decrypting function to Hibernate :
Decryption is processed by the database to query on crypted column.
One of my need was to query some Postgresql database columns, columns being crypted. I had to extend GORM Hibernate mapping and query to be able to work with crypting. Grails 2.3.5, Hibernate 4.1.
Mapping Domain property
First job was to map my Domain properties with a custom Hibernate type.Hibernate UserType :
public class StringEncrypted implements UserType {
// Here we will manipulate only BINARY persisted types (encrypted data are persisted into binary format)
static final int SQL_TYPE = Types.BINARY
static final int[] SQL_TYPES = [ SQL_TYPE ]
private static boolean initialized = false;
protected static CipherService cipherService = null;
/**
* The class returned by nullSafeGet().
*
* @return Class
*/
@Override
Class returnedClass() {
return T.class
}
/**
* Return the SQL type codes for the columns mapped by this type.
* The codes are defined on java.sql.Types.
*
* @return int[] the typecodes
* @see Types
*/
@Override
public final int[] sqlTypes() {
return (int[]) SQL_TYPES.clone();
}
/**
* Compare two instances of the class mapped by this type for persistence "equality".
* Equality of the persistent state.
*
* @param x
* @param y
* @return boolean
* @throws HibernateException
*/
@Override
public final boolean equals(final Object x, final Object y)
throws HibernateException {
return x == y || ( x != null && y != null && x.equals( y ) );
}
/**
* Get a hashcode for the instance, consistent with persistence "equality".
*
* @param x
* @return
* @throws HibernateException
*/
@Override
public final int hashCode(final Object x)
throws HibernateException {
return x.hashCode();
}
/**
* Retrieve an instance of the mapped class from a JDBC resultset. Implementors should handle possibility of null values.
*
* @param rs a JDBC result set
* @param names the column names
* @param session
* @param owner the containing entity
* @return Object
* @throws HibernateException
* @throws SQLException
*/
@Override
public Object nullSafeGet(final ResultSet rs, final String[] names,
final SessionImplementor session, final Object owner)
throws HibernateException, SQLException {
checkInitialization();
final byte[] message = rs.getBytes(names[0])
return rs.wasNull() ? null : convertToObject( cipherService.decrypt( message ) )
}
/**
* Write an instance of the mapped class to a prepared statement.
* Implementors should handle possibility of null values.
* A multi-column type should be written to parameters starting from index.
*
* @param st a JDBC prepared statement
* @param value the object to write
* @param index statement parameter index
* @param session
* @throws HibernateException
* @throws SQLException
*/
@Override
public void nullSafeSet(final PreparedStatement st, final Object value, final int index,
final SessionImplementor session) throws HibernateException, SQLException {
checkInitialization();
if (value == null) {
st.setNull(index, SQL_TYPE);
} else {
st.setBytes(index, cipherService.encrypt( convertToByteArray( (T)value ) ) );
}
}
/**
* Return a deep copy of the persistent state, stopping at entities and at collections.
* It is not necessary to copy immutable objects, or null values, in which case it is safe to simply return the argument.
*
* @param value the object to be cloned, which may be null
* @return Object a copy
* @throws HibernateException
*/
@Override
public final Object deepCopy(final Object value)
throws HibernateException {
return value;
}
/**
* Are objects of this type mutable?
*
* @return boolean
*/
@Override
public final boolean isMutable() {
return false;
}
/**
* Transform the object into its cacheable representation. At the very least this method should perform a deep copy if the type is mutable.
* That may not be enough for some implementations, however; for example, associations must be cached as identifier values. (optional operation)
*
* @param value the object to be cached
* @return a cachable representation of the object
* @throws HibernateException
*/
@Override
public final Serializable disassemble(final Object value)
throws HibernateException {
if (value == null) {
return null;
}
return (Serializable) deepCopy(value);
}
/**
* Reconstruct an object from the cacheable representation.
* At the very least this method should perform a deep copy if the type is mutable. (optional operation)
*
* @param cached the object to be cached
* @param owner the owner of the cached object
* @return a reconstructed object from the cachable representation
* @throws HibernateException
*/
@Override
public final Object assemble(final Serializable cached, final Object owner)
throws HibernateException {
if (cached == null) {
return null;
}
return deepCopy(cached);
}
/**
* During merge, replace the existing (target) value in the entity we are merging to with
* a new (original) value from the detached entity we are merging.
* For immutable objects, or null values, it is safe to simply return the first parameter.
* For mutable objects, it is safe to return a copy of the first parameter.
* For objects with component values, it might make sense to recursively replace component values.
*
* @param original the value from the detached entity being merged
* @param target the value in the managed entity
* @param owner
* @return the value to be merged
* @throws HibernateException
*/
@Override
public final Object replace(final Object original, final Object target, final Object owner)
throws HibernateException {
return original;
}
/**
* Charset used for the non-encrypted data
*/
public static String CHARSET = "UTF-8"
@Override
protected String convertToObject(byte[] byteArray) {
return new String( byteArray, CHARSET )
}
@Override
protected byte[] convertToByteArray(String object) {
return object.getBytes( CHARSET )
}
/**
* Checks if cipherService is initialized, and initializes it if necessary
*/
protected static synchronized final void checkInitialization() {
if (!initialized) {
cipherService = Holders.getGrailsApplication().mainContext.getBean("cipherService")
initialized = true;
}
}
Then apply this type to any Domain property :
String label
static mapping={
label type: StringEncrypted
}
From now, the encryption/decryption will be automatically applied on Grails save and find. Magic !
Add decryption function to HQL language
To query with decryption "on the fly" by the databse engine, this requires first that you crypting algorithm is exactly the same between your JVM and your database. Use "AES/CBC/PKCS5Padding" between Java and Postgresql.In you BootStrap.groovy, add a decrypting function to Hibernate :
def dialect = grailsApplication.mainContext.sessionFactory.dialect
def decryptString = new SQLFunctionTemplate(BinaryType.INSTANCE, "convert_from(decrypt(?1, convert_to('"+grailsApplication.config.cipherKey+"', 'UTF8'), 'aes'), 'UTF8')"){
public Type getReturnType(Type columnType, Mapping mapping){
return StringType.INSTANCE;
}
}
dialect.registerFunction('decryptString', decryptString)
From now, a new HQL function is available : decryptString.Query
You can now decrypt on the fly in your HQL function :def res = DemoDomain.executeQuery(
"FROM DemoDomain \
WHERE decryptString(label) = :testValue", [testValue : testValue])[0]
Decryption is processed by the database to query on crypted column.
Note
- This raises performance issues as the query performs a full table scan... Well sometimes you just haven't the choice but to query this way.
- Couldn't find a way to query with add custom function on Closure notation. What a pity.
Articles
