Difference between .query() and .execute() in MySQL

user_4668155 0
Viewed 7955

I'm having difficulty comprehending the implementation of prepared statements. I've done a fair amount of research but most of the information I found is either out of context or contain examples far more complex than what I'm trying to accomplish. Can anyone clarify for me why the execute method in the second example below is throwing a syntax error?

NOTE: I'm using the node-mysql2 package here.

controller.js (using query mysql method)

  const db = require("../lib/database");


  async addNewThing(req, res, next) {

    let data = req.body

    const queryString = 'INSERT INTO table SET ?'
    try {
      await db.query(queryString, data)
      res.status(201).json({
        message: 'Record inserted',
        data
      })
    } catch (error) {
      next(error)
    }
  }

Record is successfully inserted into the database

controller.js (using execute mysql method)

  const db = require("../lib/database");


  async addNewThing(req, res, next) {

    let data = req.body

    const queryString = 'INSERT INTO table SET ?'
    try {
      await db.execute(queryString, [data])
      res.status(201).json({
        message: 'Record inserted',
        data
      })
    } catch (error) {
      next(error)
    }
  }

Results in the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

data

{ thing_id: '987654', thing_name: 'thing' }
express node.js syntax-error mysql
1 Answers

With .query(), parameter substitution is handled on the client, including objects which let data = req.body is in the above examples.

With .execute() prepared statement parameters are sent from the client as a serialized string and handled by the server. Since let data = req.body is an object, that's not going to work.

user_46681550
Related