On 6/25/07, Aaron Smith <beingthexemplary / gmail.com> wrote:
> Peter Cooper wrote:
> > On 6/25/07, Aaron Smith <beingthexemplary / gmail.com> 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 Cooper
> > 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..

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 Cooper
http://www.rubyinside.com/