How to fix bash script not completing Postgres commands

Question

Script only opens up Postgres but does not process any commands after that.

#/bin/bash
filename='mac_addresses.txt'
filelines=`cat $filename`
echo Start
for line in $filelines ; do
    psql pcoip_mc_db postgres
    update endpoint set endpoint_group_id = 15 where mac_address='$filelines';
    \q
done

Expected results are to see this script go line by line in the mac_addresses.txt file and, after connecting to Postgres, run this command on every mac address in mac_addresses.txt:

update endpoint set endpoint_group_id = 15 where mac_address='$filelines';

George · Answer 1 · 2019-01-24T06:33:46.030

0

These 3 lines are not doing what you expect:

psql pcoip_mc_db postgres
update endpoint set endpoint_group_id = 15 where mac_address='$filelines';
\q

Each line should be a bash command. So you need to wrap the SQL query in a string and then pass it to the psql command. Like this:

psql pcoip_mc_db postgres -c "update endpoint set endpoint_group_id = 15 where mac_address='$line';"

(-c tells psql to execute the string as an SQL command)

Also, this bash will be a bit inefficient as you're going to connect and disconnect from the database for every line of the file. A more idiomatic bash script would transform each line of the file into an appropriate SQL expression, and then pipe all the generated SQL into a single psql connection. This can replace your script with a single line:

<"$filename" awk '{print "update endpoint set endpoint_group_id = 15 where mac_address=\'"'"'"$0"\'"'"';"}' | psql pcoip_mc_db postgres

(As a further improvement, you could even generate a single SQL query using an IN clause, such as: update endpoint set endpoint_group_id = 15 where mac_address IN ('mac1', 'mac2', ...);)

edited Jan 24 '19 at 06:33

answered Jan 24 '19 at 05:52

George

4,147
24
33

Do I need the < in front of $filename? Will this one liner pull the mac addresses from my mac_address.txt file? I am getting the following: ]$ <$filename awk '{print "update endpoint set endpoint_group_id = 15 where mac_address=\'"'"'"$0"\'"'"';"}' | psql pcoip_mc_db postgres -bash: $filename: ambiguous redirect $ <$mac_addresses.txt awk '{print "update endpoint set endpoint_group_id = 15 where mac_address=\'"'"'"$0"\'"'"';"}' | psql pcoip_mc_db postgres -bash: .txt: No such file or directory – Jeff Jan 24 '19 at 06:04
The "<" tells bash to treat that file as input to the awk command. I've slightly changed the answer to put quotes around "$filename" (this is an annoying bashism to handle whitespace in the filename variable) – George Jan 24 '19 at 06:17
Thank you George! So will I still have a separate mac_addresses.txt file? If I am understanding correctly this code below will go inside my script.sh file? <"$filename" awk '{print "update endpoint set endpoint_group_id = 15 where mac_address=\'"'"'"$0"\'"'"';"}' | psql pcoip_mc_db postgres – Jeff Jan 24 '19 at 06:19
You need to either define "$filename" the same way you do in your question, with `filename='mac_addresses.txt'` or you could replace the beginning of the command directly with ` – George Jan 24 '19 at 06:23
So I ran this and received: ./script.sh awk: cmd. line:1: warning: escape sequence `\'' treated as plain `'' UPDATE 1 UPDATE 1 Is this anything to be concerned about? – Jeff Jan 24 '19 at 06:27
To debug this, you should run the command but without the `| psql pcoip_mc_db postgres` at the end. That should print out a bunch of SQL commands which you should look over to verify that the generated SQL looks correct. If they look right, then you're good to go. To get rid of the warning, just remove the backslash characters in the awk command. – George Jan 24 '19 at 06:32
I am a little confused. I ran this and got the following: ./script.sh awk: cmd. line:1: warning: escape sequence `\'' treated as plain `'' update endpoint set endpoint_group_id = 15 where mac_address='00:05:52:52:52:47'; update endpoint set endpoint_group_id = 15 where mac_address='00:05:55:55:53:5A'; I am a little confused on exactly how to get rid of these errors and which `` characters you are referring to. – Jeff Jan 24 '19 at 06:40
Awesome! Got it working now! Thank you so much George! – Jeff Jan 24 '19 at 06:43
That's the thing I am aware of the IN clause but I am trying to prevent tediousness of having to type in each mac surrounded by single quotes. I have a whole bunch of mac addresses that are in an excel spreadsheet that I need to run this command on and it is much easier to just dump the MACs to a txt file and let the script go through each line in the text file. Am I making sense? – Jeff Jan 24 '19 at 06:53
Yes, its a great effort :) I just mentioned the IN clause as an optimization that can be done in a bash script... though for large input sizes it brings up some potential batching issues so that's why I didn't offer an implementation. If you do want to attempt it, a non-batching solution would involve 1) make a bash command to surround each line of your input file in quotes 2) use `paste -sd,` to combine all the lines into a single line with `,` delimiters 3) surround that giant line with the beginnin/end of the IN version of the query. – George Jan 24 '19 at 07:11
Sure! I’d like to try that! So what would the code look like for that? – Jeff Jan 24 '19 at 13:56
I need to add "AND online = 't';" to the command below. So how would it all look put together? <"$filename" awk '{print "update endpoint set endpoint_group_id = 15 where mac_address=\'"'"'"$0"\'"'"';"}' | psql pcoip_mc_db postgres – Jeff Jan 24 '19 at 20:14

score 0 · Answer 2 · answered Jan 24 '19 at 05:54

The problem is that the update and the \q are not handled as input to the psql command, but as shell commands. You have to tell bash that this is supposed to be the standard input for psql, for example with a “here document”:

#/bin/bash
filename='mac_addresses.txt'
filelines=`cat $filename`
echo Start
for line in $filelines ; do
    psql pcoip_mc_db postgres <<EOF
    update endpoint set endpoint_group_id = 15 where mac_address='$filelines';
EOF
done

Warning: this code is still unsafe and vulnerable to SQL injection. If any of the entries in the file contain spaces or single quotes, you will get errors and worse.

How to fix bash script not completing Postgres commands

2 Answers2