[Rails] SQL Injection Attacks

[David Heinemeier Hansson]

> The code of interest is in the sql.rb file, modules BasicBind (for
> binding) and BasicQuote (for quoting).  Driver writer can override or
> replace these services if they are not appropriate for their particular
> database.

Sounds like a good idea. I'd certainly be willing to adopt a patch that 
provided a method for using real bind variables. Preferably, it would 
coexist with the current sprintf formatting for backwards compatibility 
while taking over any ? for the binds.

Who's up for this?

> You might not be worried about SQL injection problems, but I attended a
> security forum yesterday and saw a demo of SQL injection.  By typing 
> "' OR
> 1=1" into the name field of a login screen, the user was able to log 
> in as
> a random user without using a password.  Granted, the demo was designed
> with this in mind, but it does point out the potential problems if we 
> are
> not careful.

This is indeed a serious risk. Currently, Active Record mitigates it 
through sprintf. But as Dave Thomas have just so passionately argued 
for, this can open a hole if you don't realize that the sprintf'ed 
string is inserted directly into the SQL. Meaning that this is 
dangerous:

   Person.find_first(["firm_id = %s", firm_id ])

Note the missing quotes around the %s, which makes it possible to give 
"1 OR 1=1" as the firm_id. This will return the first user in the 
system, which is often the admin account. That wouldn't be good. So you 
need to always be sure to quote %s parts or use %d. So here are two 
safe alternatives:

   Person.find_first(["firm_id = '%s'", firm_id ])
   Person.find_first(["firm_id = %d", firm_id ])

The last one will raise an ArgumentError if firm_id can't be converted 
to an integer ("23" can, but "xcv" can't), which is reasonable enough, 
but something that you need to keep in mind.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://macromates.com/       -- TextMate: Code and markup editor (OS X)
http://www.loudthinking.com/ -- Broadcasting Brain