Waterline serialize value in double quote causing MySQL 5.7 to misinterpret as column

[rkt2spc]

Sails version: 0.12.14
Node version: 8.11.4
NPM version: 5.6.0
DB adapter name: sails-mysql
DB adapter version: 0.11.5
Operating system: OSX

---

In one of my projects running sailjs & mysql, I wrote a query like this

sails.model.user.findOne({ username: "john.doe" }).exec(...)

Setting LOG_QUERIES=true give me this SQL command

MYSQL.find:  SELECT `konga_users`.`id`, `konga_users`.`username`, `konga_users`.`email`, `konga_users`.`firstName`, `konga_users`.`lastName`, `konga_users`.`admin`, `konga_users`.`node_id`, `konga_users`.`active`, `konga_users`.`activationToken`, `konga_users`.`createdAt`, `konga_users`.`updatedAt`, `konga_users`.`createdUserId`, `konga_users`.`updatedUserId`, `konga_users`.`node` FROM `konga_users` AS `konga_users`  WHERE `konga_users`.`username` = "john.doe"  LIMIT 1

And this error was thrown

Error (E_UNKNOWN) :: Encountered an unexpected error
: ER_BAD_FIELD_ERROR: Unknown column 'john.doe' in 'where clause'
    at Query.Sequence._packetToError (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
    at Protocol._parsePacket (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Parser.js:73:12)
    at Protocol.write (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:96:28)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:597:20)
    --------------------
    at Protocol._enqueue (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:141:48)
    at PoolConnection.query (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:201:25)
    at __FIND__ (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/lib/adapter.js:838:20)
    at afterwards (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/lib/connections/spawn.js:84:5)
    at /Users/john.doe/Works/projects/konga/node_modules/sails-mysql/lib/connections/spawn.js:40:7
    at Ping.onOperationComplete [as _callback] (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/Pool.js:99:5)
    at Ping.Sequence.end (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Ping.Sequence.OkPacket (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
    at Protocol._parsePacket (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Parser.js:73:12)
    at Protocol.write (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/john.doe/Works/projects/konga/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:96:28)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:597:20)

Details:  Error: ER_BAD_FIELD_ERROR: Unknown column 'john.doe' in 'where clause'

Notice this part in the SQL query

`username` = "john.doe"

After some experimenting I found out that MySQL doesn't really like double quote and interpret it wrongly. The fix would be to convert double quote " to single quota '
So the query become

`username` = 'john.doe'

[sailsbot]

Hi @rocketspacer! It looks like you missed a step or two when you created your issue. Please edit your comment (use the pencil icon at the top-right corner of the comment box) and fix the following:

• Verify "I am experiencing a concrete technical issue (aka a bug) with Sails (ideas and feature proposals should follow the guide for proposing features and enhancements (http://bit.ly/sails-feature-guide), which involves making a pull request). If you're not 100% certain whether it's a bug or not, that's okay--you may continue. The worst that can happen is that the issue will be closed and we'll point you in the right direction."
• Verify "I am not asking a question about how to use Sails or about whether or not Sails has a certain feature (please refer to the documentation(http://sailsjs.com), or post on http://stackoverflow.com, our Google Group (http://bit.ly/sails-google-group) or our live chat (https://gitter.im/balderdashy/sails)."
• Verify "I have already searched for related issues, and found none open (if you found a related closed issue, please link to it in your post)."
• Verify "My issue title is concise, on-topic and polite ("jst.js being removed from layout.ejs on lift" is good; "templates dont work" or "why is sails dumb" are not so good)."
• Verify "I have tried all the following (if relevant) and my issue remains:"
• Verify "I can provide steps to reproduce this issue that others can follow."

As soon as those items are rectified, post a new comment (e.g. “Ok, fixed!”) below and we'll take a look. Thanks!

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[rkt2spc]

Ok, fixed!

[sailsbot]

@rocketspacer Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[johnabrams7]

@rocketspacer Thanks for the updates - it appears this is still an ongoing development in the sails-mysql adapter. I found a great StackOverflow post covering a lot of advice on double-quotes usage in MySQL along with various alternatives for identifiers when double quotes aren't supported. Hope this helps and best of luck with the developments!