1// WARNING: In this sample, the query inputs are not sanitized. For production use, you should use sql builder
2// libraries like Knex.js (https://knexjs.org/) to prevent SQL injection.
3
4const fs = require('fs');
5const SQLite3 = require('sqlite3').verbose();
6const TABLE = 'annotations';
7
8module.exports = (app) => {
9 // Create a database if it doesn't exist
10 if (!fs.existsSync('server/xfdf.db')) {
11 fs.writeFileSync('server/xfdf.db', '');
12 }
13
14 // Create annotations table with columns documentId, annotationID and xfdfString
15 const db = new SQLite3.Database('server/xfdf.db');
16 db.run(`CREATE TABLE IF NOT EXISTS ${TABLE} (documentId TEXT, annotationId TEXT PRIMARY KEY, xfdfString TEXT)`);
17 db.close();
18
19 // Handle POST request sent to '/server/annotationHandler.js'
20 app.post('/server/annotationHandler.js', (req, res) => {
21 const documentId = req.query.documentId;
22 const annotationId = JSON.parse(req.body).annotationId;
23 const xfdfString = JSON.parse(req.body).xfdfString.replace(/\'/g, `''`); // To escape single quote character in SQLite
24
25 const db = new SQLite3.Database('server/xfdf.db');
26 db.serialize(() => {
27 const isDeleteCommand = /<delete>(.*)<\/delete>/s.test(xfdfString);
28
29 let query;
30 if (isDeleteCommand) {
31 // Instead of saving the delete command, we can remove the row from the database
32 query = `DELETE FROM ${TABLE} WHERE annotationId = '${annotationId}'`;
33 } else {
34 // Save document ID, annotation ID and XFDF string to database
35 query = `INSERT OR REPLACE INTO ${TABLE} VALUES ('${documentId}', '${annotationId}', '${xfdfString}')`;
36 }
37
38 db.run(query, err => {
39 if (err) {
40 res.status(500);
41 } else {
42 res.status(200);
43 }
44 res.end();
45 });
46 });
47 db.close();
48 });
49
50 // Handle GET request sent to '/server/annotationHandler.js'
51 app.get('/server/annotationHandler.js', (req, res) => {
52 const documentId = req.query.documentId;
53
54 const db = new SQLite3.Database('server/xfdf.db');
55 // Read from the database and send the rows as a response
56 db.all(`SELECT annotationId, xfdfString FROM ${TABLE} WHERE documentId = '${documentId}'`, (err, rows) => {
57 if (err) {
58 res.status(204);
59 } else {
60 res.header('Content-Type', 'application/json');
61 res.status(200).send(rows);
62 }
63 res.end();
64 });
65 db.close();
66 });
67}
68