Script to generate sql insert commands


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', '', 'fs', 2, '', NOW());

but it ends up looking like –

INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`) VALUES (1111111111, 1111111111,, fs, 2,, 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 ""
while IFS= read line
#Add new routing rule
echo 'INSERT INTO `opensips`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`'') VALUES ('$line', '$line', ''', 'fs', 2, '',' NOW());'

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

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



Single quotes don’t nest.

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

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

Answered By – choroba

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published