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

[Ting Chang]

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

 

[Todd Benson]

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

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

 

[KUBO Takehiro]

HI,

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.
http://en.wikipedia.org/wiki/SQL_injection

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

 

[Andreas Warberg]

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

 

[Ryan Lewis]

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

 

[Phillip Gawlowski]

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

Ryan Lewis 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 Gawlowski
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-----

 

[Todd Benson]

This is insecure.
http://en.wikipedia.org/wiki/SQL_injection

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

How can you not still do insecure injection with this?

Todd

 

[Todd Benson]

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

Ryan Lewis 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

 

[Florian Gilcher]

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

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

Ryan Lewis 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..

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 Gilcher
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

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

 

[Ting Chang]

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

Thanks guys~

-Erick

 

[David Masover]

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.