MySQL function using Rails/ActiveRecord

Question

I have a problem in using Rails / ActiveRecord.

I want to insert record with MySQL function, for example GeomFromText('POINT(1 1)'). Using ActiveRecord normally, these functions are quoted automatically. I want not to quote these values.

Model.create(geo: GeomFromText('POINT(1 1)'))

this ActiveRecord statement will generate following SQL

INSERT INTO `Model` (`geo`) VALUES ('GeomFromText(\'POINT(1 1)\')')

It may be easy to use raw SQL, but I want to use ActiveRecord because my Model set several callbacks include self table.

How can use MySQL function with ActiveRecord statement?

you want to store the value returned from the mysql function instead of the quoted text? — Midhun Krishna
– Midhun Krishna, Commented Jul 7, 2014 at 9:20
in this case, not need to filter mysql function. but the case in using mysql geometry type, statement of GeomFromText must contain. but ActiveRecord normally quote this and mysql don't recognize as function. — keijisan55
– keijisan55, Commented Jul 7, 2014 at 9:33

admgc · Accepted Answer · 2017-08-18 21:42:49Z

Summary

You can't by design; this behavior is important for preventing SQL injection attacks. You would need to explicitly execute raw SQL in conjunction with ActiveRecord.

Details

As you saw, the SQL statement gets interpolated as a string by design, which doesn't do what you want (Rails ~> 4.0):

> Country.create(name: 'LOWER("CANADA")')
  => SQL (0.3ms)  INSERT INTO `Country` (`Name`) VALUES ('LOWER(\"CANADA\")')

Nor can you use the same tricks that would work for the .where method:

> Country.create(["name = LOWER(:country)", { country: 'CANADA' }])
  => ArgumentError: When assigning attributes, you must pass a hash as an argument.

You would need to execute arbitrary SQL first to get the proper value, then make another SQL call via ActiveRecord to achieve your callback:

> Country.create( name: ActiveRecord::Base.connection.execute(%q{ SELECT LOWER('CANADA') }).first[0] )
  =>     (0.3ms)  SELECT LOWER('CANADA')
  => SQL (0.3ms)  INSERT INTO `Country` (`Name`) VALUES ('canada')

That said, it's probably cleaner to re-implement the SQL function at the application layer instead of the DB layer (unless you've got a really complex SQL function).

Collectives™ on Stack Overflow

MySQL function using Rails/ActiveRecord

1 Answer 1

Summary

Details

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Summary

Details

Comments

Your Answer

Sign up or log in

Post as a guest

Related