using bash to insert values into sqlite database

Question

I'm having trouble inserting values into a sqlite database. I'm using a bash script to parse a xml file then inserting these values into the database. My script is as follows:

#!/bin/bash

create_books_db()
{
sqlite3 books.db <<EOF

create table books (
book_id primary key,
book_url text,
description text,
book_name text
);

EOF
}

plug_values_into_books_db()
{
sqlite3 books.db <<EOF

insert into books (book_id,book_url,description,book_name)

values('$book_id',"$book_url","$description","$book_name");

EOF
}

start of script

create_books_db

line="/home/$USER/star.wars.the.empire.strikes.back.xml"

book_id=$(xmlstarlet sel -t -v //book/id $line);
book_url=$(xmlstarlet sel -t -v //book/book_url $line);
description=$(xmlstarlet sel -t -v //book/description $line);
book_name=$(xmlstarlet sel -t -v //book/name $line);

plug_values_into_books_db

#end of script

This is a sample of the xml file I'm using:

?xml version="1.0"?>
<book>
  <book_url>https://starwars.com/books/</book_url>
  <character_credits>
    <character>
      <character_id>99</character_id>
      <character_name>Darth Vader</character_name>
    </character>
  </character_credits>
  <description>   The Empire Strikes Back (also known as Star Wars: Episode V – The
                  Empire Strikes Back) is a 1980 American epic space-opera film directed
                  by Irvin Kershner. Leigh Brackett and Lawrence Kasdan wrote the
                  screenplay, with George Lucas writing the film's story and serving as
                  executive producer. It was produced by Gary Kurtz for Lucasfilm and
                  stars Mark Hamill, Harrison Ford, Carrie Fisher, Billy Dee Williams,
                  Anthony Daniels, David Prowse, Kenny Baker, Peter Mayhew, and Frank Oz.
                  It is the second installment in the original Star Wars trilogy, the
                  second of the franchise to be produced, and the fifth episode in the
                  &#x201C;Skywalker Saga.&#x201D;</description>
  <book_id>103</book_id>
  <book_name>The Empire Strikes Back</book_name>
</book>

1) When I try this in the values line:

values($book_id,"$book_url","$description","$book_name");

then select * from books in sqlite, the book_id and book_name values are missing

2) changing to single quotes in the values line produces this error:

Error: near line 2: near "s": syntax error

3) Escaping the quotes produces:

Error: near line 2: unrecognized token: "\"

I've tried putting "'" quotes around the bash variables as well but I keep getting errors

Only point 1) above works without errors but it seems to omit the book_id and book_name tags,

I'm in the process of learning sqlite and I think this has something to do with escaping or sanitizing the input before it is inserted but I can't work out how to do this in bash. Can anyone help?

dash-o · Accepted Answer · 2020-01-05 16:31:03Z

1

There are 2 typos in tag name use to extract the data from the XML.

  # Was book/id
book_id=$(xmlstarlet sel -t -v //book/book_id $line);
book_url=$(xmlstarlet sel -t -v //book/book_url $line);
description=$(xmlstarlet sel -t -v //book/description $line);
  # Was book/name
book_name=$(xmlstarlet sel -t -v //book/book_name $line);

This should make the insert work as expected.

Suggestion: For consistency, use double quote for book_id. Note that double quote MUST be used for the description, since it contain single quotes like film's.

values("$book_id","$book_url","$description","$book_name");

answered Jan 5, 2020 at 16:31

dash-o

14.7k1 gold badge14 silver badges43 bronze badges

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

1 Comment

ethelfrith Over a year ago

Thanks, I completely missed these typos, this now works as expected

Collectives™ on Stack Overflow

using bash to insert values into sqlite database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related