How can I update column values with the content of a file without interpreting it?

Question

I need to update values in a column when the row matches a certain WHERE clause, using the content of a text file.

The content of the file is javascript code and as such it may contain single quotes, double quotes, slashes and backslashes - out of the top of my mind, it could contain other special characters. The content of the file cannot be modified. This has to be done via psql, since the update is automated using bash scripts.

Using the following command - where scriptName is a previously declared bash variable -

psql -U postgres db<<EOF
\set script $(cat $scriptName.js))
UPDATE table SET scriptColumn=:script WHERE nameColumn='$scriptName';
EOF

returns the following error

ERROR:  syntax error at or near "{"
LINE 1: ...{//...
           ^

I would like to treat the content of the file $scriptName.js as plain text, and avoid any interpretation of it.

does `quote_literal(:script)` work? Or `$content$:script$content$`? — , Nov 05 '19 at 14:34
@a_horse_with_no_name Hi and thanks for your reply. None of the two solves the problem. I am still getting the error `ERROR: syntax error at or near "//" LINE 1: // dummy text blahblah` — gorgeous.george, Nov 05 '19 at 14:57

score 0 · Answer 1 · answered Nov 05 '19 at 15:01

0

You should quote the variable:

UPDATE table SET scriptColumn=:'script' WHERE ...

That causes the contents of the variable to be properly escaped as a string literal.

answered Nov 05 '19 at 15:01

Laurenz Albe

209,280
17
206
263

Have you considered the case where `script` contains a `'`? – Socowi Nov 05 '19 at 15:06
Hi and thanks for your reply. That was my first try but it didn't work - I get the usual `ERROR: syntax error at or near "//" LINE 1: // dummy text blahblah` – gorgeous.george Nov 05 '19 at 15:09

score 0 · Accepted Answer · answered Nov 05 '19 at 16:36

I found a solution to my problem, even though I don't know why it works. I leave it here in the hope it might be useful to someone else, or that someone more knowledgeable than me will be able to explain why it works now.

In short, setting the variable as a psql parameter did the trick:

psql -U postgres db -v script="$(cat $scriptName.js)"<<EOF
UPDATE table SET scriptColumn=:'script' WHERE nameColumn='$scriptName'
EOF

Not sure how this differs from

psql -U postgres db <<EOF
\set script "$(cat $scriptName.js)"
UPDATE table SET scriptColumn=:'script' WHERE nameColumn='$scriptName'
EOF

which I tried previously and returns the following error:

unterminated quoted string
ERROR:  syntax error at or near "//"
LINE 1: // dummy text blahblah

Thanks to everybody who helped!

The key here is to never mix your data in with the SQL syntax; if you do, any syntax contained in the data can cause parsing confusion. SQL variables contain data, not SQL syntax, so passing it in that form avoids the confusion. — Gordon Davisson, Nov 05 '19 at 20:10
@GordonDavisson I've just realized, thanks to your comment, that the cause of the error in my previous commands was the `\set` line, and not the `UPDATE` one. Thanks for the illuminating advice! — gorgeous.george, Nov 06 '19 at 07:26

How can I update column values with the content of a file without interpreting it?

2 Answers2

Linked