find_by _sql problems

I am creating a find by sql method to update various tables, but when the values get inserted into the script there are surrounded by single quotes, is there a way around this to get it to just enter in the string?

The command

MassEffect.find_by_sql(['update ? set ? = ? where id = ? ', a.table_name, me.attribute, me.new_value ,me.record])

and it issues this to my sql

update 'team_members' set 'permission_type_id' = '4' where id = 1 ;

and im looking to get

update team_members set permission_type_id = '4' where id = 1 ;

all help is appreciated

White Wizzard

You would need to hand craft the where clause leaving the ?'s in for the values only. The ?'s (placeholders) are only for values and will always get properly escaped.

White Wizzard wrote:

Hi all, The problem with just creating the where clause is that I need to do many updates on different tables is there any way i could force them first two ? to be escaped?

White Wizzard

This will work as long as you are certain that there is no possibility of a sql injection:

MassEffect.find_by_sql(["update #{a.table_name} set #{me.attribute} = ? where id = ? ", me.new_value ,me.record])

White Wizzard wrote:

Why use find_by_sql to do an update when that's exactly what update_all does ?

Fred

Update_all works on the main table in my project mass_effect, mass_effect contains a tablename, table_column, a record number and the new value of the table. mass_effect.update_all would update everything in mass effect table when I need to update everything based on what is in this table.

def mass_effect_update    MassEffect.find(:all).each{ |me|     a = Model.find(:first,:conditions => ['id = ?', me.model_id])     sql = "update #{a.table_name} set #{me.attribute} = #{me.new_value} where id = #{me.record}"     ActiveRecord::Base.connection.update(sql, "MASS EFFECT #{a.table_name} Update")       } end

this is what I finally came up with it basically does this . ..       for every record in the mass_effect table              get the corresponding model name using the model id              update that table at the attribute - value at the record getting that information from the mass_effect table              connect to the database and run the command, putting "MASS EFFECT tablename Update" in the logge       end of loop

I know that there is probably not the best way to do this and there is some code cleaning that needs to be done but thats what I have and it works.

Hi all,

I eneded up using Base.connect to directly talk to the database, and I did escape the values that needed to be "in the set and where clause" and left the ones that needed not to have quotes alone.

White Wizzard