How to sanitize sql-column names?

[Jan Lühr]

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.findall, :conditions => ['last_name LIKE ?',"Luehr" ])

But for column-names, it doesn't work:
Address.findall, :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]

You could do something like this:

Address.findall, :conditions =3D> ['? 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]

Hello,

You could do something like this:

Address.findall, :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]

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

Hello,

You could do something like this:

Address.findall, :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

 

[Brian Candler]

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.findall, :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