Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > How to sanitize sql-column names?

Reply
Thread Tools

How to sanitize sql-column names?

 
 
Jan Lühr
Guest
Posts: n/a
 
      05-23-2009
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
 
Reply With Quote
 
 
 
 
Michael J. I. Jackson
Guest
Posts: n/a
 
      05-23-2009
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 <(E-Mail Removed)> 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
>
>


 
Reply With Quote
 
 
 
 
Jan Lühr
Guest
Posts: n/a
 
      05-23-2009
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
 
Reply With Quote
 
Michael J. I. Jackson
Guest
Posts: n/a
 
      05-24-2009
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 <(E-Mail Removed)> =
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
>
>


 
Reply With Quote
 
Brian Candler
Guest
Posts: n/a
 
      05-24-2009
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/.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Ruby 1.9.2: How to sanitize text with invalid characters? Andreas S. Ruby 6 10-12-2010 07:29 AM
Sanitize file name Philipp Java 18 10-26-2007 08:35 AM
Best way to sanitize user input? Brad Baker ASP .Net 1 09-24-2006 11:32 PM
Sanitize/scrub user HTML-code =?iso-8859-1?Q?Nils=20Hedstr=f6m?= ASP .Net 0 04-05-2006 01:07 PM



Advertisments