Improve the security and performance of DB2 Ruby on Rails applications using parameterized queries

The new ActiveRecord adapter for DB2

With the increasing interest in Ruby on Rails from companies in the enterprise world, some observers have posed questions about its suitability when it comes to the demanding requirements in this arena. One issue that some have called attention to is that ActiveRecord, Rails' Object-Relational Mapper (ORM), doesn't use prepared statements—or at least it didn't until now. With the latest release of DB2® on Rails, parameterized queries are automatically available and bring with them important performance and security benefits to Rails applications.

Share:

Antonio Cangiano (cangiano@ca.ibm.com), Software Engineer and Technical Evangelist, IBM

Antonio Cangiano photoAntonio 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.



21 January 2010

Also available in Russian Japanese Portuguese

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.


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:

Listing 1. Similar SQL queries
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:

Listing 2. Parameterized query
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):

Listing 3. ActiveRecord equivalent to 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:

Listing 4. Safe handling of the input in 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:

Listing 5. Unsafe handling of the input in ActiveRecord
# 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:

Listing 6. An SQL injection attack
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.


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:

Listing 7. Installing the ibm_db gem on Windows
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:

Listing 8. Installing the ibm_db gem on Linux/AIX®
$ 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:

Listing 9. Installing the ibm_db gem on Mac OS X 10.5
$ 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:

Listing 10. Installing the ibm_db gem on Mac OS X 10.6
$ 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:

Listing 11. Updating RubyGems
$ gem update --system

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:

Listing 12. A sample configuration snippet for a single environment
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.


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.

Figure 1. The benchmark results
Graph showing the benchmark results, comparing transactions over time

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.


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

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source, Web development
ArticleID=462541
ArticleTitle=Improve the security and performance of DB2 Ruby on Rails applications using parameterized queries
publish-date=01212010