0

The reserved word 'user' is the problem for me, because it should be escaped with "" (double quotes) but with the combination of different quotes (' and " and \' and "/) psql passes it as user (without double escape). How to get this special word escaped?

I've tried already using $$ (Dollar-Quoted String Constants) or putting it in different variables, but nothing helps.

DB=mydb

MAIL_USER=my@mail.de
MAIL_PASS=password
MAIL_SERVER=mail.server.de
MAIL_PORT_IMAP=993
MAIL_PORT_SMTP=465

su -c "psql -d $DB -c \"INSERT INTO fetchmail_server(name, \"user\", type, server, port, is_ssl, password, active, priority, state, original) SELECT '$MAIL_USER', '$MAIL_USER', 'imap', '$MAIL_SERVER', $MAIL_PORT_IMAP, True, '$MAIL_PASS', True, 1, 'done', True WHERE NOT EXISTS (SELECT 1 FROM fetchmail_server WHERE name='$MAIL_USER')\"" postgres

Expected result would be, that this command is executed in the database

INSERT INTO fetchmail_server(name, "user", type, server, port, is_ssl, password, active, priority, state, original) SELECT 'my@mail.de', 'my@mail.de', 'imap', 'mail.server.de', 993, True, 'password', True, 1, 'done', True WHERE NOT EXISTS (SELECT 1 FROM fetchmail_server WHERE name='my@mail.de')
fphilipe
  • 9,739
  • 1
  • 40
  • 52
aNj
  • 174
  • 1
  • 6

1 Answers1

1

Thank you @wildplasser, your hint with just using psql with the right user was right. So I was able to reduce one nesting level. That works:

SQL_COMMAND='INSERT INTO fetchmail_server(name, "user", type, server, port, is_ssl, password, active, priority, state, "original") SELECT '\'$MAIL_USER\'', '\'$MAIL_USER\'', '\'imap\'', '\'$MAIL_SERVER\'', '\'$MAIL_PORT_IMAP\'', True, '\'$MAIL_PASS\'', True, 1, '\'done\'', True WHERE NOT EXISTS (SELECT 1 FROM fetchmail_server WHERE name='\'$MAIL_USER\'')'
echo $SQL_COMMAND
psql -d $DB -U postgres -c "$SQL_COMMAND"
aNj
  • 174
  • 1
  • 6