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

Question

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' }

Holpen

Can you share an example of the data you're passing to the query? – Yvonneyvonner

@Yvonneyvonner Sure, see above. – Holpen

Answer 1

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.

Holpen

Answer 2

It is a good practice to pass dynamically changing inputs as parameters and not by string concatenation. This example might help.

Query - SELECT * from users where users.id = ? LIMIT ? Params - [1,10]

.query() - SELECT * from users where users.id = 1 LIMIT 10 ✅

.execute() - SELECT * from users where users.id = 1 LIMIT ? ❌

So in this case, .execute() will throw an error code: 'ER_WRONG_ARGUMENTS', as the limit's value has to be part of the query and cannot be passed as input params later.

Lauer