How to sanitize sql-column names?

Ruby
1

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

2

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

3

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

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

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