SQL Literals in Squeel (or, Overriding Backticks in Ruby)


A little-known feature of the Squeel DSL is its support for creating SQL Literals via backticks. I didn’t make a very big deal about this, because it was ridiculously simple to implement, but it’s part of the DSL that I’m really happy with, for reasons I’ll go into below. Anyway, a Twitter conversation reminded me that the simple trick I used to implement SQL Literals in Squeel is less well-known than I thought it was.

@erniemiller yea .. I was wondering how you got around the ` in ruby (instead of running it as shell code)

– Eric Boehs (@ericboehs) May 29, 2012

Read on for details on how it works.

Shortest post ever.

So, without further ado, I present to you the highly complex source code that allows backticks to create SQL literals in Squeel:


def `(string)

That’s it. Ok, end of post. You can stop reading now.

You’re still here? You sly dog, you. I’ll let you in on the super-secret part of this post, then.

Why does it work?

This works because ` is a method on Kernel, which is an ancestor of Object, so it just happens to be available pretty much anywhere in Ruby. Don’t believe me? Check out the docs. By default, it returns the results of running the string parameter provided as a parameter. Much like the [](key) and []=(key, value) methods, Ruby wraps some syntactical sugar around this method which allows the parameter to be provided inside a matched pair of backticks. By the time the parameter is sent to the method, string interpolation has already happened. Let’s run a quick experiment.

ernie@morpheus:~$ irb
1.9.3p194 :001 > self.send(:`, 'echo hello, shell!')
 => "hello, shell!\n" 
1.9.3p194 :002 > shell = "bash"
 => "bash" 
1.9.3p194 :003 > self.send(:`, 'echo hello, #{shell}!')
 => "hello,\n" 
1.9.3p194 :004 > `echo hello, #{shell}!`
 => "hello, bash!\n" 

As you can see, the string that contained the interpolation text is passed as-is to the backtick method, which results in the text after “hello, “ being interpreted by the shell as a comment. Using backticks as quotes, however, interpolates the string as expected.

Nifty! I’m gonna run around putting backtick overrides all over my code, now!

Hold on there, buddy. Not so fast. Despite the fact that backtick is a method, we don’t normally think of it that way. If Spider-Man taught us anything, it’s that with great power comes great responsibility. Backticks are normally sent to the implicit receiver, self, and calling the method directly requires using send as in the examples above, to avoid confusing Ruby’s lexer.

In other words, unless you want to change the behavior of backticks in anything that includes your module or inherits from your class, and prevent using backticks for shell commands, you probably don’t want to do this. But it’s great for an instance_evaled DSL like Squeel, since the implicit receiver is going to be the instance of Squeel::DSL for the contents of the DSL block, anyway.

Why backticks?

I really like using backticks for creating SQL literals in the case of the Squeel DSL, for a number of reasons.

  1. Backticks indicate that we want to drop down a level of abstraction – from the Ruby interpreter, to the underlying system. Backticks in the Squeel DSL likewise eliminate an abstraction, passing the string directly to the database, unmodified.
  2. Backticks carry with them a sense of danger. Competent Ruby programmers know that you must be very careful with text you place inside backticks, since it will be getting passed to a shell. This inherent caution should carry over to the sending of literal strings to the database.
  3. Backticks already provide useful interpolation functionality. We don’t need to do any custom interpolation with %{} or similar placeholders.
  4. Backticks are extremely unlikely to collide with the default Ruby behavior in intentional usage. If you actually want to take the output of a system command and pass it unmodified to your database, it should be more cumbersome – also, may God have mercy on your soul.
  5. Backticks allow conciseness that isn’t otherwise possible, and increase readability. The alternative ways of generating a literal SQL string involve things like Arel.sql(“my SQL”) or monkey-patching String to add a method like literal. Overriding backticks makes the SQL literal string convenient to type, and can be made to stand out in a syntax highlighting editor, as well.

So, that’s why I think the backticks are a great fit for the Squeel DSL. Now that you know how to (ab)use custom backtick functionality in your own code, please consider your reasons for doing so. The criteria I outlined above would be a great place to start. Have fun!

comments powered by Disqus