There is no parameter $1 ...Sending SQL statement to Rails for Postgresql, what am I doing wrong?

Question

ActiveRecord::Base.connection.execute(
          "WITH numberofdays AS 
          (SELECT percent_change FROM asset_histories 
          WHERE date < $1
          AND asset_symbol = $2
          ORDER BY date DESC
          LIMIT $3)
          SELECT stddev_samp(percent_change) as stdev FROM numberofdays",
          [day,symbol,daystolimit])

Where day, symbol and daystolimit are variables assigned before the above code. They are assigned as such:

day = '2013-03-25'
symbol = 'AAPL'
daystolimit = 20

I don't want to use #{variable} because of the potential for malicious intent. As referenced in the title, my statement is returning

PGError ERROR: there is no parameter $1 LINE 1 ... WHERE date <$1 AND...

Using Postgresql 9.2 and rails 3.2

EDIT adding alternative solution I found here:

In addition to mu is too short where he uses connection.method(:quote), I couldn't find documentation on it so I came across another post where quoting was done as such:

a = ActiveRecord::Base.connection
a.execute(%Q{SELECT * FROM table WHERE id = #{a.quote(variable)}...})

mu is too short · Accepted Answer · 2013-03-26 17:31:27Z

1

From the fine (?) manual:

execute(sql, name = nil)

Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.

Note that the second parameter isn't for binding parameters, it is a name for (AFAIK) logging purposes.

ActiveRecord doesn't actually use bound parameters internally, it just does the quoting and escaping itself and builds a big SQL string for the database (cringe). You can mimic this behavior using the quote method and string interpolation:

q = ActiveRecord::Base.connection.method(:quote)
ActiveRecord::Base.connection.execute(%Q{
    WITH numberofdays AS 
    (SELECT percent_change FROM asset_histories 
    WHERE date < #{q[day]}
    AND asset_symbol = #{q[symbol]}
    ORDER BY date DESC
    LIMIT #{daystolimit})
    SELECT stddev_samp(percent_change) as stdev FROM numberofdays
})

You'll have to ensure that daystolimit is a number on your own (or quote it too and cast to an integer inside the SQL).

Alternatively, connect to the database using the raw pg interface and use real prepared statments.

edited Mar 26, 2013 at 17:31

answered Mar 25, 2013 at 23:19

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

8 Comments

Terence Chow Over a year ago

Ty, I saw others referencing that method as a way to pass parameters here and there and thought it was the correct way. I will look into the quote method more.

mu is too short Over a year ago

@Chowza: Numbered placeholders ($1, $2, ...) are the preferred way to use placeholders with PostgreSQL, the problem is that execute doesn't understand them so you have to do it by hand. If you talk to PostgreSQL through the low level pg interface, then you can use numbered placeholders. I don't know how to get the underlying PostgreSQL connection out of ActiveRecord though.

Terence Chow Over a year ago

Thanks. I haven't gotten to the level where I need to learn pg yet so I'm gonna keep everything high level :) By the way, the quotes should be in square brackets like such: #{q[symbol]}. Had to play around with it to figure that out. Thanks for the help and clarification!

Terence Chow Over a year ago

Also, I couldn't find documentation on connection.method(:quote) when trying to figure out the square brackets thing, and I came across connection.quote. I'm assuming there is no difference between the two? Would you know? Is there a best practice to use one over the other?

mu is too short Over a year ago

@Chowza: Thanks for spotting the bracket problem, thinking in the wrong language again :) The connection.method(:quote) trick just grabs a short reference to the quote method of connection, that way we don't have to a bunch of noise inside the SQL. See the method method for details. So my q and ActiveRecord::Base.connection.quote are the same thing but q is less noisy.

|

Collectives™ on Stack Overflow

There is no parameter $1 ...Sending SQL statement to Rails for Postgresql, what am I doing wrong?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related