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

Discussion in 'Ruby' started by Ting Chang, May 30, 2008.

  1. Ting Chang

    Ting Chang Guest

    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
     
    Ting Chang, May 30, 2008
    #1
    1. Advertisements

  2. Ting Chang

    Todd Benson Guest

    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
     
    Todd Benson, May 30, 2008
    #2
    1. Advertisements

  3. HI,

    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
    ----------------------------------------------------------------------------------------------------
     
    KUBO Takehiro, May 30, 2008
    #3
  4. 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
     
    Andreas Warberg, May 30, 2008
    #4
  5. Ting Chang

    Ryan Lewis Guest

    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
     
    Ryan Lewis, May 30, 2008
    #5
  6. -----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-----
     
    Phillip Gawlowski, May 30, 2008
    #6
  7. Ting Chang

    Todd Benson Guest

    How can you not still do insecure injection with this?

    Todd
     
    Todd Benson, May 30, 2008
    #7
  8. Ting Chang

    Todd Benson Guest

    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
     
    Todd Benson, May 30, 2008
    #8
  9. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    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-----
     
    Florian Gilcher, May 30, 2008
    #9
  10. Ting Chang

    Ting Chang Guest

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

    Thanks guys~

    -Erick
     
    Ting Chang, May 31, 2008
    #10
  11. 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.
     
    David Masover, Jun 2, 2008
    #11
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.