0
          ActiveRecord::Base.connection.execute
        ( "SELECT * FROM search_results(p_limit := 12,
         p_offset := 0,p_blocked_ids := '',p_following_ids 
        := '54,171,174',p_sort := 'latest',
        p_tags := '', p_search := '(O'neil)')" )

The special characters in the p_search param fail. I tried double quoting and $$ $$ quoting with but it's not solving the issue.

What is the proper way to escape special characters in this query?

Full error message:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "neil"
LINE 1: ...t := 'latest',         p_tags := '', p_search := '(O'neil)')
                                                                ^
: SELECT * FROM search_results(p_limit := 12,          p_offset := 0,p_blocked_ids := '',p_following_ids          := '54,171,174',p_sort := 'latest',         p_tags := '', p_search := '(O'neil)')
    from /Users//.gem/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
    from /Users//.gem/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
    from /Users//.gem/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
    from /Users//.gem/ruby/2.3.0/gems/activesupport-5.0.6/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
    from /Users//.gem/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
    from /Users//.gem/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execu

The suggestion from the assumed duplicate has not helped in this case and returns ', p_search := ''o''neil'')

Ayrad
  • 3,996
  • 8
  • 45
  • 86

1 Answers1

0

Try this: 

ActiveRecord::Base.connection.execute
        ( "SELECT * FROM search_results(p_limit := 12,
         p_offset := 0,p_blocked_ids := '',p_following_ids 
        := '54,171,174',p_sort := 'latest',
        p_tags := '', p_search := '(O\'neil)')" )

Notice the extra \ in (O\'neil)' near the end.

I think '(O'neil)' is being read by PostgreSQL and since there is an single quote after (O, the string ends there and it would complain about the syntax.

Vaibhav
  • 628
  • 6
  • 10