String#squish Quietly Changed My SQL

I use String#squish almost without thinking.

It's one of those little ActiveSupport helpers that keeps heredoc SQL readable in code and tidy in logs. You write a nicely indented query, call .squish, and end up with a clean one-liner.

sql = <<~SQL.squish
  SELECT *
  FROM orders
  WHERE status = 'active'
SQL

# => "SELECT * FROM orders WHERE status = 'active'"

I still love it.

Last week, though, it introduced a bug so subtle that I chased it through three layers of the stack before realizing the culprit had been sitting in plain sight the whole time.

The bug report

A reporting dashboard had an "Average Days to Ship" metric filtered by sales representative.

It worked perfectly for every rep except one.

Mary Jones

Notice the double space between her first and last name.

The report wasn't wrong. Her name really was stored with two spaces, and it displayed correctly everywhere else. Only this one metric always returned 0.

Note: The production query has been simplified and anonymized to protect sensitive data. The bug and the relevant code path are exactly the same.

The investigation

Suspect #1: The data

My first instinct was bad data.

SELECT sales_rep_name
FROM orders
WHERE sales_rep_name LIKE 'Mary%';

-- "Mary  Jones"

The database contained the double space exactly as expected.

Suspect #2: The params

Maybe the browser or Rails params had collapsed the whitespace.

I traced the value from the form, through the controller, into the query builder.

Still two spaces.

Suspect #3: The browser

HTML has its own whitespace rules, so I convinced myself the <select> element had to be responsible.

It wasn't.

The option was rendered with an explicit value="Mary Jones" attribute, and attribute values preserve internal whitespace.

Another dead end.

At that point, I finally did what I should have done from the beginning.

Instead of reasoning about where the value might have changed, I logged the final SQL string immediately before it was executed.

... WHERE "orders"."sales_rep_name" = 'Mary Jones' ...

One space.

The database had two.

The params had two.

The Arel-generated SQL fragment had two.

Somewhere during the final query construction, one of those spaces disappeared.

The reveal

Here's a simplified version of the method:

def sub_query
  <<~SQL.squish
    SELECT AVG(days) AS average_count
    FROM (
      SELECT ...
      FROM orders
      WHERE (#{apply_filter(Order.all).to_sql.split('WHERE').last})
    ) sub
  SQL
end

The important part is the order of operations.

apply_filter(...).to_sql produces a properly quoted SQL fragment:

"sales_rep_name" = 'Mary  Jones'

That fragment is interpolated into the heredoc.

At that point, the SQL string already contains the data value.

Then .squish runs.

And String#squish does exactly what it's designed to do: collapse every run of whitespace into a single space.

It doesn't know that some whitespace belongs to SQL formatting while other whitespace is part of a string literal.

To squish, they're all just whitespace.

You can reproduce it with a single line:

"WHERE name = 'Mary  Jones'".squish
# => "WHERE name = 'Mary Jones'"

The query now searches for a person who doesn't exist.

No rows match.

The metric ends up reporting 0.

Why this is such a good trap

Every individual piece of code is perfectly reasonable.

• Building SQL with a heredoc.
• Interpolating a properly quoted Arel fragment.
• Using .squish to make the SQL compact.

The bug only appears when those three things are combined.

Even worse, it only appears when the data happens to contain adjacent whitespace.

Most names don't.

Most free-text values don't.

So the code can happily run for years, pass every test, and then one day someone named Mary Jones appears, or a product category is saved as Office Supplies, and one dashboard quietly starts reporting incorrect numbers.

Nothing crashes.

Nothing logs an error.

The result just looks... believable.

And that's the dangerous kind of bug.

A crash gets fixed.

A believable but incorrect number can live in production for months.

The fix

The fix turned out to be almost anticlimactic.

Don't apply a whitespace-normalizing transformation after you've already mixed data into the string.

Removing .squish solved the problem.

PostgreSQL doesn't care whether the query spans one line or twenty, so the SQL executes exactly the same.

The only difference is how it looks in the logs.

More importantly, avoid interpolating values into raw SQL whenever possible.

Let ActiveRecord and the database driver handle quoting and parameter binding instead.

The broader lesson

This isn't really about String#squish.

It's about applying text transformations after you've mixed structure and data into the same string.

Whether it's:

• String#squish
• strip
• gsub(/\s+/, " ")
• split.join(" ")
• an HTML minifier
• or any other "cleanup" step

they all operate on plain text.

They can't distinguish between formatting and content.

Once your data has been interpolated into the string, a transformation intended to clean up the structure can just as easily modify the data.

Normalize your templates before interpolation.

Normalize your data at the boundaries of your system.

But once structure and data have been combined into the same string, treat that string as read-only.

I still use String#squish almost every day.

I just make sure it's the last thing that touches my SQL template, not the first thing that touches my data.