Inserting byte[] into MySQL

[porchrat]

Hi all

I've been working at this for hours and read all sorts of stuff of the web and I just can't seem to figure out how to do this.

I have 2 blob fields in my MySQL table that I'm trying to insert the byte arrays into. When I try to insert it I get an SQL syntax Exception:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[B@35d9dc39, [B@72093dcd)' at line 1

This is how I try to insert it:

String sel = "INSERT INTO config.user (name, password) VALUES (" + usrDigest + ", " + pswdDigest + ")";

Statement stmt = conn.createStatement();
stmt.execute(sel);

Obviously there are some try statements etc. hanging around that I have removed for easy reading and this is just a simple version of what I plan to do as later I'm going to query the DB for the blobs and compare them to inputs but I don't get what I'm doing wrong here with this insert. It looks as though MySQL is having a problem with the contents of the blob. Would a prepared statement make a difference or am I just not handling this byte array properly?

Just as a bit of background: the byte arrays are the result of SHA-1 digests. I'm trying to make a simple user authorisation system with one-way hashing.

[masijade]

PreparedStatement see the tutorials.

[porchrat]

PreparedStatement see the tutorials.

Yes that is what I was thinking. If I use a PreparedStatement it won't try to pass the byte array as a String. Before you posted this I had already started working on it. Hopefully that does the trick.

I will report back with my results. Thanks :D

[masijade]

Aside from the fact that you never cobble together statements like that (at least values, table names, column names, etc, okay, but not values) it leads to too much trouble to make it worthwhile.

[porchrat]

Aside from the fact that you never cobble together statements like that (at least values, table names, column names, etc, okay, but not values) it leads to too much trouble to make it worthwhile.

Yes I know I shouldn't be patching statements together. It is a habit I'm breaking myself of. I'm already having to go through code and undo it. :o

[porchrat]

OK sorted it out. Using the PreparedStatement.setBytes(int, byte[]) method solved my problem.

Thanks so much for the input masijade :)

Now I'm off to change the rest of my statements to mirror this.