Using Grep to create multiple variables in a loop per line - BASH

Question

I'm trying to take a multiline file in the above format and make MySQL insert statement from it. End result aim is something similar to outputting: -

insert into ansible_db (ip, version) values ("$ip", "$version")

per line for each of the following data examples (this is the input file, I want the IP address and the result of the msg segment - ie 4.0.3): -

ok: [192.168.0.214] => {s    "msg": "4.0.3"s}
ok: [192.168.0.210] => {s    "msg": "4.0.8"s}
ok: [192.168.0.208] => {s    "msg": "fdsajkghjfdka"s}
ok: [192.168.0.216] => {s    "msg": "Potatoes""""s}

The following greps get the IP's and message out appropriately: -

$ip=grep -oE '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' 
$version= grep -oP '(?<=\"\: \").*(?=\"s})' #format of the message will vary going forward.

I want to put it in a loop, but I'm struggling. Any advice appreciated.

At least to me, it's not clear what part of this is the input format, and what part is the output format. I think it could use some editing to clarify what each looks like/needs to look like. — Jerry Coffin
– Jerry Coffin, Commented Sep 25, 2017 at 11:49
no need to bash loop, try awk -v FS='[][]|"' '{print "insert into ansible_db (ip, version) values ( "$2 " , "$6" )" }' yourInputFile — P....
– P...., Commented Sep 25, 2017 at 11:52
End result will be code vulnerable to a SQL injection attack. Don't do this. — chepner
– chepner, Commented Sep 25, 2017 at 14:23

Lets Script This · Accepted Answer · 2017-09-26 09:03:04Z

1

In this case, while-loop is not required. If your file is having the consistent structure like you provided in the question following command should print the required lines.

awk -v FS='[][]|"' '{print "insert into ansible_db (ip, version) values ( \""$2 "\" , \""$6"\" )" }' yourInputFile

Note that above command will display the result in std out, if you want to execute then you can use |bash in the end of the command like:

awk -v FS='[][]|"' '{print "insert into ansible_db (ip, version) values ( "$2 " , "$6" )" }' yourInputFile |bash

edited Sep 26, 2017 at 9:03

Lets Script This

535 bronze badges

answered Sep 25, 2017 at 12:20

P....

18.6k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RomanPerekhrest · Accepted Answer · 2017-09-25 12:27:52Z

0

Note, MySql VALUES syntax requires complex string arguments to be enclosed with quotes.
To obtain valid MySql insert statements use the following approach:

awk '{ gsub(/[\[\]]/,"",$2); sub("\"+s}","\"",$6); 
       print "insert into ansible_db (ip, version) values (\""$2"\", "$6");" }' file

The output:

insert into ansible_db (ip, version) values ("192.168.0.214", "4.0.3");
insert into ansible_db (ip, version) values ("192.168.0.210", "4.0.8");
insert into ansible_db (ip, version) values ("192.168.0.208", "fdsajkghjfdka");
insert into ansible_db (ip, version) values ("192.168.0.216", "Potatoes");

answered Sep 25, 2017 at 12:27

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

1 Comment

Lets Script This Over a year ago

Thank you, I had already noted and escaped the quotes as per your example. I will request that PS updates his answer to include that though.

Collectives™ on Stack Overflow

Using Grep to create multiple variables in a loop per line - BASH

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related