Saving Binary to MySQL

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

User avatar
jpardoe
Posts: 132
Joined: 31 Aug 2009, 20:54

Saving Binary to MySQL

Postby jpardoe » 20 Dec 2010, 09:51

I am trying to save a binary file (ByteArray) to my MySQL Database.

I have done tests that show it is being successfully created and sent to the server.

I have narrowed it down to the UPDATE statement of the SQL. It seems that when it is actually saved to the database using UPDATE, it changes somehow.

The database table is set up to receive a BLOB type.

Any thoughts on what's going wrong??

Here is a snippet of code:

Code: Select all

int pid = tileData.getInt("pid");
int sectX = tileData.getInt("sectX");
int sectY = tileData.getInt("sectY");
byte[] byteArray = tileData.getByteArray("tileData");

getExtension().trace(byteArray[0]); // traces the correct first value in the byteArray

if(user.getVariable("admin").getBoolValue()){
    getExtension().trace("updating tile data: " + byteArray.length); // traces the correct size.

    String updateSQL = "UPDATE tile_data " +
    "SET tileData = '" + byteArray + "' " +
    "WHERE planetID = '" + pid + "' " +
    "AND sectX = '" + sectX + "' " +
    "AND sectY = '" + sectY + "'";

    try {
        _dbManager.executeUpdate(updateSQL);
...
...
...



It's also worth mentioning that SOMETHING is being written to the database because the BLOB field's size changes. However the size does not reflect that of the binary data, and when reading the stored binary data the data seems to have changed.
tchen
Posts: 191
Joined: 11 Dec 2010, 14:14

Postby tchen » 20 Dec 2010, 17:08

My guess is that inlining byteArray like that is converting it back into text, which has some issues with encoding. Try using PreparedStatement.

Code: Select all

connection = dbManager.getConnection();
PreparedStatement stmt = connection.prepareStatement("
UPDATE tile_data SET tileData=(?)
WHERE planetID=? AND sectX=? AND sectY=?");

stmt.setBytes(1, byteArray);
stmt.setInt(2, planetID);
stmt.setInt(3, sectX);
stmt.setInt(4, sectY);

ResultSet res = stmt.executeQuery();
User avatar
jpardoe
Posts: 132
Joined: 31 Aug 2009, 20:54

Postby jpardoe » 20 Dec 2010, 17:18

Thank you so much! I've been struggling with this all day!

I've seen PreparedStatement used before - but don't know what it does exactly?

Can you enlighten me?

Thanks again, you really helped!
tchen
Posts: 191
Joined: 11 Dec 2010, 14:14

Postby tchen » 20 Dec 2010, 17:28

Actually, this page from the docs2x might help.

There apparently was a shorter version

Code: Select all

DBManager.executeQuery(String sql, Object[] params)


that would have worked too.

The PreparedStatement is useful when you're performing the same SQL over and over again. The DBMS normally recompiles any sent SQL each time you run query. The PreparedStatement makes it easier for the DBMS to pick out the precompiled version, avoiding that computation step. You can change the parameters each time you submit it, but the logical flow will already be set.

The executeQuery doesn't provide that level of optimization, but it does provide you the same parameter marshalling that PreparedStatement does.
User avatar
jpardoe
Posts: 132
Joined: 31 Aug 2009, 20:54

Postby jpardoe » 21 Dec 2010, 03:25

Thanks for the explanation!

In this forum post: viewtopic.php?t=8952 they implied that it also prevents SQL injection too.

Is that a secondary feature of PreparedStatement? Does it simply escape quotes or does it do more advanced prevention?
tchen
Posts: 191
Joined: 11 Dec 2010, 14:14

Postby tchen » 21 Dec 2010, 13:09

See http://www.owasp.org/index.php/SQL_Inje ... heat_Sheet for a good overview of injection defense.

But in summary, it goes beyond escaping because it processes the sql command flow without the parameter values first. This flow is immutable. Hence it closes the ability for an injector to add a DELETE or INSERT command in the midst of your query.

Just be sure to use the stmt.set* functions to bind variables, and not inline it in the constructor statement.

Cheers,
Ted
tchen
Posts: 191
Joined: 11 Dec 2010, 14:14

Postby tchen » 21 Dec 2010, 21:48

I think I might be losing my mind, but going through the javadocs today, I noticed:

IDBManager.executeQuery(String sql, Object[] params)

It beats all that typing of having to create and manage Connections. But alas, I don't see it in my jars.
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 22 Dec 2010, 07:03

The javadoc has been updated because we have launched a new update --> RC1B. We have sent a newsletter and now are adding notes all over the place :) You probably checked in the middle of the website update
Lapo
--
gotoAndPlay()
...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 48 guests