In the model code that responds to the login we have shown in Example 43.3, repeated here for your reading convenience:

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;
}

Assume the user enters tulle@x15.dk, and test. The test hashes to ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88... in line 5 above.

This means that in line 8-10 the sql will be:

let sql = `select name, password
from user
where email = 'tulle@x15.dk' and password = 'ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff'`;

and there will be exactly one match in the database. One? The combination exists, and the email address is the primary key of the user table thus appearing only once. Now, enter the red team. Its member will enter

' or 1=1; -- 

in the user field, and whatever in the password field resulting in

let sql = `select name, password
from user
where email = '' or 1=1; -- 'and password = 'whatever'`;

meaning that the de facto executed sql declaration will be

select name, password
from user
where email = '' or 1=1;

because the rest is comment. The result will be that all user rows will be read and the login cookie will be set. the red team is in!

Alternatively, it is better for red team to enter as 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

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.

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.

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 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 43.3 line 38-40 useless.

On login the application takes the entered password, hashes it (line 35), 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.

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.