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:

 

@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.

One of the ideas that the people that I use to chat with about web development told me, was to use the del.icio.us API to publish, in my site, my last bookmarked links. In this post I want to tell you how you can do something quite like I have on my home.

The first thing you need to do is open the controller that manages the view where you'll put the bookmarks, and in the desired method of that controller, put the following code:


require 'net/https'
require "rexml/document"

 The first one will help to connect to del.icio.us API, and the second one will help on the XML parsing of the data outputed by the API. So, let's start by connecting to the del.icio.us API. To accomplish this an authentication is needed via SSL, so you'll have to pass  an username and password, the ones of your account.

@delicious = ""
http = ""
resp = ""

http = Net::HTTP.new('api.del.icio.us', 443)
http.use_ssl = true
http.start do |http|
request = Net::HTTP::Get.new('/v1/posts/recent?&count=7')
request.basic_auth 'putyourusernamehere', 'putyourpasswordhere'
response = http.request(request)
response.value
resp = response.body
end

By default this action will try to retrieve the last 7 bookmarked links on del.icio.us. Don't forget to substitute the putyour... words by your own username and password.

 But, as you can see in the del.icios.us API site, there are much more commands that you would possibly want to execute, besides the last 7 bookmarked links. The method of retrieving should be the same for every action. Besides that, see on the API documentation the optional or required arguments that each command has. In recent?, for example, I defined the argument &count=7 to define a specific number of bookmarks returned. If none defined it'd retrieve 15.

Now that we have our output stored in the variable resp, it's time to do the parsing and put the final result on our webpage.

doc = REXML::Document.new(resp)
doc.elements.each("*/post") { |element| @delicious += "<a href=\"" + element.attributes["href"] + "\">" + element.attributes["description"] + "</a><br />" }

First, we're declaring a new document based on the output received and storaged in the variable resp.  Then, we're sliding through each item post (we used */ for not having to declare all the path to the post item). Then, to access each element's attributes we have only to element.attributes["attribute name"]. I saved the result on a @delicious variable with all the html that I needed to output my last bookmarks as links to the links themselves and the required line breaks.

Everything is done now, you just have to put <%= @delicious %> on your web page and all should be fine.

But, and what if the host is unavailable or the parsing fails? We've to catch those exceptions, so add this final code to the end of your controller's method:

rescue SocketError
  @delicious = "Host unavailable"
rescue REXML::ParseException => e
 @delicious = "error parsing XML "  + e.to_s
 

Try it and enoy it.