Rails query to SQL statement

Question

I'm trying to write an write this:

Team.last.players.sum("goals")

erb:

SELECT SUM("players"."goals") 
FROM "players" 
WHERE "players"."team_id" = $1  [["team_id", 2]]

how to rewrite this so that I could use it in a method:

def sql_search
  sql = "SELECT SUM \"players\".\"goals\" FROM \"players\" WHERE \"players\".\"team_id\" = $1 [[\"team_id\", #{self.id}"
  connection.execute(sql);
end

keep getting this error:

PG::SyntaxError: ERROR:  syntax error at or near "."
LINE 1: SELECT SUM "players"."goals" FROM "players" WHERE "players"....

Any ideas would be appreciated

pangpang · Accepted Answer · 2015-05-20 16:23:20Z

2

You don't need to add \" in sql statement, just remove them.

def sql_search
    sql = "SELECT sum(goals) FROM players WHERE team_id = #{self.id};"
    connection.execute(sql);
end

edited May 20, 2015 at 16:23

answered May 20, 2015 at 15:28

pangpang

8,86915 gold badges67 silver badges99 bronze badges

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

1 Comment

Mauricio Gracia Gutierrez Over a year ago

This seems right but a little explanation could help

bideowego · Accepted Answer · 2016-04-01 04:42:52Z

0

Is there some reason that you want to hard code the SQL query? It's generally bad practice to use string interpolation to insert parameters to SQL queries because of SQL injection attacks. Instead it's recommended to use ActiveRecord's SQL query parameter binding like this:

user_input = 5
Player.where('team_id = ?', user_input).sum(:goals)

Basically what this does is insert the parameter 5 after sanitization. This means you're safe from attacks where a hacker attempts to insert arbitrary SQL into parameter variables attempting to return sensitive data or delete data entirely!

answered Apr 1, 2016 at 4:42

bideowego

4511 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

Rails query to SQL statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related