Bash: How insert text with special character in sqlite3?

Question

Let's imagine I want to save the text of various bash script in my database with sqlite3.

If my script do this

VARIABLE=$(cat "bashScript.sh")
sqlite3 mydb.db "CREATE TABLE data (myBash BLOB)" 
sqlite3 mydb.db "INSERT INTO data VALUES (${VARIABLE})"

Because the bash script will have all the special character, this will not work. How can I do this?

glenn jackman · Accepted Answer · 2012-05-11 17:39:36Z

2

You need to quote the value in the Insert statement, and you'll want to protect quotes in the value itself: untested:

sql_value=$(sed 's/'\''/&&/g' <<< "$VARIABLE")
sqlite3 mydb.db "insert into data values ('$sql_value')"

answered May 11, 2012 at 17:39

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

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

2 Comments

Rodrigo Over a year ago

Good idea, use '' inside a "", so the $sql_value work. The unique character I have to worry is the '? What you do is replace all ' by &&? And what the <<< really do? I search and don't find the mean of 3 <

glenn jackman Over a year ago

In the right-hand side of sed's s/// command, a & means "use whatever was matched on the left-hand side" -- so I'm replacing each single quote with 2 single quotes. The <<< is a bash here-string

Collectives™ on Stack Overflow

Bash: How insert text with special character in sqlite3?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related