# Find\_by \_sql problems

**URL:** <https://www.ruby-forum.com/t/find-by-sql-problems/116474>\
**Category:** Rails\
**Created:** [October 5, 2007, 9:37pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474 "2007-10-05T21:37:06Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 5, 2007, 9:37pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/1 "2007-10-05T21:37:06Z")

</div>

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 W.

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 5, 2007, 9:48pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/2 "2007-10-05T21:48:50Z")

</div>

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 W. wrote:

> White W.
> 
> >

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 5, 2007, 10:04pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/3 "2007-10-05T22:04:03Z")

</div>

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 W.

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 6, 2007, 7:39pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/4 "2007-10-06T19:39:20Z")

</div>

On 5 Oct 2007, at 20:36, White W. wrote:

> 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?

Why use find\_by\_sql to do an update when that’s exactly what  
update\_all does ?

Fred

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 8, 2007, 2:14pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/5 "2007-10-08T14:14:44Z")

</div>

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.  
On Oct 6, 1:38 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 5, 2007, 10:26pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/6 "2007-10-05T22:26:51Z")

</div>

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 W. wrote:

> 

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![whitewolfaustria](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/bbce88/32.png) [@whitewolfaustria](https://www.ruby-forum.com/u/whitewolfaustria)\
**Post date:** [October 11, 2007, 2:13pm UTC](https://www.ruby-forum.com/t/find-by-sql-problems/116474/7 "2007-10-11T14:13:30Z")

</div>

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 W.