Heroku and PostgreSQL command-line using -c/--command to process query with characters that need to be escaped

Question

I am familiar with the syntax of MySQL as far as escaping characters is concerned. However, as the Ruby on Rails application that I am currently building uses PostgreSQL I am struggling to get the command line to behave properly. I am trying to run a basic query through a shell script when rebasing the database to a more recent backup of the live database.

For example the command that I am trying to run looks like the following:

psql db_name u_name -c "UPDATE users SET encrypted_password = 'crazy_long_hash_here';"

I understand the security implications of the query being run, but this is for a development workstation where we need to be able to log in as varying classes of users to test different functionality.

The problem that I get when running this command is that the application the crashes with a Ruby Exception:

BCrypt::Errors::InvalidHash

This message clearly states that the value of the hash is no longer correct. However, if I run the same command in the standard command line style as follows:

psql db_name u_name

db_name=# UPDATE users SET encrypted_password = 'crazy_long_hash_here';
UPDATE (row count)

the command resets the password to the desired universal password and I am able to log in as any user to properly test any functionality.

What part of the escaping procedure am I missing? The following characters outside of the standard [0-9][a-zA-Z] character set are used in the hash:

$ .

Any help would be appreciated, as google seems strangely silent on the matter. The only other question I have found is not helpful: Escaping single quotes in shell for postgresql

---

UPDATE

I have also tried this with the here-document syntax to no avail:

psql db_name u_name << EOF
  UPDATE users SET encrypted_password = 'crazy_long_hash_here';
EOF

Answer 1

So as the case would have it, A little more fiddling resulted in the answer. When running the command though the standard psql interface, I needed to escape the $ to \$.

However, this continued to fail when running:

heroku pg:psql -c "UPDATE users SET encrypted_password = 'hash_with_\$_in_string';"

The answer to this was to double escape the characters, meaning:

heroku pg:psql -c "UPDATE users SET encrypted_password = 'hash_with_\\\$_in_string';"

Once both the \ and the $ are each escaped, the heroku toolbelt properly interpreted the hash and all is well.