[SOLVED] Insert data into postgres using bash

Issue

I'm trying to insert values into postgres using bash. During my searching, I did find this:

href="https://stackoverflow.com/questions/53222779/inserting-data-into-postgres-db-using-shell-script">Inserting data into postgres db using shell script

The script I wrote is not working, and giving the following errors:

ERROR: column "task does not exist
LINE 1: INSERT INTO my_table (task,date,quad,branch) VALUES (task,2024-0...
                                                             ^
HINT:  There is a column named "task" in table "my_table", but it cannot be referenced from this part of the query.

My script is here:

#!/bin/bash
server=my_server
database=postgres
user=my_user

taskInput="task"
dateInput="2024-01-03"
quadinput="input_quad"
branchInput="shipping"
psql -U $user -w -h $server -p 5432 -d $database -c  "INSERT INTO my_table (task,date,quad,branch) VALUES ($taskInput,$dateInput,$quadInput,$branchInput)"

I'm not sure what's going on, and hoping you smart people can enlighten me.

Solution

Because you're building the SQL string yourself (and not using a library), you must provide quotes around strings:

psql -U $user -w -h $server -p 5432 -d $database -c "INSERT INTO my_table (task,date,quad,branch) VALUES ('$taskInput','$dateInput','$quadInput','$branchInput')"

---

Although this code allows an SQL injection, it's clear this is an internal use only operational script and therefore "OK". I'm quite sure users of this script can drop tables etc without having to use injection via this script, so you're not really any less safe by the existence of this script.

Answered By - Bohemian

Answer Checked By - Timothy Miller (WPSolving Admin)