Escaping Single Quotes in Shell for Postgresql

Escaping single quotes in shell for postgresql

What I usually do is use double quotes (") for postgres -c's argument and escaped double quotes (\") for psql -c's argument. That way, I can use single quotes (') inside the SQL string with no problem:

[root@mycomputer ~]# su postgres -c "psql -c \"SELECT 'hi'  \" "
 ?column? 
----------
 hi
(1 row)

Insert text with single quotes in PostgreSQL

String literals

Escaping single quotes ' by doubling them up → '' is the standard way and works of course:

'user's log'     -- incorrect syntax (unbalanced quote)
'user''s log'

Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes ", used for identifiers.

In old versions or if you still run with standard_conforming_strings = off or, generally, if you prepend your string with E to declare Posix escape string syntax, you can also escape with the backslash \:

E'user\'s log'

Backslash itself is escaped with another backslash. But that's generally not preferable.

If you have to deal with many single quotes or multiple layers of escaping, you can avoid quoting hell in PostgreSQL with dollar-quoted strings:

'escape '' with '''''
$$escape ' with ''$$

To further avoid confusion among dollar-quotes, add a unique token to each pair:

$token$escape ' with ''$token$

Which can be nested any number of levels:

$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$

Pay attention if the $ character should have special meaning in your client software. You may have to escape it in addition. This is not the case with standard PostgreSQL clients like psql or pgAdmin.

That is all very useful for writing plpgsql functions or ad-hoc SQL commands. It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible, though. @Craig's answer has more on that. More details:

• SQL injection in Postgres functions vs prepared queries

Values inside Postgres

When dealing with values inside the database, there are a couple of useful functions to quote strings properly:

• quote_literal() or quote_nullable() - the latter outputs the string NULL for null input.
  
  There is also quote_ident() to double-quote strings where needed to get valid SQL identifiers.
• format() with the format specifier %L is equivalent to quote_nullable().
  
  Like: format('%L', string_var)
• concat() or concat_ws() are typically no good for this purpose as those do not escape nested single quotes and backslashes.

How do I escape single quote in command line query of psql?

In Postgres you can use dollar-quoted strings:

select id,ext_ids ->> $$qwe$$ as qwe from data ORDER BY qwe;
-- or
select id,ext_ids ->> $anything$qwe$anything$ as qwe from data ORDER BY qwe;

Bash - Escaping single quotes

This is the wrong way to pass a complex command:

PSQL="psql -p $PGPORT -h $DBHOST -d $DB -tAc "

Instead, use arrays:

single_quote="'"
escaped_single_quote="\\'"
quoted_fname=${1//$single_quote/$escaped_single_quote}
quoted_lname=${2//$single_quote/$escaped_single_quote}
psql=( psql -p "$PGPORT" -h "$DBHOST" -d "$DB" -tAc )
dup_id=( "${psql[@]}" "SELECT id FROM ... WHERE ... '${quoted_lname}'" )

...and then using "${dup_id[@]}" to run your command will protect you from shell injection bugs. It isn't guaranteed to protect you from SQL injection attacks (there are too many ways to perform those, and databases have too many idiosyncrasies around character set conversion, to trust the simple character-replacement-based escaping used here against hostile data), but, well, that's why folks who are concerned about correctness or security use languages that support bind parameters -- a set of which bash is not a member -- for generating SQL queries.

See also BashFAQ #50, and the BashWeaknesses page from the freenode.org #bash channel's wiki -- the latter of which explicitly calls out SQL generation as a task for which bash is unfit.

How to escape single quote in postgres query via ansible

Not sure how it would work out in ansible playbook, but there is usually 3 ways to deal with this:

• use doublequotes around the query

  command: psql -U dbuser dbname -c "SELECT count(*) from table where time <= '01-sep-2016';"
  

• use backslash:

  command: psql -U dbuser dbname -c 'SELECT count(*) from table where time <= \'01-sep-2016\';'
  

• use quotes twice in a row:

  command: psql -U dbuser dbname -c 'SELECT count(*) from table where time <= ''01-sep-2016'';'
  

single quotes when using psql from command line

This is really a shell question, and is answered for example here.

Adapted for your question:

psql -U myuser -p 6432 -h myhost -d mydb-c 'update schema.table set column ='"'"'NS'"'"' where id <> 123;'

Note: sometimes, when you have several levels of passing args to nested shell invocations, it can also be beneficial to use the octal form of single-quote:

echo "\047"
# '

PSQL \copy from a bash script with QUOTE

This should work:

psql -c "\\copy raw.ap1_1 from file.csv (format 'csv', header, quote '\"')"

---

Related Topics