Forum: Ruby insert marshall'd data into mysql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
5e8fd591aeccbffd6fc93c171ac4fa43?d=identicon&s=25 Aaron Smith (warhero)
on 2007-06-25 02:08
Hey all.. what methods can I use to safely insert Ruby marshall'd data
into mysql?

Thanks
Aaron
509e15b158eaac5ab3a5332a3f89d4a7?d=identicon&s=25 Peter Cooper (Guest)
on 2007-06-25 02:17
(Received via mailing list)
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/
5e8fd591aeccbffd6fc93c171ac4fa43?d=identicon&s=25 Aaron Smith (warhero)
on 2007-06-25 02:24
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..
509e15b158eaac5ab3a5332a3f89d4a7?d=identicon&s=25 Peter Cooper (Guest)
on 2007-06-25 04:10
(Received via mailing list)
On 6/25/07, Aaron Smith <beingthexemplary@gmail.com> 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 Cooper
http://www.rubyinside.com/
5e8fd591aeccbffd6fc93c171ac4fa43?d=identicon&s=25 Aaron Smith (warhero)
on 2007-06-25 04:13
Peter Cooper wrote:
> On 6/25/07, Aaron Smith <beingthexemplary@gmail.com> 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 Cooper
> 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
This topic is locked and can not be replied to.