Arch Linux

initbox

Member

Registered: 2008-09-27

Posts: 172

bash script - sqllite insert problem

So, I'm making a bash script that inserts a config file into a database, but I can't get it to work.

This is my best attempt so far:

...
name="bashrc"
location="/home/user/.bashrc"
content=`cat "$location"`
...
sqlite3 testdb "insert into dbase values ('`echo $name`','`echo "$location"`','`echo "$content"`')"

the table is

create table dbase(name text, location text, data text)

I had a hard time trying to figure out how I'd be able to get $variables to work (because of the quotes) and this echo style seems to work, but there seems to be no way to insert the config file into the database.

plaintext files seem to work no problem, but when I'm trying to insert some config file, it spits out errors. (It seems like it's trying to execute the file somehow?)

Last edited by initbox (2009-08-29 15:28:49)

Dusty

Schwag Merchant

From: Medicine Hat, Alberta, Canada

Registered: 2004-01-18

Posts: 5,986

Website

Re: bash script - sqllite insert problem

sqlite3 testdb "insert into dbase values ('$name', '$location', '$content')"

will work, you don't have to use backticks to echo in quotes.

The chances are your content has a ' in it that is getting inserted into the string to prematurely close the quotes around content. You need to pass it through something to escape the ' into \'. Sed can do the job, but there may be a filter that escapes bash content that I'm not thinking of.

content = $(cat $location | sed "s/\'/\\\'/g")

should do the trick.

Dusty

Last edited by Dusty (2009-08-29 16:16:57)

---

** Arch Linux Apparel and more ** Arch Linux stickers, laptop bags, jewelery ** I wrote a book on Python 3 ** and one on mental health **

initbox

Member

Registered: 2008-09-27

Posts: 172

Re: bash script - sqllite insert problem

sqlite3 testdb "insert into dbase values ('$name', '$location', '$content')"

will work, you don't have to use backticks to echo in quotes.

The chances are your content has a ' in it that is getting inserted into the string to prematurely close the quotes around content. You need to pass it through something to escape the ' into \'. Sed can do the job, but there may be a filter that escapes bash content that I'm not thinking of.

content = $(cat $location | sed "s/\'/\\\'/g")

should do the trick.

Dusty

I was thinking the single quotes were stopping bash from replacing $variables with the content, hence the echoes.

I'm still getting an SQL syntax error, so bash is no longer getting in the way, but this thing is a true nightmare. What next? This turned out to be way harder than I expected, I'm just doing this as a "fun" learning experiment.

Dusty

Schwag Merchant

From: Medicine Hat, Alberta, Canada

Registered: 2004-01-18

Posts: 5,986

Website

Re: bash script - sqllite insert problem

I was thinking the single quotes were stopping bash from replacing $variables with the content, hence the echoes.

your single quotes are inside a double quote, so to bash they're just another character in the string.

I'm still getting an SQL syntax error, so bash is no longer getting in the way, but this thing is a true nightmare. What next? This turned out to be way harder than I expected, I'm just doing this as a "fun" learning experiment.

Your error is probably because I was escaping the quotes for bash, and I should have been escaping them for sqlite. In sqlite, escaping a quote is done by doubling it, so ' is escaped to '' (that's two apostrephes side by side, not a double quote. Compare '' and ").

This untested sed command might work:

content = $(cat $location | sed "s/\'/\'\'/g")

The \'s are escaping the quote from bash, what its really doing is replacing ' with ''.

Dusty

---

** Arch Linux Apparel and more ** Arch Linux stickers, laptop bags, jewelery ** I wrote a book on Python 3 ** and one on mental health **

initbox

Member

Registered: 2008-09-27

Posts: 172

Re: bash script - sqllite insert problem

This untested sed command might work:

content = $(cat $location | sed "s/\'/\'\'/g")

I did try that already to no avail...