Insert marshall'd data into mysql


#1

Hey all… what methods can I use to safely insert Ruby marshall’d data
into mysql?

Thanks
Aaron


#2

On 6/25/07, Aaron S. removed_email_address@domain.invalid wrote:

Hey all… what methods can I use to safely insert Ruby marshall’d data
into mysql?

Use a BLOB column. MySQL treats BLOB columns as 8 bit binary data.
TEXT will also work, but there are a number of considerations (
http://dev.mysql.com/doc/refman/5.0/en/blob.html ). Since Marshalled
data is just regular 8 bit data, you can just throw it in there. If
you were /really/ worried / paranoid about throwing full 8 bit data,
non printable characters and all, into a database, you could Base64
encode and decode.

Cheers,
Peter C.
http://www.rubyinside.com/


#3

Peter C. wrote:

On 6/25/07, Aaron S. removed_email_address@domain.invalid wrote:

Hey all… what methods can I use to safely insert Ruby marshall’d data
into mysql?

Use a BLOB column. MySQL treats BLOB columns as 8 bit binary data.
TEXT will also work, but there are a number of considerations (
http://dev.mysql.com/doc/refman/5.0/en/blob.html ). Since Marshalled
data is just regular 8 bit data, you can just throw it in there. If
you were /really/ worried / paranoid about throwing full 8 bit data,
non printable characters and all, into a database, you could Base64
encode and decode.

Cheers,
Peter C.
http://www.rubyinside.com/

Makes sense with blobs. What about in the actual insert statments? EX:

someObj= {:one => ‘two’, :three => ‘four’}
d = Marshal.dump(someObj)
@con.query(“INSERT INTO user_data SET marshal=’#{d}’”);

That will produce an error because of some of the characters in the
marshal string…


#4

Peter C. wrote:

On 6/25/07, Aaron S. removed_email_address@domain.invalid wrote:

non printable characters and all, into a database, you could Base64
@con.query(“INSERT INTO user_data SET marshal=’#{d}’”);

That will produce an error because of some of the characters in the
marshal string…

Use parameter binding. Not sure what library you’re using, but with
Ruby DBI it’d be something like this:

@handle.do(“INSERT INTO user_data SET marshal=?”, d)

See if the same works on your setup. Parameter binding should be
supported by all decent database libraries, so there’ll be a way to do
it. If not, then there has to be an escaping/quotation method to get
the data in :wink:

Cheers,
Peter C.
http://www.rubyinside.com/

I’m using the mysql library for Ruby. Not sure about the paramter
binding… but Base64.b64encode and Base64.decode64 works fine.

Thanks


#5

On 6/25/07, Aaron S. removed_email_address@domain.invalid wrote:

non printable characters and all, into a database, you could Base64
@con.query(“INSERT INTO user_data SET marshal=’#{d}’”);

That will produce an error because of some of the characters in the
marshal string…

Use parameter binding. Not sure what library you’re using, but with
Ruby DBI it’d be something like this:

@handle.do(“INSERT INTO user_data SET marshal=?”, d)

See if the same works on your setup. Parameter binding should be
supported by all decent database libraries, so there’ll be a way to do
it. If not, then there has to be an escaping/quotation method to get
the data in :wink:

Cheers,
Peter C.
http://www.rubyinside.com/