help Problem uploading credential to DB
Hi,
I am trying to upload a public key cert in .pem format to a MySQL database, using JDBC. Having read the cert out of a file successfully, as a String, I pass that String to an upload method. Since I had problems just uploading the String (in the values clause it translated to nothing, and took away the closing ), causing a SQL exception; I converted it to a byte [] and tried to upload. Having been stuck on this problem for two days, and with the entire architecture dependent on storing the cert in .pem format in a SQL DB, any help would be much appreciated. The relevant code is:
public void uploadCredential(String userNameIn, String credIn) {
byte [] credArray = credIn.getBytes();
System.out
.print("INSERT INTO bmcert (bmusername, bmusercert) VALUES ("
+ "'" + userNameIn + "'" + "," + "'" + credArray + "'"
+ ")");
dbi.singleInsert("INSERT INTO bmcert (bmusername, bmusercert) VALUES ("
+ "'" + userNameIn + "'" + "," + "'" + credArray + "'" + ")");
}
The print statement produces:
INSERT INTO bmcert (bmusername, bmusercert) VALUES ('Joe Turney','[B@923e30')
which looks to be valid SQL, but the value for bmusercert is clearly not a real
cert.
Further down, I read it back in:
ResultSet rs = dbi
.singleQuery("SELECT * FROM bmcert WHERE bmusername =" + "'"
+ userNameIn + "'");
try {
while (rs.next()) {
String name = rs.getString("bmusername");
byte[] certArray= rs.getBytes("bmusercert");
String cert = new String(certArray);
Which gets me a null pointer exception when I instantiate cert {the italicized line.
Anyway, I've worked on it for a while and am out of ideas, so any help you can give me would be very much appreciated.
Thanks!
[1833 byte] By [
cup_joea] at [2007-11-15]
Pem files are binary files so should not be converted to a String using new String(pemFileContent). You should use Base64 or Hex encoding.
Also, the SQL
"INSERT INTO bmcert (bmusername, bmusercert) VALUES ("
+ "'" + userNameIn + "'" + "," + "'" + credArray + "'"
will not contain the bytes of credArray since the toString() method of credArray is being used and this does not use the content of the array.
Also, you should use a PreparedStatement.
Message was edited by:
sabre150
Hi sabre,
Thanks, for helping me with getting the pem into the DB, which now works using the Prepared Statement and storing the credential as a ByteArrayInputStream.
Unfortunately I also have to get the data out of the DB, and getBytes seems to be giving me null, so I looked for a method giving me back a ByteArrayInputStream or ByteArrayOutputStream. No luck. So I'm almost certainly just missing something, but I need to get this working andany help would be much appreciated. The relevant code is:
public void readUploadedCredential(String userNameIn) {
Connection con = dbi.getConnection();
ByteArrayInputStream bis = null;
PreparedStatement ps = null;
ResultSet rs = null;
byte [] cred = null;
try {
ps = con
.prepareStatement("SELECT bmusercert FROM bmcert");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("SQL query is: " + ps.toString());
try {
rs = ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (rs != null) {
try {
rs.beforeFirst();
cred = rs.getBytes("bmusercert");
System.out.println("cred at rs.getBytes is: " + cred);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Credential as bytes is: " + cred);
}
else {
System.out.println("credential from DB is null");
}
}
and the relevant section of the output is:
END CERTIFICATE--SQL query is: com.mysql.jdbc.PreparedStatement@337838: SELECT bmusercert FROM bmcertjava.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:703)
at CredentialUpload.readUploadedCredential(CredentialUpload.java:127)
at CredentialUpload.main(CredentialUpload.java:152)
Credential as bytes is: null
Any help is much appreciated!
1) Get rid of ALL that exception handling and just let the method return an SQLException. One should never catch exceptions UNLESS one is able to deal with the problem (even if that means wrapping the exception into one of your own and throwing it).
2) After executing the query the ResultSet cannot be null. If you don't believe me then check the Javadoc.
3) After executing the query the ResultSet is positioned before the first result so there is no need to explicitly do rs.beforeFirst();
again check the Javadoc.
4) After executing the query you need to move on to the first record before reading the value. The result set method next() does that and returns true if there is a next or false if there is not. You don't restrict the query in any way so that you will get ALL the credentials so to access them you need something likewhile (rs.next())
{
byte[] cred = rs.getBytes("bmusercert");
// Here you need to do something with the 'cred'.
...
}
I suspect you need to go though the JDBC tutorial,
Hi sabre,
Thanks for response. I have corrected the error of not looping through the result set, and apologize for not noticing and correcting it before posting the problem. Unfortunately credArray = rs.getBytes("bmusercert"); is giving me null. This prevents me from encoding it. In the past, I found a way to get the object id out, because it printed out 10 byes; but when I then encoded in Base64 I only got three bytes. Is there any reason the query should be giving me null? There are credentail values in the the DB {verified with the MySQL query browser) so the data is in there, and the query column is correct {the error message is that the column could not be found when the query column is wrong}.
The current form of the relevant method:
public void readUploadedCredentials(String userNameIn) {
Connection con = dbi.getConnection();
ByteArrayInputStream bis = null;
PreparedStatement ps = null;
ResultSet rs = null;
byte[] credArray = new byte[5000];
try {
ps = con.prepareStatement("SELECT bmusercert FROM bmcert");
// ps.(1, new byte[](credArray),
// credArray.length);
System.out.println("SQL query is: " + ps.toString());
rs = ps.executeQuery();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
while (rs.next()) {
credArray = rs.getBytes("bmusercert");
System.out.println("The resultset's columns are: " + rs.getMetaData());
System.out.println("credArray is: " + credArray);
String s = new sun.misc.BASE64Encoder().encode(credArray);
System.out.println("Cred is: " + credArray);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
The output:
SQL query is: com.mysql.jdbc.PreparedStatement@337838: SELECT bmusercert FROM bmcert
The resultset's columns are: com.mysql.jdbc.ResultSetMetaData@18558d2 - Field level information:
com.mysql.jdbc.Field@18a47e0
catalog: bmadmin
table name: bmcert
original table name: bmcert
column name: bmusercert
original column name: bmusercert
MySQL data type: 253
Data as received from server:
03 64 65 66 07 62 6d 61. d e f . b m a
64 6d 69 6e 06 62 6d 63d m i n . b m c
65 72 74 06 62 6d 63 65e r t . b m c e
72 74 0a 62 6d 75 73 65r t . b m u s e
72 63 65 72 74 0a 62 6dr c e r t . b m
75 73 65 72 63 65 72 74u s e r c e r t
0c 08 00 00 fa 00 00 fd. . . . . . . .
00 00 00 00 00 00. . . . . .
credArray is: null
Exception in thread "main" java.lang.NullPointerException
at java.io.ByteArrayInputStream.<init>(Unknown Source)
at sun.misc.CharacterEncoder.encode(Unknown Source)
at CredentialUpload.readUploadedCredentials(CredentialUpload.java:118)
at CredentialUpload.main(CredentialUpload.java:137)
Any help you can offer would be much appreciated.
1) Check that your database contains the data you expect. You don't need Java for that.
2) Get rid of all that exception handling. It makes your code impossible to read and does nothing for you.
3) Post the code that you use to insert the data and the code you use to extract the data. Make sure that the extraction is the reverse of the insert.
4) Post you code using the [code[] tags so it is readable.
Hi sabre150,
The input and output code, minus error handling, is below. Please note that the method of loading the data into the DB and the method of reading it out look to be the same:
// This method insert the credential to the db
public void uploadCredential(String userNameIn, String credIn) {
byte[] credArray = credIn.getBytes();
Connection con = dbi.getConnection();
PreparedStatement ps = null;
ps = con
.prepareStatement("INSERT INTO bmcert (bmusername, bmusercert) VALUES (?, ?)");
ps.setString(1, userNameIn);
ps.setBinaryStream(2, new ByteArrayInputStream(credArray),
credArray.length);
ps.executeUpdate();
}
// This methods reads the inserted credential for testing purposes
// Note that there is not ResultSet.getByteArrayInputStream method
// or for that matter ResultSet.getByteArrayOutputStream.
public void readUploadedCredentials(String userNameIn) {
Connection con = dbi.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
byte[] credArray = new byte[5000];
ps = con.prepareStatement("SELECT bmusercert FROM bmcert");
ps.setBinaryStream(0, new ByteArrayInputStream(credArray),
credArray.length);
System.out.println("SQL query is: " + ps.toString());
rs = ps.executeQuery();
while (rs.next()) {
credArray = rs.getBytes("bmusercert");
System.out.println("The resultset's columns are: " + rs.getMetaData());
System.out.println("credArray is: " + credArray);
// String s = new sun.misc.BASE64Encoder().encode(credArray);
System.out.println("Cred is: " + credArray);
}
}
}
and here is the output of one iteration through the ResultSet loop:
credArray is: [B@19fcc69
Cred is: [B@19fcc69
The resultset's columns are: com.mysql.jdbc.ResultSetMetaData@253498 - Field level information:
com.mysql.jdbc.Field@19c26f5
catalog: bmadmin
table name: bmcert
original table name: bmcert
column name: bmusercert
original column name: bmusercert
MySQL data type: 253
Data as received from server:
03 64 65 66 07 62 6d 61. d e f . b m a
64 6d 69 6e 06 62 6d 63d m i n . b m c
65 72 74 06 62 6d 63 65e r t . b m c e
72 74 0a 62 6d 75 73 65r t . b m u s e
72 63 65 72 74 0a 62 6dr c e r t . b m
75 73 65 72 63 65 72 74u s e r c e r t
0c 08 00 00 fa 00 00 fd. . . . . . . .
00 00 00 00 00 00. . . . . .
The value of bmusercert in the DB begins with:
Bag Attributes
and is clearly the beginning of an X.509 cert, not [B@19fcc69
or anything like it.
Thank you for your help so far, and anything you have to offer on this problem would be much appreciated.
Thanks!
You have done it again. You have made the assumption thatSystem.out.println("credArray is: " + credArray);
prints the contents of the arrays when it does not! It just prints a pseudo reference to the array.
You can use Arrays.toString(credArrays) to get a String representation of the contents.
