how to handle data gotten from CLOBs?

[Philip Fry]

Hello,

I need some guidance with handling clob data. I have a derby table:

login(varchar), hash(clob), salt(clob).

Hashes and salts are generated like this:

public byte[] generate(byte[] password) throws Exception {
		String tempSalt = Long.toHexString(new Random().nextLong());
		this.salt = StringUtils.getBytesUtf8(tempSalt);
		byte[] tempSaltPassword = new byte[this.salt.length + password.length];
		System.arraycopy(this.salt, 0, tempSaltPassword, 0, this.salt.length);
		System.arraycopy(password, 0, tempSaltPassword, this.salt.length, password.length);
		MessageDigest md = MessageDigest.getInstance("sha-256");
		md.reset();
		md.update(tempSaltPassword);
		byte[] digested = md.digest(tempSaltPassword);
		return digested;
		// byte[] mdb = DigestUtils.sha256(tempSaltPassword);
		// return mdb;
	}

Then records are stored like this:

stmt = conn.getConnection().createStatement();
		stmt.execute("insert into " + tableName + " (login, hash, salt) values ('" + login +"', '" + hash + "', '" + salt + "')");

This bit works fine. At least I am convinced so.
Once I store the digests in a DB I use this method to compare them:

public boolean compareHashes(byte[] password, byte[] hash, byte[] salt) throws Exception {
		byte[] tempSaltPassword = new byte[password.length + salt.length];
		System.arraycopy(salt, 0, tempSaltPassword, 0, salt.length);
		System.arraycopy(password, 0, tempSaltPassword, salt.length, password.length);
		MessageDigest md = MessageDigest.getInstance("sha-256");
		md.reset();
		md.update(tempSaltPassword);
		byte[] digested = md.digest(tempSaltPassword);
		return md.isEqual(digested, hash);
	}

Hash and salt arguments being passed to the compareHashes() are the ones retrieved from a database with:

public byte[] selectUserPassword(String login) throws IOException {
		byte[] hash = null;
		try {
			stmt = new DBCon().getConnection().createStatement();
			ResultSet result = stmt.executeQuery("select hash from " + tableName + " where login = '" + login + "'");
			if (result.next()) {
				Clob clob = result.getClob("hash");
				hash = IOUtils.toByteArray(clob.getAsciiStream());
				for (byte b : hash) {
					System.out.print(b);
				}
				result.close();
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hash;
	}

and also:

public byte[] selectUserSalt(String login) throws IOException {
		byte[] salt = null;
		try {
			stmt = new DBCon().getConnection().createStatement();
			ResultSet result = stmt.executeQuery("select salt from " + tableName + " where login = '" + login + "'");
			if (result.next()) {
				Clob clob = result.getClob("salt");
				salt = IOUtils.toByteArray(clob.getAsciiStream());
				result.close();
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return salt;
	}

Clearly I am doing something wrong with getting data from Clobs. And this:

salt = IOUtils.toByteArray(clob.getAsciiStream());

is the only way I coud come up with to convert the clob back to array, which isn't working like i would like it to. I thought byte[] arrays passed were binary and since clobs are? also binary it would work with out additional conversions.
Do any of you have any ideas/suggestions/libraries which would be able to convert the clobs to what I want?

[Paul Clapham]

Philip Fry wrote:since clobs are? also binary

CLOB: Character Large Object

BLOB: Binary Large Object

[Philip Fry]

I was under the impression that blobs were for files like media files or pictures? Also I am not able to insert to Blob type fields things like: 81-88108101-39-95348102-413-2318-44-95-4029-92-11-53115-124-89-89080-102126888411531
because an exception is being thrown:

java.sql.SQLSyntaxErrorException: Columns of type 'BLOB' cannot hold values of type 'CHAR'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
	at db.InsertStmt.insertUsers(InsertStmt.java:15)
	at gui.CreateAccPanel$1.actionPerformed(CreateAccPanel.java:65)
	at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2012)
	at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2335)
	at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:404)
	at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:253)
	at java.awt.Component.processMouseEvent(Component.java:6268)
	at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
	at java.awt.Component.processEvent(Component.java:6033)
	at java.awt.Container.processEvent(Container.java:2045)
	at java.awt.Component.dispatchEventImpl(Component.java:4629)
	at java.awt.Container.dispatchEventImpl(Container.java:2103)
	at java.awt.Component.dispatchEvent(Component.java:4455)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4633)
	at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4297)
	at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4227)
	at java.awt.Container.dispatchEventImpl(Container.java:2089)
	at java.awt.Window.dispatchEventImpl(Window.java:2517)
	at java.awt.Component.dispatchEvent(Component.java:4455)
	at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:649)
	at java.awt.EventQueue.access$000(EventQueue.java:96)
	at java.awt.EventQueue$1.run(EventQueue.java:608)
	at java.awt.EventQueue$1.run(EventQueue.java:606)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:105)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:116)
	at java.awt.EventQueue$2.run(EventQueue.java:622)
	at java.awt.EventQueue$2.run(EventQueue.java:620)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:105)
	at java.awt.EventQueue.dispatchEvent(EventQueue.java:619)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:275)
	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:200)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:190)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:185)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:177)
	at java.awt.EventDispatchThread.run(EventDispatchThread.java:138)
Caused by: java.sql.SQLException: Columns of type 'BLOB' cannot hold values of type 'CHAR'. 
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
	... 46 more
Caused by: ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR'. 
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(Unknown Source)
	at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
	... 40 more

And it works with clobs type fields.

[Paul Clapham]

Philip Fry wrote:I was under the impression that blobs were for files like media files or pictures? Also I am not able to insert to Blob type fields things like: 81-88108101-39-95348102-413-2318-44-95-4029-92-11-53115-124-89-89080-102126888411531
because an exception is being thrown:

Blobs are -- just as their name says -- Binary Large Objects. It's true that the files you mention are binary and can be large, but it doesn't follow from that statement that you can't use Blobs for anything else.

And no, you can't put character data into them. You have to put bytes into them. Naturally you can't do that if you're constructing your SQL statements in a String (I can't believe you're doing all of this login security and then using a technology which is susceptible to SQL injection attacks). You can put bytes into them if you use a PreparedStatement and then use its setBlob() or setBinaryStream() method.

[Philip Fry]

Thank you very much good sir.