NML Says

Data Security 4 - OWASP Top Ten - SQL Injections

References for this Part

OWASP Top Ten 2025

Model Solutions Previous Lesson

Example Model DS.3.0 register.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

// register.js

const bcrypt = require('bcryptjs');
const sqlite3 = require('better-sqlite3');
const Rockyou = require('./Rockyou.js');

const argv = process.argv;
const argc = argv.length;
const db = new sqlite3('test.db');              // includes connect

const register = function(user, pwd) {
        let hash = bcrypt.hashSync(pwd);
        
        try {
                let sql = 'insert into user(userid, password) values(?, ?)';
                let stmt = db.prepare(sql);
                stmt.run(user, hash);
                return true;
        } catch(err) {
                console.log(`${err.message}`);
                return false;
        }
}


let entereduser = argv[2];
let enteredpassword = argv[3];
let rockyou = new Rockyou();

let exists = rockyou.search(enteredpassword);
if (!exists) {
        let res = register(entereduser, enteredpassword);
        if (!res) {
                console.log('db error');
        } else {
                console.log('You chose wisely');
        }
} else {
        console.log('inadequate password');
}

db.close();
Example Model DS.3.0 register.py
 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

# register.py

# https://docs.python.org/3/library/sqlite3.html for doc
# https://www.slingacademy.com/article/python-sqlite3-insert-new-row-get-id/

import bcrypt
import rock
import sqlite3
import sys



def register(user, pwd):
    con = sqlite3.connect('test.db')
    cur = con.cursor()

    salt = bcrypt.gensalt()
    hash = bcrypt.hashpw(pwd, salt)

    try:
        res = cur.execute('insert into user (id, userid, password) values(null, ?, ?)', (user, hash))
        con.commit()
        return True
    except sqlite3.Error as err:
        print(err.sqlite_errorcode, err.sqlite_errorname)
        return False

if __name__ == '__main__':
    argv = sys.argv
    entereduser = argv[1]
    enteredpassword = argv[2]

    rockyou = rock.Rockyou()
    exists = rockyou.search(enteredpassword)
    if not exists:
        enteredpassword = enteredpassword.encode()
        result = register(entereduser, enteredpassword)
        if not result:
            print('db error')
        else:
            print('inserted')
    else:
        print('inadequate password')

OWASP

The Open Worldwide Application Security Project (OWASP) is a nonprofit foundation that works to improve the security of software. Our programming includes:

  • Community-led open source projects including code, documentation, and standards
  • Over 250+ local chapters worldwide
  • Tens of thousands of members
  • Industry-leading educational and training conferences

Injections - Create a Situation

An obvious target for the hacker in order to try to gain unauthorized access to a system is the login screen. An alarming number of real life login situations are not well protected. We shall look at some issues to illustrate, and we shall see what we, as developers, can do to protect our applications.

The core of any web application is the router. Let us look at an example:

Example 1: The Router, vulnerable0/routes/router.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

'use strict';
/*
 * check if routed handler function exists
 * if yes call it, else complain
 */
const handlers = require('../controllers/handlers');    // handlers module
const requestHandlers = {                               // application urls here
        GET: {
                '/': handlers.home,
                '/page1': handlers.other,
                '/about': handlers.other,
                '/contact': handlers.other,
                '/contacts': handlers.contacts,
                '/login': handlers.login,
                '/logout': handlers.logout,
                '/notfound': handlers.notfound,
                'js': handlers.js,
                'css': handlers.css,
                'png': handlers.png,
                'svg': handlers.svg,
                'ico': handlers.ico
        },
        POST: {
                '/contact': handlers.receiveContacts,
                '/login': handlers.verifyLogin
        }
}

module.exports = {
        route(req, res, bodydata) {
                let urls = req.url.split('?');          // separate query string from url
                req.url = urls[0];                      // clean url
                let arr = req.url.split('.');           // filename with suffix
                let ext = arr[arr.length - 1];          // get suffix
                                                        // look for route
                if (typeof requestHandlers[req.method][req.url] === 'function') {
                                                        // if found, call
                        requestHandlers[req.method][req.url](req, res, bodydata);
                } else if (typeof requestHandlers[req.method][ext] === 'function') {
                                                        // if css, js, png, or ... get it
                        requestHandlers[req.method][ext](req, res);                             
                } else {                                                                        
                        requestHandlers.GET['/notfound'](req, res);                             
                                                        // use notfound
                }
        }
}

When we submit the login form we are issuing a POST request to the server. Please look at lines 25, and 35-36 above. The router will accordingly send us to the verifyLogin function in the following requesthandler code. This can easily be verified by displaying the source code of the form in your browser.

Example 2. The Handlers, vulnerable0/controllers/handlers.js, (fragment)
 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

'use strict';
/*
 * handlers.js
 * Requesthandlers to be called by the router mechanism
 */

// ...
const models = require("../models/dbCode");                 // models are datahandlers
// ...


        async verifyLogin (req, res, data) {
            let obj = lib.makeWebArrays(req, data)          // home made GET and POST objects
            let r = await models.verify(obj);
            console.log(`read for login-verify: ${r.length} = number read`);
            if (r.length > 0) {                                                           
                // anybody read
                cookie.set(req, res, '' + r[0].name);       // create login cookie
                res.writeHead(httpStatus.MOVED_TEMPORARILY, {   // write header
                    "Location": '/'
                });
                res.end();                                  // send it
            } else {
                res.writeHead(httpStatus.MOVED_TEMPORARILY, {   // write header
                    "Location": '/logout'
                });
                res.end();                                  // send it
            }
        },

// ...
Example 3. The Model, vulnerable0/models/dbCode.js (fragment)
 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

e strict';
/*
 * models
 * handlers for data manipulation
 */
const maria = require("mariadb");                               // file system access
const dbp = require('./dbParams.js');
const crypto = require('crypto');

module.exports = {

//...

    async verify(obj) {
        const dbh = await maria.createConnection(dbp);
        let email = obj.POST.email;
        console.log(`før sql: ${email} ${obj.POST.password}`);
        let pwd = await crypto.createHash('sha512')
                            .update(obj.POST.password, 'utf8')
                            .digest('hex');
        let sql = `select name, password
                   from user
                   where email = '${email}' and password = '${pwd}'`;
        console.log(`sql stmt: ${sql}`);
        let rows = await dbh.query(sql);
        return rows;
    }
}

This code connects to the database server, tries to read from the database based on the entered credentials, and returns the result to the controller that invoked it.

In order to verify a hashed password, we must hash the entered clear text password with the algorithm used to hash passwords in the system, and if the two hashes are equivalent, and the user ids match too, login is registered by issuing a cookie as a token.

Injections - Demo 0, SQL Injection

In the previous section we outlined an application where the two pertinent functionalities are user registration, and login. Pretend that the user registration is done securely, and the user has altered the password to a secret one.

We shall first demonstrate that required functionalities are in place. We start the server, and access it by linking to http://localhost:3000 in the browser. Then we shall

We have verified that something actually works. Let us verify that the database actually holds a hashed password, and thus meets the cornerstone of all user security that passwords are never stored as clear text.

Example 4. The Demo Database, vulnerable0
1
2
3
4
5
6
7
8
9

select * from user;
+-------+---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+--------------+---------+
| name  | email         | phone    | password                                                                                                                         | bio          | profile |
+-------+---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+--------------+---------+
| zebra | abekat@nml.dk | 12345678 | y                                                                                                                                | jaaah        |       0 |
| jane  | jane@doe.com  | 1236     | x                                                                                                                                | regular      |       0 |
| john  | john@doe.com  | 1235     | x                                                                                                                                | regular      |       0 |
| nmla  | nmla@iba.dk   | 1234     | ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff | Hi I'm admin |       1 |
+-------+---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+--------------+---------+

The hashes, done with SHA512, are 512 bits long, 64 bytes, and stored as hexidecimal digits amounting to 128 characters. This is pretty normal. We use blob as the attribute data type. This is useful because it means we do not have to change type if we update to a longer hash size.

Injections - Discussion of Demo 0

We verified that it works. We saw the database content.

What can possibly go wrong? The short answer to this rhetorical question is everything!

There are in the above at least three obvious vulnerabilities that we must discuss, and deal with.

We shall illustrate the problems and discuss countermeasures.

Challenging the Login

Let us look at the model code that responds to the login. We have shown it in Example 3, let us repeat lines 21 through 23 for easier reference:

Example 5. Fragment of a Fragment from Example 3
1
2
3
4
5
6
7

// ...
// email is from the html form
// pwd is a hash of the password from the html form
let sql = `select name, password
           from user
           where email = '${email}' and password = '${pwd}'`;
// ...

The practical demo could show a result of a login with nmla@iba.dk and test being:

Example 6. Resulting sql Statement
1
2
3

select name, password
       from user
       where email = 'nmla@iba.dk' and password = 'ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff'

meaning that the user input is taken as is, no questions asked. That is bad. The above sentence can be used directly in an interactive session with mariadb:

Example 7. Try in native mariadb
1
2
3
4
5
6
7

MariaDB [vulnerable0]> select name, password                    from user                    where email = 'nmla@iba.dk' and password = 'ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff';
+------+----------------------------------------------------------------------------------------------------------------------------------+
| name | password                                                                                                                         |
+------+----------------------------------------------------------------------------------------------------------------------------------+
| nmla | ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff |
+------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

Everything looks fine and innocent. Now enter the read team. Given the login form they will enter

Example 8. Red Team Action
1
2

Userid:     ' or 1=1; -- 
Password:   whatever cats footprint

resulting in

Example 9. Injected sql Statement
1
2
3

select name, password
       from user
       where email = '' or 1=1; -- ' and password = '1199088afb557c65bfc348ff6b8522a8fcc19d9f3783d778568c66a61ba05921aefa07384a3899685fe9f51563ab67b54dae5fcb937ed5b8292e1cad9470ac04'
Example 10. The Injected Statement in mariadb
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

MariaDB [vulnerable0]> select name, password
    ->                    from user
    ->                    where email = '' or 1=1; -- ' and password = '1199088afb557c65bfc348ff6b8522a8fcc19d9f3783d778568c66a61ba05921aefa07384a3899685fe9f51563ab67b54dae5fcb937ed5b8292e1cad9470ac04'
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| name  | password                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| zebra | y                                                                                                                                |
| jane  | x                                                                                                                                |
| john  | x                                                                                                                                |
| nmla  | ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)

Alternatively, it is better for red team to enter as an administrator, and if they know that nmla@iba.dk is the administrator, they might try that in the user field as nmla@iba.dk'; -- and with whatever in the password field resulting in

1
2
3

let sql = `select name, password
from user
where email = 'nmla@iba.dk'; -- ' and password = 'whatever'`;

In both cases the injection allows the red team to gain access bypassing the password requirement.

A successful read, a successful login. This is the quintessential sql injection example.

The issue here is that red team uses the ', apostrophe, the sql string delimiter, to intersect the declaration logic, and then the ;, semicolon, to end the declaration and the -- , dash, dash, space, to leave the rest as an sql comment. In other words building an sql declaration with unsanitized text input from the user is a risk. The metacharacters from sql should not be allowed in critical user input.

Sanitizing may be done in the client, or the server, or both. The client alone is insufficient. The red team may easily replace the HTML5 form with one of their own making. Doing it client side is optimal as it will result in speedy error messages to the ordinary user. Minimally it must be done server side.

The server side must either escape all critical characters, or use prepared statements for the sql declarations. The latter is best practice, it implies escaping, and is a must.

Optimizing the Hashing

If the evil hacker should see the above he would notice that, of course, and in a few seconds he would also notice that users anat, and user tulle have equal hashed passwords. This means that the passwords have probably not been salted while being hashed. At least not with individually randomized salts. Apart from the fact that cracking one users password gives away other users with the same password, this also means that the time consumption for cracking the passwords is shorter. In other words, we have helped the hacker.

Salting means, as discussed earlier, adding a random element to the hashing process so that cracking will be more time consuming and harder. It also means that the same password will hash differently in different hashing processes. This entails that the salt must be stored alongside the hash, otherwise it would be impossible to verify passwords in the login process. It also renders the sql code in example 9 useless.

On login the application takes the entered password, hashes it, and compares it to the stored hashed password. If they match, login is succesful. But, if salting the entered password is done with random salt, there is no way we can create the same hash in the login process. This problem is not insurmountable, we must change the code so that we first read the password and the salt, and then do the verification based on the read salt.

A subtle difference that does not allow the sql select to do the verification.

Sanitizing

We shall not dive into that at this time. It is common development practice, but we must remember to do it at each tier of the client server model.

Using Prepared Statements

Using prepared statements is also, or should also be common development practice. We shall show you a simple example.

We shall show you two fragments of examples 12 and 13 the previous lesson:

Prepared Statement in sql from Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# insert into the database table - neccessary in register
sql = "insert into user(id, userid, password) values (null, ?, ?)"
res = cur.execute(sql, (argv[1], 'x'))
con.commit()

# select all rows from table
sql = 'select * from user'
res = cur.execute(sql)
print(res)


# select one from table - neccessary before compare/verify
sql = 'select userid, password from user where userid = ?'
res = cur.execute(sql, (argv[1]))
print(res);
Prepared Statement in sql from JavaScript
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// insert into the database table - neccessary in register
sql = "INSERT INTO user(userid, password) VALUES (?, ?)";
stmt = db.prepare(sql);
res = stmt.run(argv[2], 'x');
console.log(res);


// select all rows from table
sql = 'select * from user';
stmt = db.prepare(sql);
rows = stmt.all();
console.log(rows);

// select one from table - neccessary before compare/verify
sql = 'select userid, password from user where userid = ?';
stmt = db.prepare(sql);
rows = stmt.get(argv[2]);
console.log(`${rows.userid}, password=${rows.password}`);

Exercises

The rules for handing in assignments may be found in the README

Documentation in the following exercises MUST be written into the README of the exercise as a description of what to do, and what results to expect.

Exercise DS.4.0

Test your solution to exercise In Exercise DS.3.0 You built a registration program. Now you must build a login mechanism using the user table from the database.

Let the program respond by printing id, and userid if the login succeeds, and error if it fails. Verify and document that the program is immune to sql injection.

Exercise DS.4.1

Create a new version of your solution to Exercise DS.3.0 where you replace the use of bcrypt with sha256.

You SHOULD create a user256 table in that database to avoid creating a second database.

1
2
3
4
5
6

CREATE TABLE user256 (
id integer primary key autoincrement,
userid varchar(32) unique not null,
password blob nut null,
comments text not null
);

Please notice that there is a comments column. Do not omit it. It is for later use. Then build a login mechanism using this database table.

Let the program respond by printing id, and userid if the login succeeds, and error if it fails. Verify and document that the program is immune to sql injection.