Ruby/Rails/PostgreSQL PGError: syntax error at or near

Question

I'm getting the error in the title when trying to select a cookie value from a table in a postgresql database, and I've no idea why. Selecting other fields in this table work fine.

Here's the line where it is breaking:

user=UniqueUser.find(:all, :select => 'DISTINCT visitor_id', :conditions=> "visitor_id=#{visitorid}")

The column is defined as character varying(255)

Here's the error:

187/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapt
ers/abstract_adapter.rb:219:in `log': PGError: ERROR:  syntax error at or near "
c5a" (ActiveRecord::StatementInvalid)
LINE 1: ...M "unique_users" WHERE (visitor_id=d5fb0ff2-319e-4c5a-b07c-a...

It seems like Rails should put quotes around the data field in the where clause.

I'm certainly not a Rails expert, so it could be something really simple that I am doing wrong, and appreciate any help.

Hi shingara, the only output that I see is what I pasted above. ...M "unique_users" WHERE (visitor_id=d5fb0ff2-319e-4c5a-b07c-a... There is probably a way to get the exact statement, but I don't know it, and Swanand solved my problem, so I guess I won't worry about it now. Thanks for your help. — Adrian Carr
– Adrian Carr, Commented Oct 28, 2010 at 15:36

Swanand · Accepted Answer · 2010-10-28 13:10:00Z

2

You aren't escaping your input properly. Try this:

user = UniqueUser.find(:all, :select => 'DISTINCT visitor_id', :conditions=> ['visitor_id = ?', visitorid])

answered Oct 28, 2010 at 13:10

Swanand

12.4k7 gold badges48 silver badges62 bronze badges

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

4 Comments

Ariejan Over a year ago

Congratulations, you have just exposed your app to SQL injection :)

Adrian Carr Over a year ago

Ariejan- If you have time to elaborate a bit, I'd appreciate it. I understand SQL injection, but not how it relates to this Rails example. Thanks!

Topher Hunt Over a year ago

The OP code has an injection vulnerability because the visitorid is injected directly into the SQL statement using #{ }. If some user somehow got the value 0 OR 1=1 into visitorid, your SQL statement would end up looking like this: SELECT visitor_id FROM visitors WHERE visitor_id = 1 OR 1=1, and the query would return a list of all user IDs in your system. Or worse, a more complex string could allow someone to execute a second query after the first, and get back any information they want. Prevent this by passing all variables to the SQL the Rails way, using question marks as shown above.

Swanand Over a year ago

@Ariejan : Can you explain how SQL injection is possible in this case? :conditions => escapes user input, hence the ?, think of it as prepared query. In fact, the conditions array is used to avoid SQL injection.

Collectives™ on Stack Overflow

Ruby/Rails/PostgreSQL PGError: syntax error at or near

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related