[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

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

Ting Chang

5/29/2008 11:10:00 PM

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
--
Posted via http://www.ruby-....
10 Answers

Todd Benson

5/30/2008 12:04:00 AM

0

On Thu, May 29, 2008 at 6:09 PM, Ting Chang <aumart@gmail.com> wrote:
> 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

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

5/30/2008 6:04:00 AM

0

HI,

On Fri, May 30, 2008 at 9:04 AM, Todd Benson <caduceass@gmail.com> 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.
http://en.wikipedia.org/wiki/SQL...

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

5/30/2008 12:26:00 PM

0

Ting Chang 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
--
Posted via http://www.ruby-....

Ryan Lewis

5/30/2008 1:34:00 PM

0

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
--
Posted via http://www.ruby-....

Phillip Gawlowski

5/30/2008 4:38:00 PM

0

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

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

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

Todd Benson

5/30/2008 5:31:00 PM

0

On Fri, May 30, 2008 at 1:04 AM, KUBO Takehiro <kubo@jiubao.org> wrote:
> This is insecure.
> http://en.wikipedia.org/wiki/SQL...
>
> 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

5/30/2008 9:01:00 PM

0

On Fri, May 30, 2008 at 11:37 AM, Phillip Gawlowski
<cmdjackryan@googlemail.com> wrote:
> -----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

5/30/2008 10:50:00 PM

0

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

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

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

5/30/2008 11:05:00 PM

0

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 Gilcher wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On May 30, 2008, at 6:37 PM, Phillip Gawlowski 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 Gilcher
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.8 (Darwin)
>
> iEYEARECAAYFAkhAhKIACgkQJA/zY0IIRZaEsQCeLig1V1IQeJVRcvf2A194pCw7
> vW4AniG9q9dCLwTxChvfAQm9tooTjpqn
> =fp2m
> -----END PGP SIGNATURE-----

--
Posted via http://www.ruby-....

David Masover

6/2/2008 3:18:00 AM

0

On Friday 30 May 2008 12:30:59 Todd Benson wrote:
> On Fri, May 30, 2008 at 1:04 AM, KUBO Takehiro <kubo@jiubao.org> 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.