Node-mysql2: Difference between .query() and .execute()

[dashmug]

I was under the impression that .execute() is always better because of prepared statements.

I cannot explain the following though.

This does not work (using .execute())...

const sql = `INSERT INTO ${this.table} SET ?`

return this.app.db.execute(sql, payload)

But this works (using .query())...

const sql = `INSERT INTO ${this.table} SET ?`

return this.app.db.query(sql, payload)

Am I missing something?

_Note: I'm using the Promise wrapper._

[sidorares]

query() does parameter substitution on the client, and as a consequence we have a little bit more flexibility on what and how can be substituted. Read detail explanation here - https://github.com/mysqljs/sqlstring#escaping-query-values

Prepared statement parameters on the other hand are handled by server and must be of simple type (in fact, at the moment they always serialised as string when sent from client but that is probably going to change and we'll be sending them as binary doubles, strings and dates ( + buffers ).

Would be good to add a section explaining what's allowed to be in prepared statement, there are more restrictions in addition to parameter types

[sidorares]

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html#idm140602214371920

You can always try to prepare statement from command line - if it fails in command line it won't work from node client as well, e.i try doing PREPARE stmt FROM 'INSERT INTO tablename SET ?'; in cli

[dashmug]

I see. Thanks for the clarification @sidorares.

[sidorares]

Feel free to contribute 'query vs execute' section to documentation :) Looks like other people often have questions similar to yours

[dashmug]

I'll try when I have the time.

[bigyonk]

I now understand the difference between these two methods. However are values escaped when using .query() on prepared statements?

[sidorares]

However are values escaped when using .query() on prepared statements?

not sure if I understand correctly. .query() does not do "real" prepared statements, it does client side placeholders substitution.

It does escape values for each key when parameter is an object ( like in "INSERT INTO table SET ?" example ), so it's safe to do .query("INSERT INTO students SET ?", [{ name: "Robert'; DROP TABLE students; --"}])

https://github.com/mysqljs/sqlstring/blob/8f193cae10a2208010102fd50f0b61e869e14dcb/lib/SqlString.js#L54

https://github.com/mysqljs/sqlstring/blob/8f193cae10a2208010102fd50f0b61e869e14dcb/lib/SqlString.js#L180

[bigyonk]

not sure if I understand correctly. .query() does not do "real" prepared statements, it does client side placeholders substitution.

I should have been more explicit, but you've filled in all of the blanks very nicely. Thanks for your help and furthermore, your patience.

[kmvan]

.query() -> TextRow { id: 1 }
.execute() -> BinaryRow { id: 1 }

What's different?