Variations in escaping string inputs Ruby VS SQL

We recently refactored some code.

This code allowed the server to read text data from images and then do lookups in a postgres db for related objects.

After the refactor, even with a reasonably solid test suite, honeybadger logged an annoying error.

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "s"
LINE 1: ...,'as','reporting','the','results','of','purchaser<error>'s'</error>,'activi...

: select * from products where catalog_number in (......)

The error being the presence of the apostrophe 'purchaser's'in the SQL IN Statement, causing the db to “GET ALL GRUMPY CAT”.

As the DB is really looking for something like

good_supplier_query = "select * from product_providers where name in ('name_1','name_2', 'name_3')"

# NOT
bad_supplier_query = "select * from product_providers where name in ('name_1','name_2's', 'name_3')"

SO, What’s a programmer to do ?

remove the apostrophe?
change the apostrophe to something else?
escape the apostrophe?

After Lengthy (2 min) consultation with the rest of the StartupLandia devs we decided that removing the apostrophe was a little too “blunt instrument” for us, we decided to escape the apostrophe. (this is where the interesting part of the post begins…)

Escaping a ' in ruby

Starting input would be something like ["name", "other name", "third's name"].join("'")

query_terms = ["name", "other name", "third's name"].join("' ")
query = "select * from product_providers where name in ('#{query_terms}')"

Which produces something like

"select * from product_providers where name in ('name' other name' third's name')"
# sql will choke on 'name' other name' third's name' 

Programmer might try

"funky's".gsub(/'/,"\'")

# "funky's" => NOPE

Programmer might try

"funky's".gsub(/'/,"\\'")

# "funkyss" => EVEN MORE NOPE
# the first '\' means everything after the match '\''

Programmer might try

"funky's".gsub(/'/,"\\\'")

# "funkyss" => EVEN MORE MORE NOPE
# the first '\' means everything after the match '\''
# this is like the everything after, everything after the match '\''

Programmer might try

"funky's".gsub(/'/,"\\\\'")

# "funky\\'s" => GETTING WARMER
# basically, escape the regex modifiers and just plain ol' escape the '

# BUT THE SPECS STILL FAIL - WAAA
ActiveRecord::StatementInvalid:
       PG::SyntaxError: ERROR:  syntax error at or near "s"
       LINE 1: ...re catalog_number in ('foo-456','bar-678','funky\'s','john\'...
                                                                    ^
       : select * from products where catalog_number in ('foo-456','bar-678','funky\'s','john\'s','baz-890')
     # ./app/models/product_predictor.rb:22:in `match_product_from_terms'
     # ./app/models/product_predictor.rb:14:in `predict!'

So three strikes your out right? Sorry, that’s baseball, this is programming.

After a bit of digging, we come to understand that yes, we have escaped the apostrophe correctly for ruby, and if all we had to do was display text in a view file or something we’d be good. However, we really want to feed our string into a SQL IN query. A bit more SQL related digging reveals that SQL has it’s own set of escape protocols and shockingly in this case, to escape a single ' the programmer needs to double it up ''

So then we try..

"funky's".gsub("'","''")

# "funky''s"
# our specs pass! yay!

Finished in 0.4846 seconds (files took 4.7 seconds to load)
1 example, 0 failures

Our specs pass, so we are back to let’s party cat ?

This post proves yet again, a real devil in the details almost always comes back to character sequence and syntax. Specifically in product engineering, where we focus on solving business problems with computer programming, we look for ways to avoid solving new computational problems (so that we might focus on the business problem). Many errors in product engineering (and general engineering) can be traced back to people going too fast, or especially in the case of web programming, not exhibiting enough sensitivity to changes in abstraction layers, ie, from ruby string escape to sql query inputs escaping.

For more reading on the above topics, please see

https://stackoverflow.com/questions/2180322/ruby-gsub-doesnt-escape-single-quotes https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server

Have a great day!

Variations in escaping string inputs Ruby VS SQL

A Dream Team