0

i am a beginner programmer of nodejs with mysql. while making a crud application using node js with mysql ran into the problem with You have an error in your SQL syntax
when i trying to search the record what i tried so far i attached below.i couldn't find the error here.

Search

server.get('/api/student/:id',(req, res) => {
  var sql = "SELECT * FROM student WHERE id=" + req.params.id;
  
  con.query(sql, function (err, result, fields) {
          if (err) throw err;
          res.end(JSON.stringify(result));
      });
});

i attached the full error below.

Error: ER_PARSE_ERROR: 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 ':3' at line 1
    at Query.Sequence._packetToError (E:\nodemysqll\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (E:\nodemysqll\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (E:\nodemysqll\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (E:\nodemysqll\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (E:\nodemysqll\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (E:\nodemysqll\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (E:\nodemysqll\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (E:\nodemysqll\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (node:events:390:28)
    at addChunk (node:internal/streams/readable:315:12)
    --------------------
    at Protocol._enqueue (E:\nodemysqll\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Connection.query (E:\nodemysqll\node_modules\mysql\lib\Connection.js:198:25)
    at E:\nodemysqll\server.js:76:19
    at Layer.handle [as handle_request] (E:\nodemysqll\node_modules\express\lib\router\layer.js:95:5)
    at next (E:\nodemysqll\node_modules\express\lib\router\route.js:144:13)
    at Route.dispatch (E:\nodemysqll\node_modules\express\lib\router\route.js:114:3)
    at Layer.handle [as handle_request] (E:\nodemysqll\node_modules\express\lib\router\layer.js:95:5)
    at E:\nodemysqll\node_modules\express\lib\router\index.js:284:15
    at param (E:\nodemysqll\node_modules\express\lib\router\index.js:365:14)
    at param (E:\nodemysqll\node_modules\express\lib\router\index.js:376:14) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "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 ':3' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'SELECT * FROM student WHERE id=:3'
Phil
  • 157,677
  • 23
  • 242
  • 245
abi jega
  • 135
  • 9
  • Please [edit] your question to include the full error message. Also, you should use the [placeholder syntax](https://github.com/mysqljs/mysql#escaping-query-values) to make your queries safer – Phil Jan 12 '23 at 03:15
  • 1
    If you properly handle query values, you won't get errors but your request should look like `GET /api/student/3`, **not** `GET /api/student/:3` – Phil Jan 12 '23 at 03:21
  • 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). And this? [Express routes parameters](https://stackoverflow.com/q/34704593/283366) – Phil Jan 12 '23 at 03:22
  • Phil thanks is working now /api/student/3 use this way working – abi jega Jan 12 '23 at 03:23

0 Answers0