How to sanitize sql-column names?

Discussion in 'Ruby' started by Jan Lühr, May 23, 2009.

  1. Jan Lühr

    Jan Lühr Guest

    Hello,

    I'm developing a search functionality (as part of a RoR-App) and I was
    wonderinger: Is there a way to sanitize column-names for security?

    For values, there are prepared statements like:

    Address.find:)all, :conditions => ['last_name LIKE ?',"Luehr" ])

    But for column-names, it doesn't work:
    Address.find:)all, :conditions => ['? LIKE ?',"last_name","Luehr" ])

    Creates:
    SELECT * FROM `addresses` WHERE ('last_name' LIKE 'Luehr')
    (last_name is uses as a string here)

    I looked for escaping methods but I just got DBMS specfic ones like
    Mysql::escape_string()

    Do you know a generic escaping method?

    Thanks in advance,
    Keep smiling
    yanosz
     
    Jan Lühr, May 23, 2009
    #1
    1. Advertising

  2. You could do something like this:

    Address.find:)all, :conditions =3D> ['? LIKE ?',
    Address.connection.quote_column_name("last_name"), "Luehr" ])

    It's a bit more verbose, but I think it should work.

    Michael

    On Sat, May 23, 2009 at 8:15 AM, Jan L=FChr <> w=
    rote:
    > Hello,
    >
    > I'm developing a search functionality (as part of a RoR-App) and I was
    > wonderinger: Is there a way to sanitize column-names for security?
    >
    > For values, there are prepared statements like:
    >
    > Address.find:)all, :conditions =3D> ['last_name LIKE ?',"Luehr" ])
    >
    > But for column-names, it doesn't work:
    > Address.find:)all, :conditions =3D> ['? LIKE ?',"last_name","Luehr" ])
    >
    > Creates:
    > SELECT * FROM `addresses` WHERE ('last_name' LIKE 'Luehr')
    > (last_name is uses as a string here)
    >
    > I looked for escaping methods but I just got DBMS specfic ones like
    > Mysql::escape_string()
    >
    > Do you know a generic escaping method?
    >
    > Thanks in advance,
    > Keep smiling
    > yanosz
    >
    >
     
    Michael J. I. Jackson, May 23, 2009
    #2
    1. Advertising

  3. Jan Lühr

    Jan Lühr Guest

    Hello,

    Michael J. I. Jackson schrieb:
    > You could do something like this:
    >
    > Address.find:)all, :conditions => ['? LIKE ?',
    > Address.connection.quote_column_name("last_name"), "Luehr" ])
    >
    > It's a bit more verbose, but I think it should work.


    ehm no:
    SELECT * FROM `addresses` WHERE ('`last_name`' LIKE 'Luehr')

    Keep smiling
    yanosz
     
    Jan Lühr, May 23, 2009
    #3
  4. Sorry, it was just a guess! Those place holders are obviously only for
    user values then. You'll just have to use quote_column_name to
    interpolate the string manually.

    Have fun,

    Michael

    On Sat, May 23, 2009 at 10:30 AM, Jan L=FChr <> =
    wrote:
    > Hello,
    >
    > Michael J. I. Jackson schrieb:
    >>
    >> You could do something like this:
    >>
    >> Address.find:)all, :conditions =3D> ['? LIKE ?',
    >> Address.connection.quote_column_name("last_name"), "Luehr" ])
    >>
    >> It's a bit more verbose, but I think it should work.

    >
    > ehm no:
    > SELECT * FROM `addresses` WHERE ('`last_name`' LIKE 'Luehr')
    >
    > Keep smiling
    > yanosz
    >
    >
     
    Michael J. I. Jackson, May 24, 2009
    #4
  5. Michael J. I. Jackson wrote:
    > Sorry, it was just a guess! Those place holders are obviously only for
    > user values then. You'll just have to use quote_column_name to
    > interpolate the string manually.


    Or keep it simple:

    def col(colname)
    raise ArgumentError, "Bad column name" unless colname =~ /\A\w+\z/
    colname
    end

    Address.find:)all, :conditions => ["#{col(c)} LIKE ?","Luehr" ])

    Personally I would be uncomfortable allowing users to query on
    absolutely any column, even one that I had not indexed or was perhaps
    used for internal or auditing purposes. So I would prefer:

    ALLOWED_COLS = {
    'first_name' => true,
    'last_name' => true,
    }.freeze
    def col(colname)
    raise ArgumentError, "Bad column name" unless
    ALLOWED_COLS[colname]
    colname
    end
    --
    Posted via http://www.ruby-forum.com/.
     
    Brian Candler, May 24, 2009
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,286
  2. =?iso-8859-1?Q?Nils=20Hedstr=f6m?=

    Sanitize/scrub user HTML-code

    =?iso-8859-1?Q?Nils=20Hedstr=f6m?=, Apr 5, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    501
    =?iso-8859-1?Q?Nils=20Hedstr=f6m?=
    Apr 5, 2006
  3. Brad Baker

    Best way to sanitize user input?

    Brad Baker, Sep 24, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    12,929
    Brad Baker
    Sep 25, 2006
  4. Philipp

    Sanitize file name

    Philipp, Oct 25, 2007, in forum: Java
    Replies:
    18
    Views:
    1,146
    Philipp
    Oct 26, 2007
  5. Andreas S.
    Replies:
    6
    Views:
    1,197
    Quintus
    Oct 12, 2010
Loading...

Share This Page