0

I'm using bash shell. I have a CSV file in which each line's tokens are separated by commas. I want to take the second and third columns and forma new string out of them (an SQL statement). I thought I could use awk for this purpose, so I tried ...

localhost:mydir davea$ awk -F ',' -v OFS=',' "REPLACE INTO my_table (ID, NAME, HOURS) VALUES ('$2', '$2', '$3');" types.csv
awk: syntax error at source line 1
 context is
    REPLACE INTO my_table >>>  (ID, <<<
awk: bailing out at source line 1

but as you can see I'm getting an error. Am I leaving something out? How do I form my new string from each line in the CSV file?

  • Good catch. But even when I change it to "awk -F ',' -v OFS=',' "{print REPLACE INTO my_table (ID, NAME, HOURS) VALUES ('$2', '$2', '$3');}" types.csv", I get the same syntax error as above. – Dave Dec 6 '17 at 21:30
2
awk -F ',' '{
    printf("REPLACE INTO my_table (ID, NAME, HOURS) VALUES ('\''%s'\'', '\''%s'\'', '\''%s'\'');\n", $2, $2, $3) 
}' types.csv
1

Putting single quotes inside of a single quoted string is really tedious. Here, I'm passing a single quote into the the awk variable "q". I'm also trying to take care of SQL injection.

awk -F ',' -v q="'" '
    {
        for (i=1; i<=NF; i++) gsub(q, q q, $i)
        print "REPLACE INTO my_table (ID, NAME, HOURS) VALUES (" q $1 q "," q $2 q "," q $3 q");"
    }
' <<END
foo,bar,Robert');DROP TABLE students;--
END
REPLACE INTO my_table (ID, NAME, HOURS) VALUES ('foo','bar','Robert'');DROP TABLE students;--');
  • drop table? sounds like a joke – Sasha Che Dec 6 '17 at 21:45
  • follow the link in my answer... – glenn jackman Dec 6 '17 at 21:54
  • +1 for for (i=1; i<=NF; i++) gsub(q, q q, $i). – PesaThe Dec 6 '17 at 22:02
0

Might be not the most efficient solution but shall work:

awk  -F, '{print "REPLACE INTO my_table (ID, NAME, HOURS) VALUES (\x27"$2"\x27,\x27"$2"\x27,\x27"$3"\x27);"}' types.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.