04 Database - kjellhex/diode GitHub Wiki

A database-driven service is probably the first idea that comes to mind when thinking about a rest service, so let's build that now. We have already seen how to use JSON with Diode - let's examine a case where we are using XML instead.

We need one script to set up a database to work on - let's use SQLite3 to keep things simple. A simple server and a service that can fetch a record or update a record will be enough to demonstrate how Diode can be used to quickly build a data-driven service.

Create a simple library database with a books table by placing the following code into a file named create-db.rb:

require 'sqlite3'
db = SQLite3::Database.new "library.db"
db.execute("DROP TABLE IF EXISTS books;")
db.execute("CREATE TABLE IF NOT EXISTS books ( id int primary key, title text, author text );")
db.execute("INSERT INTO books (id, title, author) VALUES (?,?,?)", [3, "War and Peace", "Leo Tolstoy"])
db.execute("INSERT INTO books (id, title, author) VALUES (?,?,?)", [7, "Disintegration", "Andrei Martyanov"])
puts("[+] sample database created")

and execute it:

$ ruby create-db.rb
[+] sample database created

Create a simple server in db-server.rb:

require 'diode/server'
require 'sqlite3'
$db = SQLite3::Database.new "library.db"
routing = [
  [%r{^/book$}, "Book"]
]
load 'book.rb'
Diode::Server.new(3999, routing).start()

Place the service code in book.rb:

class Book

  def serve(request)
    request.method == "GET" ? get(request) : post(request)
  end

  def get(request)
    if request.params.key?("id")
      bookid = request.params["id"] || ""
      return Diode::Response.xml(404, "<reason>no id</reason>") if bookid.empty?
      row = $db.execute("SELECT * FROM books WHERE id=?", bookid.to_i()).first()
      return Diode::Response.xml(404, "<reason>not found</reason>") if row.empty?
      bookid, title, author = row
      body = assemble_xml(bookid, title, author)
    else # return all books
      list = $db.execute("SELECT * FROM books").collect{ |id,title,author|
        assemble_xml(id, title, author)
      }
      body = "<list>\n#{list.join("\n")}\n</list>"
    end
    Diode::Response.xml(200, body)
  end

  def post(request)
    return Diode::Response.xml(400, "<reason>not xml</reason>") unless request.headers["Content-Type"].downcase().end_with?("/xml")
    request.hash_xml()  # parse the XML payload into the request.fields hash
    p request
    bookid = request.fields["id"] || ""
    title = request.fields["title"] || ""
    author = request.fields["author"] || ""
    return Diode::Response.xml(400, "<reason>need id, title, author</reason>") if bookid.empty? or author.empty? or title.empty?
    $db.execute("UPDATE books SET title=?, author=? WHERE id=?", [title, author, bookid])
    Diode::Response.xml(200, "<status>success</status>")
  rescue
    Diode::Response.xml(500, "<status>failure</status>")
  end
  
  def assemble_xml(id, title, author)
    ["<book id=\"#{bookid}\">",
     "  <title>#{title}</title>",
     "  <author>#{author}</author>",
     "</book>"
    ].join("\n")
  end

end

Execute the sample code by running:

$ ruby db-server.rb

and visit http://127.0.0.1:3999/book to see a list of all the books:

<list>
  <book id="3">
    <title>War and Peace</title>
    <author>Leo Tolstoy</author>
  </book>
  <book id="7">
    <title>Disintegration</title>
    <author>Andrei Martyanov</author>
  </book>
</list>

If you would like to see the details of just one book, provide its identifier: http://127.0.0.1:3999/book?id=3

<book id="3">
  <title>War and Peace</title>
  <author>Leo Tolstoy</author>
</book>

And to update the details of War and Peace, POST to the same URL:

<book id="3">
  <title>War and Peace</title>
  <author>Richard Haas</author>
</book>

Setting up the database is trivial for anyone with experience of SQL, and for everyone else the SQLite3 documentation is a great place to start. The only interesting aspect of the server is that we create a reference to the database in the global $db variable so that it is available to any service.

If no id parameter is given in the request URL, then all records are returned. The query returns all books, and we collect a string for each book, join them with newlines, and wrap it all in a <list> tag.

else # return all books
  list = $db.execute("SELECT * FROM books").collect{ |id,title,author|
    assemble_xml(id, title, author)
  }
  body = "<list>\n#{list.join("\n")}\n</list>"
end

Each record is formatted by the assemble_xml method:

def assemble_xml(id, title, author)
  ["<book id=\"#{bookid}\">",
   "  <title>#{title}</title>",
   "  <author>#{author}</author>",
   "</book>"
  ].join("\n")
end

For a specific record, given by the id parameter, the service needs to get the value of the id parameter, defaulting to empty string if there is no value. If the id is empty, reject the request with a 400 Bad Request response. Try to get the record from the database - the result is a list of lists, where each inner list contains the values for the record. If the outerlist is empty then no record was found, and we reject the request with a 400 response:

if request.params.key?("id")
  bookid = request.params["id"] || ""
  return Diode::Response.xml(404, "<reason>no id</reason>") if bookid.empty?
  row = $db.execute("SELECT * FROM books WHERE id=?", bookid.to_i()).first()
  return Diode::Response.xml(404, "<reason>not found</reason>") if row.empty?

Only if the record was found does the service return the corresponding XML:

  bookid, title, author = row
  body = assemble_xml(bookid, title, author)
end
Diode::Response.xml(200, body)

Since the Book service is producing XML, we use Response.xml instead of Response.new because Diode defaults to JSON.

The Book service updates the database if a valid record is posted to the /book path. The first thing to check is that the request is XML of some kind, either text/xml or application/xml. If not, the request is rejected with a 400 Bad Request response.

  def post(request)
    return Diode::Response.xml(400, "<reason>not xml</reason>") unless request.headers["Content-Type"].downcase().end_with?("/xml")

For JSON payloads, the json gem provides excellent support for parsing and generating JSON. For XML, however, there is a long and sorry history of XML parsers having security holes, due in part to the nature of the XML specification. Diode took a design decision not to use a full-featured XML parser but to instead insist on a very strict format which lends itself to safe parsing.

The strict format requires a data record to be flat (no hierarchy, like the columns in a relational database table). The parent tag (eg. record) can be named anything and it may have an optional id attribute. That is the only attribute that is meaningful - any other attributes on this tag or its children are ignored. Only direct children are allowed, and for each child the tagName is the name of the field, and the tag content is the value of that field. All values are treated as strings.

<record id="3">
  <fieldname>Value</fieldname>
  <another>17</another>
</record>

This strict format is not flexible by design. It allows for communicating the values of the fields of a record, and the unique identifier of that record, and nothing else. No entities, no expansion, no XXE vulnerabilities. Small, simple, secure - that's what we like. It allows Diode to implement a strict, secure XML parser which is accessed through the request.hash_xml() method:

    request.hash_xml()  # parse the XML payload into the request.fields hash
    p request
    bookid = request.fields["id"] || ""
    title = request.fields["title"] || ""
    author = request.fields["author"] || ""

The hash_xml method reads the body and parses the XML into a hash named fields, so the XML:

<book id="3">
  <title>War and Peace</title>
  <author>Richard Haas</author>
</book>
``
is parsed by `hash_xml` into:
```ruby
{
  "id" => "3",
  "title" => "War and Peace",
  "author" => "Richard Haas"
}

Now that the field values have been extracted, the update request is rejected if any of the three fields is empty. We omit here any more detailed input validation. Finally, the database record is updated with the given data, and a success response returned. Should anything go wrong with the database operation, a failure message is returned instead.

    return Diode::Response.xml(400, "<reason>need id, title, author</reason>") if bookid.empty? or author.empty? or title.empty?
    $db.execute("UPDATE books SET title=?, author=? WHERE id=?", [title, author, bookid])
    Diode::Response.xml(200, "<status>success</status>")
  rescue
    Diode::Response.xml(500, "<status>failure</status>")
  end

You don't want just anyone accessing your data, so let's add some authentication with the 05 Filters tutorial.