Forum: Ruby How to sanitize sql-column names?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jan Lühr (Guest)
on 2009-05-23 18:16
(Received via mailing list)
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
Michael J. I. Jackson (Guest)
on 2009-05-23 18:27
(Received via mailing list)
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.

Michael
Jan Lühr (Guest)
on 2009-05-23 20:31
(Received via mailing list)
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
Michael J. I. Jackson (Guest)
on 2009-05-24 08:35
(Received via mailing list)
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
Brian C. (Guest)
on 2009-05-24 13:30
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
This topic is locked and can not be replied to.