How do you create prepared statements with the mysql2 gem?

Question

I've tried using google to answer this seemingly simple question, but to my surprise, it didn't help.

I have code in my rails application currently using the 'prepare' method with the mysql gem. On switching to mysql2, this breaks with the error:

undefined method `prepare' for #<Mysql2::Client::0.......

So I tried looking for a version of the 'prepare' method but this search has been unsuccessful so far. Can anyone help me out with this?

Edit: If this isn't possible, could anyone let me know if there's a way to simply parameterize my queries with something in the mysql2 library?

score 16 · Answer 1 · edited May 23 '17 at 10:29

16

UPDATE

As Ryan Rapp pointed out correctly, mysql2 now supports prepared statements. Following snippet is extracted from the readme:

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

Thanks Ryan!

Original Post

I found no such function either; neither in source nor in the documentation. Maybe the following snippet is a helpful replacement for your needs? (found in the documentation of mysql2 gem):

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

edited May 23 '17 at 10:29

Community

1
1

answered Mar 28 '12 at 11:51

rene_gr

489
4
12

I was hoping for a way to use the parametrized queries like I was earlier. "select * from my table where mycolumn1 = ?" and so on. Fingers crossed to find something like that. – Nikhil Mar 28 '12 at 13:14
Accepted this because there is no way to do it out of the box. Ended up wrapping it up with active record used outside rails in my ruby script. Thanks. – Nikhil Apr 30 '12 at 05:38
17

how incredibly disappointing for a modern database interface to not support parameterized queries – wmarbut Nov 30 '12 at 16:31
2

FYI, for those coming across this, it now supports prepared statements. See the [documentation](https://github.com/brianmario/mysql2). Will add syntax as a new answer. – Ryan Rapp Oct 28 '15 at 22:57
1

Thanks Ryan, changed the answer to reflect the change. – rene_gr Oct 30 '15 at 08:56

score 9 · Accepted Answer · answered Oct 28 '15 at 23:09

9

The mysql2 gem now supports prepared statements according to the documentation.

The syntax is as follows:

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

This was added with a merged pull request in June 2015.

answered Oct 28 '15 at 23:09

Ryan Rapp

1,583
13
18

2

Finally! It only took a few years. – Nikhil Oct 29 '15 at 02:48
1

This support is in mysql2 from 0.4.0+ – Matt Sanders May 18 '16 at 17:41

Louis St-Amour · Answer 3 · 2013-03-23T19:09:20.553

I swapped out to use https://github.com/tmtm/ruby-mysql instead of mysql2. I'm surprised this isn't a bigger deal-breaker for people using the mysql2 gem. I guess people who dig this deep into writing SQL have swapped to Postgresql?

In case others are having trouble with gem install ruby-mysql followed by require "mysql" where you get a Ruby error like 'read_eof_packet': packet is not EOF (Mysql::ProtocolError) the trick is to gem uninstall ruby-mysql and instead gem install ruby-mysql-ext (or use gem 'ruby-mysql-ext' in your Gemfile) which will swap out the Ruby implementation which isn't yet Ruby 2.0 compatible (or at least, didn't work for me) for simple C bindings.

To be clear, if you do require 'mysql' while both ruby-mysql-ext and ruby-mysql are installed, it will load the Ruby version. There may be a way to require within a specific gem, but I didn't have time to look it up.

score 1 · Answer 4 · answered Jul 02 '14 at 07:33

Yes, mysql2 adapter doesn't support binding till the current Rails 4.0. I'm being surprised! You could tell this by the code snip from ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/mysql2_adapter.rb

      def exec_query(sql, name = 'SQL', binds = [])
        result = execute(sql, name)
        ActiveRecord::Result.new(result.fields, result.to_a)
      end

      alias exec_without_stmt exec_query

      # Returns an ActiveRecord::Result instance.
      def select(sql, name = nil, binds = [])                                                                                                                      
        exec_query(sql, name)
      end

also this is helpful for you understanding:

(in ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb)

      # Returns an ActiveRecord::Result instance.
      def select_all(arel, name = nil, binds = [])
        if arel.is_a?(Relation)
          relation = arel
          arel = relation.arel                                                                                                                                     
          if !binds || binds.empty?
            binds = relation.bind_values
          end
        end

        select(to_sql(arel, binds), name, binds)
      end

That's it! And I guess I may turn to Postgres!!

score 0 · Answer 5 · answered Jan 03 '14 at 15:40

I'm also surprised that the prepare method is missing. Surely in a common ActiveRecord and Mysql2 setup, ActiveRecord must be escaping the strings rather then using libmysql, which I find a tiny bit worrying.

In the meantime, you can use https://github.com/brianmario/mysql2/tree/stmt

score 0 · Answer 6 · answered Jan 29 '14 at 13:57

0

Rails and the MySQL Active Record adaptor does not have any support for prepared statements as far as I know:

http://patshaughnessy.net/2011/10/22/show-some-love-for-prepared-statements-in-rails-3-1

This is because they actually don't have any use in speeding statements up and can actually slow things down due to MySQL's lack of query planning.

answered Jan 29 '14 at 13:57

David L

371
1
2
6

I'm not sure this is true any longer (or perhaps even then!) as you have to turn on server-side prepared statements manually, it's off by default. See this and other settings at https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration – Louis St-Amour Feb 27 '14 at 03:16

score 0 · Answer 7 · answered Sep 06 '14 at 13:21

0

You may also use mysql2-cs-bind gem: https://github.com/tagomoris/mysql2-cs-bind

answered Sep 06 '14 at 13:21

KITAITI Makoto

116
3

How do you create prepared statements with the mysql2 gem?

7 Answers7

Linked