Are you seeing that fancy live search system in the blog area? Did you ever realize it was not SQL Injection proof? I'm glad you don't, for it wasn't.
During a late night surfing on the web yesterday I came to read this post by Nuno Job, where the little introduction that he gave about SQL Injection caught my attention and made me go check the wikipedia page about it. Although I already knew what it was, I was looking for the usual inputs that could cause it.
So, I read it and went check my live search system with the char '. For my surprise a Rails error was output and I noticed I had a pretty bad security issue.
Let's see the insides of my Rails application so that you can see what went wrong and learn with it, as I did.
The problem was in the method of the live search in the controller. That system was supposed to search in the post's body and titles and for that I had code something like this:
@results = Post.find(:all, :conditions => "title LIKE '#{@searchphrase}' or body LIKE '#{@searchphrase}'")
Notice that little #{@searchphrase} thing? That's a huge mistake. Initially I had something like:
@results = Post.find(:all, :conditions => ["body LIKE ?", @searchphrase])
Which is fine and secure, but I ended up wanting to search in the post's titles too, and modified to the first version, which worked. I didn't even think about SQL Injection by that time. The ? makes the whole difference, for it escapes all the chars with meaning for SQL, preventing Injection. So, the correct solution would be:
During a late night surfing on the web yesterday I came to read this post by Nuno Job, where the little introduction that he gave about SQL Injection caught my attention and made me go check the wikipedia page about it. Although I already knew what it was, I was looking for the usual inputs that could cause it.
So, I read it and went check my live search system with the char '. For my surprise a Rails error was output and I noticed I had a pretty bad security issue.
Let's see the insides of my Rails application so that you can see what went wrong and learn with it, as I did.
The problem was in the method of the live search in the controller. That system was supposed to search in the post's body and titles and for that I had code something like this:
@results = Post.find(:all, :conditions => "title LIKE '#{@searchphrase}' or body LIKE '#{@searchphrase}'")
Notice that little #{@searchphrase} thing? That's a huge mistake. Initially I had something like:
@results = Post.find(:all, :conditions => ["body LIKE ?", @searchphrase])
Which is fine and secure, but I ended up wanting to search in the post's titles too, and modified to the first version, which worked. I didn't even think about SQL Injection by that time. The ? makes the whole difference, for it escapes all the chars with meaning for SQL, preventing Injection. So, the correct solution would be:
@results = Post.find(:all, :conditions => ["title LIKE ? OR body LIKE ?",@searchphrase,@searchphrase])
With the system like it was for five months, the SQL request that was done when you searched for something was this:
SELECT * FROM posts WHERE (title LIKE '%<what_you_typed>%' or body LIKE '%<what_you_typed>%')
So, this way, if you'd want to hack my site you'd only have to search this:
lol%'); DROP table posts; SELECT * FROM posts WHERE (title LIKE '%lol
The resulting request would be:
SELECT * FROM posts WHERE (title LIKE '%lol%');
DROP table posts;
SELECT * FROM posts WHERE (title LIKE '%lol%' or body LIKE '%lol%');
DROP table posts;
SELECT * FROM posts WHERE (title LIKE '%lol%')
Since this is a valid SQL command the result would be the successful deletion of the table posts of the database, which would instantly erase forever all my posts. Don't do mistakes like these when you're developing and just want everything to work, because the solution that just works could be a pretty serious security issue.
SELECT * FROM posts WHERE (title LIKE '%<what_you_typed>%' or body LIKE '%<what_you_typed>%')
So, this way, if you'd want to hack my site you'd only have to search this:
lol%'); DROP table posts; SELECT * FROM posts WHERE (title LIKE '%lol
The resulting request would be:
SELECT * FROM posts WHERE (title LIKE '%lol%');
DROP table posts;
SELECT * FROM posts WHERE (title LIKE '%lol%' or body LIKE '%lol%');
DROP table posts;
SELECT * FROM posts WHERE (title LIKE '%lol%')
Since this is a valid SQL command the result would be the successful deletion of the table posts of the database, which would instantly erase forever all my posts. Don't do mistakes like these when you're developing and just want everything to work, because the solution that just works could be a pretty serious security issue.
Add your thoughts about it!