0

I just started MySQL with Express. I am trying to fetch book details from the MySQL database with the book id, if the book id is given in integer it is showing the result, but when the book id is undefined then it is crashing the server, not handling the error with try-catch. connectDB.js file(for connecting to database)

const pool = mysql.createPool({
  connectionLimit: 10,
  multipleStatements: true,
  host: "localhost",
  user: "root",
  password: "password",
  database: "library_management",
  port: 3306,
});
pool.getConnection((error, connection) => {
  if (error) throw error;
  console.log("connected to db");
  connection.release();
});
export default pool;

books.js file (router)

import express from "express";
import pool from "../connectDB.js";
const booksRouter = express.Router();
booksRouter.get("/book/:bookId", async (req, res) => {
  try {
    const { bookId } = req.params;

    const query = `select * from books where book_id = ${bookId};`;
    pool.query(query, (error, result) => {
      if (error) throw error;
      res
        .status(200)
        .json({ access: true, msg: "fetched book details", bookDetails: result[0] });
    });
  } catch (error) {
     console.log(error);
    res.status(500).json({ access: false, error: "server error" });
  }
});

above code is crashing server, not handling error if the book id is undefined. And also when if I change table name from books to book(does not exist) then also it is crashing server.

/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_BAD_FIELD_ERROR: Unknown column 'undefined' in 'where clause'
    at Sequence._packetToError (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    --------------------
    at Pool.query (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Pool.js:199:23)
    at file:///home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/controllers/books.js:10:10
    at Layer.handle [as handle_request] (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/layer.js:95:5)
    at next (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/route.js:144:13)
    at Route.dispatch (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/route.js:114:3)
    at Layer.handle [as handle_request] (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/layer.js:95:5)
    at /home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:284:15
    at param (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:365:14)
    at param (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:376:14)
    at Function.process_params (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:421:3) {
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: "Unknown column 'undefined' in 'where clause'",
  sqlState: '42S22',
  index: 0,
  sql: 'select * from books where book_id = undefined;'
}

Node.js v18.12.1
[nodemon] app crashed - waiting for file changes before starting...
  • Use the [placeholder format](https://www.npmjs.com/package/mysql#escaping-query-values) to pass values into your query... `pool.query("select * from books where book_id = ?", [bookId])`. As for the value being `undefined`, what URL are you using on the client-side? – Phil Apr 24 '23 at 02:07
  • Does this answer your question? [Node-MySQL - Escaping in Query() Method vs Mysql.Escape() / Mysql.EscapeId()](https://stackoverflow.com/questions/25128055/node-mysql-escaping-in-query-method-vs-mysql-escape-mysql-escapeid) – Phil Apr 24 '23 at 02:08
  • @Phil I am asking for how to handle errors, this method will also throw error then again server will be crashed. And I am using get method and bookId retrieving from parameter of url. – Amber Ahmed Apr 30 '23 at 02:18

0 Answers0