Re: BLOBs, MySQL, hex. Oh my

FROM : Jens Alfke
DATE : Thu May 08 06:13:22 2008

On 7 May '08, at 5:16 PM, Ben Einstein wrote:

> I'm using Serge Cohen's MCPKit (aka SMySQL), which has a nifty 
> little function to convert NSData to a MySQL-legal NSString.


The "B" in "BLOB" stands for Binary, meaning that you don't have to 
encode the data in any way; the database should just accept raw bytes. 
I've never used MySQL, but the SQLite API just lets you pass in a 
pointer to the raw data. You really don't want to generate a full 
query string for every query you run. Instead, you generate a query 
template with "?"s in it for parameters, compile that, and then call 
another API to fill in the parameter values before running it. This is 
really convenient, faster, and prevents nasty quoting errors that lead 
to SQL injection attacks.

If for some reason you can't actually do that (in which case it isn't 
really a blob at all), then you should at the very least use base64 
instead of hex to encode the data; it'll cut the size down by a third. 
You should also use an existing library to do the encoding/decoding 
rather than rolling your own, since that's going to be better-tested 
and probably faster. (I know there's a base64 function in libCrypto.) 
But base64 may emit quotes, so you'll have to run this through 
whatever function MySQL uses to safely quote strings.

—Jens