Script to generate sql insert commands-CodePudding

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

CodePudding user response：

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.