Why is my MariaDB query not working on host but works locally?

Question

This is my code that works fine locally, but not on Namecheap host:

//data connection pool
const pool = require('../models/database');
const bcrypt = require('bcrypt');

module.exports = async (req, res) => {
    try {
        const {user_name, password, email} = req.body;
        const hashedPassword = await bcrypt.hash(password, 10);
        console.log(hashedPassword);

        let sql = `INSERT INTO userdb (user_name, password, email)
        SELECT ?, ?, ? 
        WHERE NOT EXISTS (SELECT 1 FROM userdb WHERE email = ? OR user_name = ?);
        SHOW WARNINGS;`;

        let result = await pool.query(sql, [user_name, hashedPassword, email, email, user_name]);
        
        console.log(result);
        console.log("the id: ", result[0].insertId);
        res.status(200).send("Success Comrad!");

        } catch (error) {
        res.status(400).send(error.message);
    }
    
}//module.export end

These are my dependencies:

"dependencies": {
    "bcrypt": "^5.0.1",
    "dotenv": "^16.0.0",
    "express": "^4.17.2",
    "mariadb": "^2.5.5",
    "pug": "^3.0.2"
  }

This is the error I'm getting:

(conn=1698954, no: 1064, SQLState: 42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT 1 FROM userdb WHERE email = '[email protected]' OR us...' at line 3 sql: INSERT INTO userdb (user_name, password, email) SELECT ?, ?, ? WHERE NOT EXISTS (SELECT 1 FROM userdb WHERE email = ? OR user_name = ?); SHOW WARNINGS; - parameters:['dookie','$2b$10$OQp5lovlqmMfKNQtuvBK9e7t4rX39nVW22XdhzDZqBnSgDKv6G0ky','d...]

I also do have this set: multipleStatements: true Any ideas why this may be?

Answer 1

I solved this by approaching it differently. My main goal is to prevent duplicate entries.

I first set a unique constraint in PHPMyAdmin by doing this:

ALTER TABLE userdb 
ADD CONSTRAINT UC_email UNIQUE (email)

and then simplified my query:

let sql = `INSERT INTO userdb (user_name, password, email)
        VALUES (?,?,?)`;