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
[email protected]
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 '[email protected]', '[email protected]', 'imap', 'mail.server.de', 993, True, 'password', True, 1, 'done', True WHERE NOT EXISTS (SELECT 1 FROM fetchmail_server WHERE name='[email protected]')
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"