# Insert marshall'd data into mysql

**URL:** <https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665>\
**Category:** Ruby\
**Created:** [June 25, 2007, 2:08am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665 "2007-06-25T02:08:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ktheory](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/e480ec/32.png) [@ktheory](https://www.ruby-forum.com/u/ktheory)\
**Post date:** [June 25, 2007, 2:08am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665/1 "2007-06-25T02:08:35Z")

</div>

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

Thanks  
Aaron

---

<div class="post-metadata">

**Author:** ![ktheory](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/e480ec/32.png) [@ktheory](https://www.ruby-forum.com/u/ktheory)\
**Post date:** [June 25, 2007, 2:17am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665/2 "2007-06-25T02:17:20Z")

</div>

On 6/25/07, Aaron S. [removed\_email\_address@domain.invalid](mailto: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](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.

> **[Ruby Inside: The Ruby Blog](http://www.rubyinside.com/)**
>
> The biggest Ruby and Rails blog since 2006 - tutorials, news, and lots more

---

<div class="post-metadata">

**Author:** ![ktheory](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/e480ec/32.png) [@ktheory](https://www.ruby-forum.com/u/ktheory)\
**Post date:** [June 25, 2007, 2:24am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665/3 "2007-06-25T02:24:47Z")

</div>

Peter C. wrote:

> On 6/25/07, Aaron S. [removed\_email\_address@domain.invalid](mailto: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](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/](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…

---

<div class="post-metadata">

**Author:** ![ktheory](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/e480ec/32.png) [@ktheory](https://www.ruby-forum.com/u/ktheory)\
**Post date:** [June 25, 2007, 4:13am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665/4 "2007-06-25T04:13:03Z")

</div>

Peter C. wrote:

> On 6/25/07, Aaron S. [removed\_email\_address@domain.invalid](mailto: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 😉
> 
> Cheers,  
> Peter C.  
> [http://www.rubyinside.com/](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

---

<div class="post-metadata">

**Author:** ![ktheory](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/e480ec/32.png) [@ktheory](https://www.ruby-forum.com/u/ktheory)\
**Post date:** [June 25, 2007, 4:10am UTC](https://www.ruby-forum.com/t/insert-marshalld-data-into-mysql/107665/5 "2007-06-25T04:10:28Z")

</div>

On 6/25/07, Aaron S. [removed\_email\_address@domain.invalid](mailto: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 😉

Cheers,  
Peter C.

> **[Ruby Inside: The Ruby Blog](http://www.rubyinside.com/)**
>
> The biggest Ruby and Rails blog since 2006 - tutorials, news, and lots more