Postgres not saving special characters

Question

I am trying to reset password for all users with following psql command. The command executes successfully but the value of password is wrong. The password is bcrypt string of 12345

psql -U myuser -d mydb -c "update \"Users\" set \"PASSWORD\" = '$2a$10$u0vd9noCisUUJME9k4daU.1/TNBPaeQam8dnV6tUOWwjn4kFT5XdW'"

The query:

select "PASSWORD" from "Users";

returns:

a.1/TNBPaeQam8dnV6tUOWwjn4kFT5XdW

instead of:

$2a$10$u0vd9noCisUUJME9k4daU.1/TNBPaeQam8dnV6tUOWwjn4kFT5XdW

What I understand that postgres is taking $ as a placeholder or special character. How can I escape it?

clemens · Accepted Answer · 2017-10-27 06:31:40Z

3

The problem is not with postgres but with the shell. It interprets the dollar signs as beginning of variables. You should escape them wit a backslash ('\$2a\$10\$u0vd9noCisUUJME9k4daU.1/TNBPaeQam8dnV6tUOWwjn4kFT5XdW'):

psql -U myuser -d mydb -c "update \"Users\" set \"PASSWORD\" = '\$2a\$10\$u0vd9noCisUUJME9k4daU.1/TNBPaeQam8dnV6tUOWwjn4kFT5XdW'"

answered Oct 27, 2017 at 6:31

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashutosh Over a year ago

Bingo, thanks a lot @macmoonshine, works perfectly :)

clemens Over a year ago

You're wellcome. BTW.: The pgcrypto module from pgcontrib offers a stored function for this encryption as well.

Collectives™ on Stack Overflow

Postgres not saving special characters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related