How do i replace actual value in the query with variables?

I try to set up a loop to put the data in the array into the oracle
data base by iteration.
Thus I put the variable into the sql query. I know this is no correct
for dynamic querying,
Can you give me some advise about how to do dynamic querying in ruby and
oracle?

Here is my wrong example. I hope this help you understand my question. I
really
appreciate your help…Thank you so much!!

for q in 0…4
s = Float(q)

conn.exec( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where RANK= s AND
playlist_id=432’)

end

Best Regards,
Erick

Ting C. wrote:

for q in 0…4
s = Float(q)

conn.exec( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where RANK= s AND
playlist_id=432’)

end

How about:

for q in 0…4
conn.exec(“UPDATE SCH_EVENT SET P_ID = 2444334 where RANK=#{q.to_f}
AND playlist_id=432”)
end

The main difference is the use of " instead of '.

Andreas

or even:
(0…4).each { |q| conn.exec( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where
RANK = #{q.to_f} AND playlist_id = 432’ ) }

<3 one liners

On Thu, May 29, 2008 at 6:09 PM, Ting C. [email protected] wrote:


Erick
You can expand within double quotes using “#{my_var}”, so maybe
something like…

conn.exec( “UPDATE SCH_EVENT SET P_ID = 2444334 where RANK = #{s} AND
playlist_id=432”)

Usually, capitalization in a SQL statement is reserved for keywords,
but everyone has their own style.

Todd

HI,

On Fri, May 30, 2008 at 9:04 AM, Todd B. [email protected]
wrote:

You can expand within double quotes using “#{my_var}”, so maybe
something like…

conn.exec( “UPDATE SCH_EVENT SET P_ID = 2444334 where RANK = #{s} AND
playlist_id=432”)

This is insecure.
SQL injection - Wikipedia

Good style is:
conn.exec( “UPDATE SCH_EVENT SET P_ID = 2444334 where RANK = :1 AND
playlist_id=432”, s)

The most efficient way in performance view is:

cursor = conn.parse( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where RANK=
:1 AND playlist_id=432’)
for q in 0…4
s = Float(q)
cursor.exec(s)
end
cursor.close

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ryan L. wrote:
| or even:
| (0…4).each { |q| conn.exec( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where
| RANK = #{q.to_f} AND playlist_id = 432’ ) }

Gotta love an SQL injection waiting to happen…


Phillip G.
Twitter: twitter.com/cynicalryan
Blog: http://justarubyist.blogspot.com

Don’t sacrifice clarity for small gains in “efficiency”.
~ - The Elements of Programming Style (Kernighan & Plaugher)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkhALVwACgkQbtAgaoJTgL9lzACgj3aKUfhMdkBthYSn/AUH5Phu
VhEAnRnvQYHp21rNB+2ouNBtK5ogkNtw
=/WtJ
-----END PGP SIGNATURE-----

On Fri, May 30, 2008 at 1:04 AM, KUBO Takehiro [email protected] wrote:

:1 AND playlist_id=432’)
for q in 0…4
s = Float(q)
cursor.exec(s)
end
cursor.close

How can you not still do insecure injection with this?

Todd

On Fri, May 30, 2008 at 11:37 AM, Phillip G.
[email protected] wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ryan L. wrote:
| or even:
| (0…4).each { |q| conn.exec( ‘UPDATE SCH_EVENT SET P_ID = 2444334 where
| RANK = #{q.to_f} AND playlist_id = 432’ ) }

Gotta love an SQL injection waiting to happen…

Umm… duh. It was probably an example. Anybody worth their grain of
salt would know you have to check the contents of q first.

Ask the guy before you throw rocks.

Todd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On May 30, 2008, at 6:37 PM, Phillip G. wrote:

Converting to a number type before using the value is injection safe.
I wonder how
you are going to convince #to_f (or #to_i )to return valid SQL code.

But: why don’t you just use prepared Statements?

Regards,
Florian G.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkhAhKIACgkQJA/zY0IIRZaEsQCeLig1V1IQeJVRcvf2A194pCw7
vW4AniG9q9dCLwTxChvfAQm9tooTjpqn
=fp2m
-----END PGP SIGNATURE-----

Yeah… this is just a example…
but #{} does convert the variables into the SQL query,
to_f and to_i works~~

Thanks guys~

-Erick

Florian G. wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On May 30, 2008, at 6:37 PM, Phillip G. wrote:

Converting to a number type before using the value is injection safe.
I wonder how
you are going to convince #to_f (or #to_i )to return valid SQL code.

But: why don’t you just use prepared Statements?

Regards,
Florian G.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkhAhKIACgkQJA/zY0IIRZaEsQCeLig1V1IQeJVRcvf2A194pCw7
vW4AniG9q9dCLwTxChvfAQm9tooTjpqn
=fp2m
-----END PGP SIGNATURE-----

On Friday 30 May 2008 12:30:59 Todd B. wrote:

On Fri, May 30, 2008 at 1:04 AM, KUBO Takehiro [email protected] wrote:

How can you not still do insecure injection with this?

Well, if you use single quotes for your SQL string, you can’t because
either
the SQL library will quote the other arguments properly, or they’ll be
sent
to the database via some other mechanism than inclusion in the string.

As another example: There’s nothing inherently insecure about:

eval(‘lambda { |x| do_something_with(x) }’).call(random_user_input)

There is, however, something very insecure about:

eval(“do_something_with(‘#{random_user_input}’)”)

The single easiest way to avoid SQL injection is to always include input
as
positional arguments, never directly in a string.