Rails: Where query syntax error

Question

I seriously cannot solve this syntax error:

PG::Error: ERROR:  syntax error at or near "07"
LINE 1: ...WHERE (post_id = 13 AND created_at > 2012-08-27 07:13:26) ...

This is what my query looks like:

Post.where(post_filter_params_where)

    def post_filter_params_where
      case params[:post_filter].to_i
      when 1
        "post_id = #{params[:id]}"
      when 2
        "post_id = #{params[:id]}"
      when 3
        time = 24.hours.ago.utc.to_s(:db)
        "post_id = #{params[:id]} AND created_at > #{time}"
      else
        "post_id = #{params[:id]}"
      end
    end

Would have been good if you posted your ruby code because this is a SQL syntax error. — Strelok
– Strelok, Commented Aug 28, 2012 at 7:17

xdazz · Accepted Answer · 2012-08-28 07:18:03Z

2

Use:

Post.where('post_id = ? AND created_at > ?', params[:id], 24.hours.ago.utc.to_s(:db))

The error is because you concat the where condition and missed the quote for the date.

answered Aug 28, 2012 at 7:18

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

flyingarmadillo Over a year ago

The problem with this is that I am actually returning the 'where' input from a function Post.where(some_function). I posted an edit.

flyingarmadillo · Accepted Answer · 2012-08-28 07:36:52Z

0

I needed to prepend the querys from the function with puts

    def post_filter_params_where
      case params[:post_filter].to_i
      when 1
        puts 'post_id = ?', params[:id]
      when 2
        puts 'post_id = ?', params[:id]
      when 3
        puts 'post_id = ?', params[:id], 24.hours.ago.utc.to_s(:db)
      else
        puts 'post_id = ?', params[:id]
      end
    end

answered Aug 28, 2012 at 7:36

flyingarmadillo

2,1395 gold badges21 silver badges38 bronze badges

Comments

lwe · Accepted Answer · 2012-08-28 07:41:10Z

Is there a specific reason why you need to use Post.where(some_function), because it would make more sense to provide a method like Post.filter(params[:post_filter], params[:id]) - if you need to reuse your filter method, just write a module and include it in all related models.

Also your current code is open to SQL injection attacks. Never use ruby string interpolation to create sql strings, see http://guides.rubyonrails.org/security.html#sql-injection

Anyway, here's some code :)

class Post < ActiveRecord::Base
  def self.filter(filter, post_id)
    if filter.to_i == 3
      where('post_id = ? AND created_at > ?', post_id, 24.hours.ago.utc)
    else
      where('post_id = ?', post_id)
    end
  end
end

Then instead of using Post.where(some_function) in your controller, simply use Post.filter(params[:post_filter], params[:id]). Bonus point, better use some constant to describe what 3 means.

Collectives™ on Stack Overflow

Rails: Where query syntax error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related