How to Properly Escape Postgres SQL Queries for Bash?

Bash has its quirks, but it comes in real handy when you want to do something quickly. I frequently use bash for scraping, making small scripts for myself, and automating daily tasks. Once you get the hang of it, you can combine various tools to get the job done easily.

However, as convenient as bash can be, it makes a few tasks incredibly hard as well. One of these tasks is executing SQL Queries with arbitrary input.

Let’s say this is your schema (orders):

order_id: text
data: jsonb 

You want to scrape JSON from an API and insert raw JSON data in the data column. Postgres has really good support for JSON, so it’s very convenient to store the raw data that can be analyzed later.

The other alternative is to carefully design your schema and insert only the columns you need, but then, if you need any other value you’d need to go through the process again. So we will use the first approach of storing raw data.

The code for scraping the API would look something like this:

for i in {1..100}; do
    # Fetch order info by ID from the API
    API_CONTENTS="$(curl -sL "http://api.example.com/order/$i")";
    
    psql "postgres://user:pass@localhost/data" -c "
        INSERT INTO `orders` (order_id, data) VALUES (
           '$i',
           '$API_CONTENTS'
        );
    "
done;

But there’s a problem here. If a single quote is encountered in the API_CONTENTS, it will cause a syntax error because bash is literally substituting the string there.

ERROR:  syntax error at or near "value"
LINE 4:                 '{"data": "'value'"}'

Escape using single quotes

We can avoid this by escaping the single quotes. In Postgres, single quotes are escaped by double single quotes. We can use sed to do the same:

for i in {1..100}; do
    API_CONTENTS="$(curl -sL "http://api.example.com/order/$i")";
    
    psql "postgres://user:pass@localhost/data" -c "
        INSERT INTO `orders` (order_id, data) VALUES (
           '$i',
           '$(echo "$API_CONTENTS" | sed "s|'|''|g")'
        );
    "
done;

Escape using dollar quoted strings

Another alternative is to use dollar-quoted strings, a Postgres feature that allows you to define string constants using two dollar signs and an optional tag. Thus, we can define a string this way:

$$ A STRING $$
$custom_str$ A STRING $custom_str$

So our code becomes:

for i in {1..100}; do
    API_CONTENTS="$(curl -sL "http://api.example.com/order/$i")";
    
    psql "postgres://user:pass@localhost/data" -c "
        INSERT INTO `orders` (order_id, data) VALUES (
           '$i',
            \$API_JSON\$ $API_CONTENTS \$API_JSON\$
        );
    "
done;

A problem here is that the code is vulnerable to SQL injection if someone knows that $API_JSON$ is being used to quote a string. I won’t be worried though if it’s a project with limited scope and doesn’t use a production database.

Using Named Parameters

Perhaps, the most proper way to do this would be variable interpolation. psql util allows you pass variables via -v flag, which you can use in your query. However, remember that these cannot be used along with -c flag.

To use the feature, we would need to pass the query as STDIN.

psql "postgres://user:pass@localhost/data" -v api_content="$API_CONTENTS" <<< "
    INSERT INTO `orders` (order_id, data) VALUES (
       '$i',
       :'api_content'
    )
"

Output

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

Perfect! Those are three ways you can use Postgres queries in bash without worrying if they will fail for some arbitrary input.