Ruby on Rails has managed to establish itself as one of the most commonly used frameworks for developing Web applications. Released in 2004, this framework has evolved rapidly and gathered an impressive amount of media attention, as well as a large number of fans within the development community.

Over the course of just a few years, Rails (as it's commonly referred to) moved from being a cutting edge tool that a few early adopters and startups employed, to a mature tool whose productivity claims have recently started to fascinate a more "enterprisey" crowd.

Rails and the enterprise

When discussing the subject at hand, one question that's frequently raised by the more skeptical amongst us is whether Rails is enterprise-ready. Rails makes no apologies for being a pragmatic and opinionated piece of software. It didn't set out to please everyone, particularly the enterprise, which is sometimes perceived as being too process-driven and overly complex. The needs of enterprise companies and those of the Rails development team tend to differ substantially. Therefore, the features that the former needs are unlikely to come from the latter.

However, it's worth reframing this subject by asking whether the enterprise world is Rails-ready. Over the past few years, the enterprise world has begun to embrace the agile development movement, as well as more lightweight tools, but there are still nonnegotiable requirements that would make Ruby on Rails a less than an ideal match in many scenarios.

Thankfully, Rails provides a system of add-ons that has enabled the creation of thousands of plug-ins (which have been developed by the community to satisfy all sorts of requirements). Fundamentally though, Rails tends to apply the principle of doing the "simplest thing that could possibly work," which is in stark contrast with the approach often preferred by the enterprise.

For example, SQLite and MySQL may be popular and sufficient for some open source developers, but enterprise developers will be far more interested in rock-solid support for commercial databases such as DB2 and Oracle.

Very early on, IBM® stepped in to catalyze the convergence of these two worlds. In fact, IBM is the only database vendor to provide and commercially support a Ruby driver and a Rails adapter for DB2 and IBM Informix® Dynamic Server (IDS). By doing so, many companies that use DB2 in their infrastructure have been able to develop new applications using Ruby on Rails. The so-called "DB2 on Rails" pair also works extremely well for open source developers and startups who'd like to combine a powerful, scalable, reliable (and free in its Express-C edition) data server with the simplicity of writing applications in Ruby on Rails.

Today, with the announcement of Version 2.0 of the Rails adapter, DB2 also became the only database that transparently supports parameterized queries in Rails (without the need to modify an application's code). This has profound performance and security implications, and is a major step forward for any developer who uses Rails in conjunction with DB2.

Back to top

Parameterized queries and ActiveRecord

Behind the scenes, a typical Web application executes a large number of similar queries over and over. In some cases, what changes is not the query itself, but rather the values used within it. For example, the following queries in Listing 1 are identical, except for the requested name:

                
SELECT * FROM people WHERE name = 'Antonio'
SELECT * FROM people WHERE name = 'Leon'
SELECT * FROM people WHERE name = 'Praveen'
SELECT * FROM people WHERE name = 'Mario'

All of these can be condensed into a single parameterized query (also known as a prepared statement), as shown in Listing 2:

                
SELECT * FROM people WHERE name = ?

The actual value is passed to the pre-compiled query as a parameter at runtime. The query itself and its access plan are cached once by DB2 and are then ready to be used every time the query is executed. This improves performance chiefly because the database saves all the overhead involved with calculating the execution plan for the same query over and over. If such a query (or a more complex one) were to be repeated a few hundred (or thousand) times, the difference between the two approaches would start to become quite drastic.

Rails applications use ActiveRecord, an object-relational mapper (ORM) framework, to handle database connections and queries. ActiveRecord does not support parameterized queries by default. This means that each query is built by dynamically merging the input from the user (which is generally duly sanitized) and then executed as a query of its own. For example, ActiveRecord translates the following method calls (Listing 3) into the aforementioned distinct queries (see Listing 1):

                 
Person.find_by_name('Antonio')
Person.find_by_name('Leon')
Person.find_by_name('Praveen')
Person.find_by_name('Mario')

Thanks to the latest Ruby gem for DB2 on Rails (called ibm_db) each of these queries is handled by the ActiveRecord adapter (that was developed by IBM for DB2) in a way that takes care of creating a single parameterized query that's instantly ready for reuse. Performance aside, parameterized queries are great for preventing SQL injection attacks. Parameters are viewed as values that are passed to the query, that have no effect on the structure of the query itself. As such, clever tricks with quotes and special characters that are sometimes employed by malicious users in an attempt to dynamically modify the structure of the query become futile.

ActiveRecord already does a good job when it comes to sanitizing the user input, as long as the developer pays attention and uses the right tools provided by the framework. For instance, the following instruction (Listing 4) will be correctly sanitized against direct SQL injections by ActiveRecord:

                 
User.find(:all, :conditions => ["role = ? AND age > ?", role, age])

If a less-than-careful developer entered the text from Listing 5 (in the hopes of embedding the values directly), the call would be exposed to SQL injection attacks:

                
# Don't do this
User.find(:all, :conditions => "role = '#{role}' AND age > #{age}")

If such was the case, the query would in fact be built dynamically, depending on the values of role and age. For example, age might be the string "18 OR 1=1" and the query would then become something along the lines of:

                 
SELECT * FROM users WHERE role = 'user' AND age > 18 OR 1=1

This would retrieve records that you didn't intend to expose.

For this reason alone, developers should always opt for the safe options provided by ActiveRecord for sanitizing the input (as shown in Listing 4). This new edition of DB2 on Rails adds an extra layer of security, thanks to the fact that these "safe" calls are translated into parameterized queries, which are immune to SQL Injection attacks. With DB2, you have the peace of mind knowing that if a clever hacker managed to circumvent the sanitation process adopted by ActiveRecord, those extra characters or ill-intentioned quotes would not change the nature of your query.

Back to top

Installing or upgrading to ibm_db 2.0.0

In order to take advantage of parameterized queries in Rails, you do not need to modify any of the code within the application. You just need to follow two simple steps to enable this new feature:

• Install the latest version of the Ruby gem for DB2 on Rails (known as ibm_db)
• Modify the application's database configuration file

Let's get started.

If you're a Microsoft® Windows® user, you can simply install the gem by running the following command from the Command Prompt:

                
C:\> gem install ibm_db

If you're a Linux® or UNIX® user, you need to specify where the development headers are located before installing the gem in the following manner:

                
$ sudo -s
$ export IBM_DB_INCLUDE=/home/db2inst1/sqllib/include
$ export IBM_DB_LIB=/home/db2inst1/sqllib/lib
$ . /home/db2inst1/sqllib/db2profile
$ gem install ibm_db

(64-bit users, should point to the lib64 library instead.)

If you're a Mac OS X 10.5 (Leopard) developer, you can similarly install the gem as follows:

                
$ sudo -s
$ export IBM_DB_LIB=/Users/<username>/sqllib/lib
$ export IBM_DB_INCLUDE=/Users/<username>/sqllib/include
$ . /Users/<username>/sqllib/db2profile
$ gem install ibm_db

While if you're a Mac OS X 10.6 (Snow Leopard) user, you should issue:

                
$ sudo -s
$ export IBM_DB_LIB=/Users/<username>/sqllib/lib64
$ export IBM_DB_INCLUDE=/Users/<username>/sqllib/include
$ export ARCHFLAGS="-arch x86_64"
$ gem install ibm_db

All these instructions install the gem or update it to the latest version if an older one was previously installed. If the gem install command has an issue with the installed RubyGems version, you can run the following command to update the package manager itself:

                
$ gem update --system

Back to top

Enabling parameterized queries in existing Rails applications

Rails applications store the database configuration in a file located in config/database.yml. To enable parameterized queries in a given application, specify the parameterized parameter. For instance:

                
production:
  adapter: ibm_db
  username: db2inst1
  password: secret
  database: mydb
  parameterized: true

If this configuration parameter is not set, the ActiveRecord adapter for DB2 will continue to work as per the previous versions, without transforming regular queries into parameterized ones. By doing so, it becomes easy to quickly assess the performance gains and decide whether this option should be kept enabled or not in production mode.

Back to top

Performance considerations

Granted, you should always run your own benchmarks, but tests in our lab have shown that enabling parameterized queries made a substantial performance improvement. The benefits of prepared statements are clearly highlighted with an increase in the workload. The graph in Figure 1 shows the results obtained when testing a relatively heavy workload with and without parameterized queries enabled, as well as with the Statement Concentrator activated.

As you can see, enabling parameterized queries increased the database throughput by about 30-40% and even offered a 15-25% increase over enabling the Statement Concentrator. The Statement Concentrator can be seen as an intermediate step. It's a feature that attempts to emulate the benefits of prepared statements at the data server level by reducing compilation overhead by sharing the same access plan for queries that are identical (save for the values of literals). It's an excellent feature that's only available in DB2 9.7 and is one that should be activated if parameterized queries are not available for a given development framework. Thankfully, from now on, DB2 on Rails developers will not have to worry about this.

Back to top

Conclusion

The announcement of this new version of the ibm_db gem addresses one of the very few (but nevertheless highly important) Rails shortcomings and should come as good news to anyone who is interested in combining the simplicity of Rails with the power of a world-class database. DB2, with its many powerful features, including native XML storage (pureXML), is leading the way in terms of making Rails applications scalable at the enterprise level. In addition, DB2 is able to do so at an unbeatable price, thanks to the absolutely no-charge version, DB2 Express-C.

Resources

Learn

• DB2 on Rails blog: Visit the DB2 on Rails blog to learn more.
  
  
• Ruby on Rails guides: Read the Ruby on Rails guides to get started with Ruby on Rails.
  
  
• Getting Started with DB2 Express-C (IBM, 2009): Read this free ebook, which is ideal for developers, consultants, ISVs, DBAs, students, or anyone who wants to get started with DB2.
  
  
• DB2 on Campus videos: Learn DB2 in one day with these DB2 on Campus videos and corresponding PDF presentations.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• DB2 Express-C: Download DB2 Express-C, a fully licensed product available at no-charge for the community.
  
  
• IBM Data Studio: Download Data Studio.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• developerWorks IBM DB2 Express Forum: exchange ideas and share solutions with your peers in the IBM DB2 Express community.
  
  
• RubyForge forums: Discuss DB2 on Rails.
  
  
• Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.
  
  

About the author

Antonio Cangiano works as a software engineer and technical evangelist for the DB2 team at the IBM Toronto Software Lab. He was the original developer of the Rails adapter for DB2 and one of the leaders of the DB2 on Rails community. He is well-known in the Ruby and Rails communities, and in 2009 authored a book on the subject for Wrox, Ruby on Rails for Microsoft Developers. You can read more of Antonio's writing on his blog, Zen and the Art of Programming.

Rate this article

Comments

Back to top

Table of contents

• Rails and the enterprise
• Parameterized queries and ActiveRecord
• Installing or upgrading to ibm_db 2.0.0
• Enabling parameterized queries in existing Rails applications
• Performance considerations
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Express-C
• Demo: Use Ajax with DB2
• Community: DB2

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers