[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

How to sanitize sql-column names?

Jan Lühr

5/23/2009 2:15:00 PM

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
4 Answers

Michael Jackson

5/23/2009 2:27:00 PM

0

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 <usenet@stephan.homeunix.net> 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
>
>

Jan Lühr

5/23/2009 4:28:00 PM

0

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 Jackson

5/24/2009 4:35:00 AM

0

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 <usenet@stephan.homeunix.net> =
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
>
>

Brian Candler

5/24/2009 9:31:00 AM

0

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-....