Script to generate sql insert commands

Question

I've been trying to make a script that will take numbers from a file and add them into an SQL insert command. So far it's working well but it's missing some of the important quotation marks

It should look like -

INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`) VALUES ('1111111111', '1111111111', 'mydomain.com', 'fs', 2, '8.8.8.8', NOW());

but it ends up looking like -

INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`) VALUES (1111111111, 1111111111, mydomian.com, fs, 2, 8.8.8.8, NOW());

Here is the full script -

echo "Removing Carriage"

#Remove carriage from file 

sed 's/\r$//' update.txt > forprocessing.txt 

sleep 1

echo "Generating Output"

sleep 2

#Generate Output

echo ""
file="forprocessing.txt"
while IFS= read line
do
#Add new routing rule
echo 'INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`'') VALUES ('$line', '$line', ''mydomian.com', 'fs', 2, '8.8.8.8',' NOW());'

done <"$file"
echo ""
echo "========"
echo "Finished"
echo "========"
#Clean up
rm forprocessing.txt

Any suggestions would be great as I'm very stuck.

Thanks Gareth

choroba · Accepted Answer · 2022-07-20 12:25:33Z

1

Single quotes don't nest.

echo 'INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`'') VALUES ('\'$line\'', '\'$line\'', '\''mydomian.com'\'', '\''fs'\'', 2, '\''8.8.8.8'\'', NOW());'

Note that this is unsafe if $line contains a single quote.

answered Jul 20, 2022 at 12:25

choroba

245k27 gold badges221 silver badges304 bronze badges

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

Collectives™ on Stack Overflow

Script to generate sql insert commands

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related