Suppose you want to authenticate user from the login/password entered on the web from [ruby] user = User.first(:conditions => "login = '#{ login }' AND password = '#{ password }'") [/ruby] Such authentication code as we know can be easily fooled with the SQL injection. Thankfully in Rails there are parameterized queries and the above can be rewritten in at least 3 injection safe ways [ruby] User.find_by_login_and_password(login, password) # or User.first(:conditions => ["login = ? AND password = ?", login, password]) # or User.first(:conditions => ["login = :login AND password = :password", { :login => login, :password => password }]) [/ruby]
Parameterized queries cover most of the cases but what if you have large query with multiple parameters? You can quickly lose track of all the question marks and order in which parameters appear. Most likely you will prefer the latter, hash-based notation.
Side note: This stuff is actually analyzed in depth by Jim Weirich in his The Building Blocks of Modularity presentation where he talks about the connascence in software.
Back to our topic what we really want is just to write SQL queries
injecting properly quoted values where needed. In other words we would like
to be able to customize the way string interpolation works. In cool
languages like Haskell this concept is natively supported and called
Quasiquotation.
While not really on par with such rigorous achievements it can
be simulated everywhere where eval is available.
Here is for example
the solution
from the
Google Caja
team.
There is eval in Ruby and there is also one peculiar feature
about it. You can pass binding to it, and when the
block is created it's binding is captured and therefore can be
used from anywhere (providing access to the lexical scope captured by the
block). Let's illustrate that
[ruby]
ruby-head > def m
local = 42
proc {} # return empty block to get binding from it later
end
ruby-head > eval("local", m.binding) # binding gives access to the scope where block was created
=> 42
[/ruby]
Using that feature we can hide eval and implement our own
safe interpolation routines like I did in
safe_interpolate.rb.
Use it like
[ruby]
ruby-head > require 'dolzenko/safe_interpolate'
ruby-head > include SafeInterpolate
ruby-head > login = "' or 1=1"
ruby-head > sql_interpolate { 'login = #{ login }' }
=> "login = ''' or 1=1'" # single quote got turned into double and the whole expression is quoted itself
[/ruby]
HTML and URI escaping are also provided
[ruby]
ruby-head > content = ""
ruby-head > html_interpolate { '
#{ content }
' } => "<script>alert(1)</script>
" # injection prevented! ruby-head > query = "&admin=1" ruby-head > uri_interpolate { 'http://example.com?q=#{ query }' } => "http://example.com?q=%26admin%3D1" [/ruby]
A type-based solution to the "strings problem": a fitting end to XSS and SQL-injection holes?
by Tom Moertel. This article got me interested in the subject.
Haskell features I'd like to see in other languages
by Tim Carstens. Check the «Quasi-quoting» section for collection of Haskell
quoting related links.