How to escape 3 level in bash (su command, psql, then query)

I'm working on a bash script used to install an application on a server and create a postgresql user for the application.

The matter is that if the user contains a special char, it's not escaped. So I would like to escape the name in the query.

Here is a part of my code:

db_user="test-test"
db_password=1234
su - postgres -c "psql -c \"CREATE USER $db_user WITH PASSWORD '$db_password';\""

So I have to escape:

the command for su -c ...
the query for psql -c ...
the user name in $db_user
the password in $db_password

Of course, if I add quotes around $db_user it's not working anymore. I tried others solutions, but none working.

Any idea?

Thanks

Solution

Let the shell do the work for you for you. In this case, that means printf '%q' -- which returns an eval-safe version of each literal argument.

cmd=( psql -c "CREATE USER $db_user WITH PASSWORD '$db_password';" )
printf -v cmd_q '%q ' "${cmd[@]}"
su - postgres -c "$cmd_q"

By the way, substituting literal text into SQL queries this way is massively insecure -- but if you follow the process above you're prone to only SQL injection bugs, not shell injection bugs.

To avoid SQL injection bugs as well, you want to avoid textual substitution of values into your queries at all.

# This is inefficient, but makes it completely unambiguous that our heredocs are literal
query=$(cat <<'EOF'
CREATE USER :new_user_name WITH PASSWORD :'new_user_password';
EOF
)

cmd=(
  psql \
    --set=new_user_name="$db_user" \
    --set=new_user_password="$db_password"
    -c "$query"
)

printf -v cmd_q '%q ' "${cmd[@]}"
su - postgres -c "$cmd_q"

See http://caryrobbins.com/dev/postgres-scripting/ for more on this technique. The classic XKCD linked in its header (also useful as a concrete example of the risks the original code was taking!) has its original source at https://xkcd.com/327/.