Data Security 8

Model Solution to Exercises from the Previous Lesson

This model solution is available in total from https://codeberg.org/arosano/restapi_done.git

Example 1. `routes/users.js`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


const express = require('express');
const router = express.Router();

const con = require('../controllers/controllers');

/* GET user register ie send form */
router.get('/register', function(req, res, next) {
    res.render('register', {
        title: 'Please Register',
        subtitle: 'Follow the embedded cues'
    });
});

router.post('/register', con.handleRegistration, function(req, res, next) {
    res.status(201).json({message: 'Registration succesfull'});
});

router.get('/login', function(req, res, next) {
    res.render('login', {
        title: 'Please Login'
    });
});

router.post('/login', con.handleLogin, function(req, res, next) {
    res.status(200).json("Bearer " + res.locals.token);
}); 

router.patch('/toggleAdmin/:email', con.isAuth, con.isAdmin, con.toggleAdmin, function(req, res, next) {
    res.status(201).json('Update successful');
}); 


module.exports = router;

Example 2. `routes/index.js`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55


const express = require('express');
const router = express.Router();

const conw = require('../controllers/controllersworld');
const con = require('../controllers/controllers');

const TITLE = 'Rest API Pattern Project';

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', {
        title: TITLE,
        subtitle: 'Front Page'
    });
});


/* API endpoints */

/* GET continents */
router.get('/continents', con.isAuth, conw.getContinents, function (req, res) {
    // variables from middleware
    res.json({continents: res.locals.continents});
});

/* GET countries */
router.get('/countries', con.isAuth, conw.getCountries, function (req, res) {
    // variables from middleware
    res.json({countries: res.locals.countries});
});

/* GET cities */
router.get('/cities', con.isAuth, conw.getCities, function (req, res) {
    // variables from middleware
    res.json({cities: res.locals.cities});
});

/* GET cities from a country */
router.get('/cities/:ctry', con.isAuth, conw.getCitiesCtry, function (req, res) {
    // variables from middleware
    res.json({cities: res.locals.cities});
});

/* post city */
router.post('/city', con.isAuth, con.isAdmin, conw.postCity, function (req, res) {
    res.status(201).json({message: "City in db"});
});

/* GET languages */
router.get('/languages', con.isAuth, conw.getLanguages, function (req, res) {
    // variables from middleware
    res.json({languages: res.locals.languages});
});

module.exports = router;

Example 3. `controllers/controllers.js`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97


/* controllers.js */
require ('dotenv').config();

const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const model = require('../models/dbhandlers');

const SU = 0;           // admin

module.exports = {

    handleLogin: async function(req, res, next) {
        let errmsg = 'Error in credentials\n';
        let errmsgt = 'Error in token signing\n';
        try {
            await model.getUser(req, res, next);
            let rc = await bcrypt.compare(req.body.password, res.locals.user.passwor
d);
            if (!rc)
                throw new Error(errmsg);

            const payload = { email: res.locals.user.email, profile: res.locals.user
.profile };
            const lifetime = { expiresIn: '1h' };
            let token = await jwt.sign(payload, process.env.SECRET, lifetime);
            if (!token)
                throw new Error(errmsgt);
            res.locals.token = token;

            next();
        } catch (err) {
            return res.status(500).json({message: err.message});
        }
    },

    handleRegistration: async function(req, res, next) {
        try {
            let hash = await bcrypt.hash(req.body.password, parseInt(process.env.ROU
NDS));
            res.locals.hash = hash;
            await model.insertUser(req, res, next);
            next();
        } catch (err) {
            console.log(err);
            return res.status(500).json({message: err.message});
        }
    },

    isAuth: async function(req, res, next) {
        try {
            let errmsg = 'You must be logged in';
            let token = req.headers.authorization && req.headers.authorization.split(' ')[1];
            if (!token)
                throw new Error(errmsg);
            errmsg = 'Failed to authenticate token';
            let rc = await jwt.verify(token, process.env.SECRET);
            console.log(rc);
            if (!rc)
                throw new Error(errmsg);

            res.locals.authorized = true;
            res.locals.profile = rc.profile;
            next();
        } catch(err) {
            res.status(500).json({message: err.message});
        }
    },

    isAdmin: function(req, res, next) {
        try {
            if (res.locals.authorized && res.locals.profile == SU) 
                next();
            else
                throw new Error('You must be a logged in admin');
        } catch(err) {
            res.status(500).json({message: err.message});
        }
    },

    toggleAdmin: async function(req, res, next) {
        try {
            req.body.email = req.params.email;
            await model.getUser(req, res, next);
            if (!res.locals.user)
                throw new Error('User not found');
            if (res.locals.user.profile == SU) {
                let count = await model.countAdmins();
                if (count <= 1)
                    throw new Error('Cannot remove last admin');
                await model.toggleAdmin(req, res, next);
            } 
            next();
        } catch(err) {
            res.status(500).json({message: err.message});
        }
    }
}

Example 4. `controllers/controllersworld.js`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


const models = require('../models/dbhandlers');

module.exports = {
    getContinents: async function (req, res, next) {
        let rows = await models.getAllContinents(req, res, next);
        res.locals.continents = rows;
        next();
    },

    getCountries: async function (req, res, next) {
        let rows = await models.getAllCountries(req, res, next);
        res.locals.countries = rows;
        next();
    },

    getCities: async function (req, res, next) {
        let rows = await models.getAllCities(req, res, next);
        res.locals.cities = rows;
        next();
    },

    getCitiesCtry: async function (req, res, next) {
        let rows = await models.getCitiesCtry(req, res, next);
        res.locals.cities = rows;
        next();
    },

    postCity: async function (req, res, next) {
        try {
            await models.insertCity(req, res, next);
            next();
        } catch(err) {
            console.log(err);
            return res.status(500).json({message: err.message});
        }
    },

    getLanguages: async function (req, res, next) {
        let rows = await models.getAllLanguages(req, res, next);
        res.locals.languages = rows;
        next();
    }

}

Example 5. `models/dbhandlers.js`

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141


const path = require('path');
const sqlite3 = require("better-sqlite3");
const SU = 0;

// Start db connection
const connect = async function () {
    try {
        const db = await new sqlite3(path.resolve('db/sampleAPI.db'), {fileMustExist: true});
        return db;
    } catch (err) {
            console.error(err);
    }
};

module.exports = {
    countAdmins: async function() {
        try {
            let db = await connect();
            let sql = 'select count(*) as c from user where profile = ?';
            let query = db.prepare(sql);
            let row = await query.all(SU);
            return row[0].c;
        } catch (err) {
            return 0;
        }
    },

    getAllCities: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from city';
            let query = db.prepare(sql);
            let rows = await query.all();
            return rows;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    getAllContinents: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from continent';
            let query = db.prepare(sql);
            let rows = await query.all();
            return rows;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    getAllCountries: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from country';
            let query = db.prepare(sql);
            let rows = await query.all();
            return rows;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    getAllLanguages: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from countrylanguage';
            let query = db.prepare(sql);
            let rows = await query.all();
            return rows;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    getCitiesCtry: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from city where countrycode = ?';
            let query = db.prepare(sql);
            let rows = await query.all(req.params.ctry);
            return rows;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    getUser: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'select * from user where email = ?';
            let query = db.prepare(sql);
            let row = await query.get(req.body.email);
            res.locals.user = row;
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    insertUser: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'insert into user (email, password, bio) values(?, ?, ?)';
            let query = db.prepare(sql);
            let row = await query.run(req.body.email, res.locals.hash, req.body.bio);
            res.locals.user = row
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    insertCity: async function (req, res, next) {
        try {
            let db = await connect();
            let sql = 'insert into city values(?, ?, ?, ?)';
            let query = db.prepare(sql);
            let row = await query.run(req.body.name, req.body.countrycode, req.body.district, req.body.population);
        } catch (err) {
            res.status(400).json(err.message);
        }
    },

    toggleAdmin: async function(req, res, next) {
        try {
            let db = await connect();
            await this.getUser(req, res, next);
            console.log(res.locals.user);
            let pro = res.locals.user.profile;
            if (pro == SU) 
                pro = 99;
            else
                pro = SU;
            let sql = 'update user set profile = ? where email = ?';
            let query = db.prepare(sql);
            await query.run(pro, res.locals.user.email);
        } catch (err) {
            res.status(400).json(err.message);
        }
    }

}

Example 6. Testing by `curlit.sh`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


#!/usr/bin/env sh
TOKEN=`curl -s -d email=$1 -d password=$APIPWD http://localhost:3000/users/login`
TOKENC=$(echo "$TOKEN" | sed 's/"//g')
curl -H "Authorization: ${TOKENC}" \
     -d "name=$2" \
     -d "population=$3" \
     -d "district=$4" \
     -d "countrycode=$5" \
     -s http://localhost:3000/city
echo ""
curl -s http://localhost:3000/continents
echo ""
curl -H "Authorization: ${TOKENC}" \
     -s http://localhost:3000/cities/DNK
echo ""
curl -X PATCH \
     -H "Authorization: ${TOKENC}" \
     -s http://localhost:3000/users/toggleAdmin/admin@nml.nml

Example 7. `curlit2.sh`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


#!/usr/bin/env sh
TOKEN=`curl -s -d email=$1 -d password=$APIPWDQ http://localhost:3000/users/login`
TOKENC=$(echo "$TOKEN" | sed 's/"//g')
curl -H "Authorization: ${TOKENC}" \
     -d "name=$2" \
     -d "population=$3" \
     -d "district=$4" \
     -d "countrycode=$5" \
     -s http://localhost:3000/city
echo ""
curl -s http://localhost:3000/continents
echo ""
curl -H "Authorization: ${TOKENC}" \
     -s http://localhost:3000/cities/DNK
echo ""

Today

Todays effort is the exercise. Read on please.

Exercises

Exercise DS.8.0

Figure 1. The World Database, `sampleAPI.db`

Logging In

This is an ER-diagram illustrating the structure of the world database as we worked with in the previous exercise. It has been slightly changed, and todays exercise is implementing the changes in a branch of the repo of the previous solution.

Today we must

Add two tables to the database:
- A Course table
- and an Exam table
The bio attribute in the user user table must be encrypted.
The grade attribute in the exam must also be encrypted
The bio attribute in the user table must only be visible to an admin user and to the user it belongs to.
The grade attribute must also be restricted to the same audience as the bio attribute.
The exam can only apply to a course and user already in the database.

You must decide on a reasonable encryption algorithm available to the chosen programming language.

I will give you the necessary SQL statements to update the database.

Example 8. The Necessary Extra SQL for `sampleAPI.db`

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20



create table course (
    course varchar(64) unique not null
);

create table exam (
    email varchar(64) not null,
    course varchar(64) not null,
    grade text not null,
    juncture datetime not null,
    primary key(email, course, juncture),
    foreign key(email) references user(email),
    foreign key(course) references course(course)
);

insert into course values('Data Security');
insert into course values('Data Integration');
insert into course values('Integrated Development Environments');
insert into course values('Web programming I');
insert into course values('Web Programming II');

You will put them into a file, say upddb.sql, and execute them by

1

.read upddb.sql

Data Security 8

Model Solution to Exercises from the Previous Lesson

Example 1. routes/users.js

Example 2. routes/index.js

Example 3. controllers/controllers.js

Example 4. controllers/controllersworld.js

Example 5. models/dbhandlers.js

Example 6. Testing by curlit.sh

Example 7. curlit2.sh