localhost:8800/api/auth/register 500 (Internal Server Error) when trying to register a new user

Question

I'm trying to create a website with register/login/logout functions using react and mysql, right now I'm kinda stuck in the registration part. When trying to create a new user I'm getting this error:

p://localhost:8800/api/auth/register 500 (Internal Server Error)

If I try to write localhost:8800/api/auth/register in url bar this is what I find in the screen: Cannot GET /api/auth/register

This is the code I'm using:

server/index.js

import express from "express";
import authRoutes from "./routes/auth.js"
import cookieParser from "cookie-parser"
import cors from "cors"


const app = express()

// middlewares
app.use(express.json())
app.use(cors())
app.use(cookieParser())

app.use("/api/auth", authRoutes);


app.listen(8800, () => {
    console.log("API working!");
  });

server/connect.js

import mysql from "mysql"

export const db = mysql.createConnection({
  host:"localhost",
  user:"root",
  password:"password",
  database:"auth"
})

server/routes/auth.js

import express from "express";
import { login,register,logout } from "../controllers/auth.js";

const router = express.Router()

router.post("/register", register)
router.post("/login", login)
router.post("/logout", logout)


export default router

server/controllers/auth.js

import { db } from "../connect.js";
import bcrypt from "bcryptjs";
import jwt from "jsonwebtoken";

export const register = (req, res) => {
  //CHECK USER IF EXISTS

  const q = "SELECT * FROM users WHERE username = ?";

  db.query(q, [req.body.username], (err, data) => {
    if (err) return res.status(500).json(err);
    if (data.length) return res.status(409).json("User already exists!");
    //CREATE A NEW USER
    //Hash the password
    const salt = bcrypt.genSaltSync(10);
    const hashedPassword = bcrypt.hashSync(req.body.password, salt);

    const q =
      "INSERT INTO users (`username`,`email`,`password`) VALUE (?)";

    const values = [
      req.body.username,
      req.body.email,
      hashedPassword,
    ];

    db.query(q, [values], (err, data) => {
      if (err) return res.status(500).json(err);
      return res.status(200).json("User has been created.");
    });
  });
};

export const login = (req, res) => {
  const q = "SELECT * FROM users WHERE username = ?";

  db.query(q, [req.body.username], (err, data) => {
    if (err) return res.status(500).json(err);
    if (data.length === 0) return res.status(404).json("User not found!");

    const checkPassword = bcrypt.compareSync(
      req.body.password,
      data[0].password
    );

    if (!checkPassword)
      return res.status(400).json("Wrong password or username!");

    const token = jwt.sign({ id: data[0].id }, "secretkey");

    const { password, ...others } = data[0];

    res
      .cookie("accessToken", token, {
        httpOnly: true,
      })
      .status(200)
      .json(others);
  });
};

export const logout = (req, res) => {
  res.clearCookie("accessToken",{
    secure:true,
    sameSite:"none"
  }).status(200).json("User has been logged out.")
};

src/pages/Register.jsx

import React, { useState } from 'react'
import { Link } from 'react-router-dom';
import "./register.scss";
import axios from "axios"

const Register = () => {
    const [inputs, setInputs] = useState({
        username:"",
        email:"",
        password:"",
    })

    const [err, setErr] = useState(null)


    const handleChange = (e) => {
        setInputs((prev) => ({ ...prev, [e.target.name]: e.target.value }));
      };

    const handleClick = async (e) => {
        e.preventDefault()
        try {
            await axios.post("http://localhost:8800/api/auth/register", inputs)
        } catch(err) {
            setErr(err.response.data)
        }
    }
    console.log(inputs)


  return (
    <div className='register'>
        <div className="card">
            <div className="left">
                <h1>test-II</h1>
                <p>Lorem ipsum dolor sit amet, consectetur adipisci elit, sed do eiusmod tempor incidunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrum exercitationem ullamco laboriosam, nisi ut aliquid ex ea commodi consequatur.</p>
                <span>Hai già un account?</span>
                <Link to="/login">
                    <button>Login</button>
                </Link>
            </div>
            <div className="right">
                <h1>Registrazione</h1>
                <form>
                    <input type="text" placeholder='Username' name="username" onChange={handleChange} />
                    <input type="email" placeholder='Email' name="email" onChange={handleChange} />
                    <input type="password" placeholder='Password' name="password" onChange={handleChange} />
                    {/* <p>{err && err}</p> */}
                    <button onClick={handleClick}>Registrati</button>
                </form>
            </div>
        </div>
    </div>
  )
}

export default Register;

Answer 1

The query is incorrect. It should be VALUES and there should be three ? as you are inserting values for three columns.

Also, note that you don't need [values] just values is enough for the .query()'s parameter.

const q = "INSERT INTO users (`username`,`email`,`password`) VALUES (?, ?, ?)";

const values = [
    req.body.username,
    req.body.email,
    hashedPassword,
];

db.query(q, values, (err, data) => {
    if (err) return res.status(500).json(err);
    return res.status(200).json("User has been created.");
});