CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Richardlonesteen
Richardlonesteen

Reputation: 594

Rails query to SQL statement

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

Upvotes: 2

Views: 461

Answers (2)

bideowego
bideowego

Reputation: 451

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!

Upvotes: 0

pangpang
pangpang

Reputation: 8821

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

Upvotes: 2

Related Questions