balderdashy / balderdashy/sails
Waterline serialize value in double quote causing MySQL 5.7 to misinterpret as column
- Dominant language
- JavaScript
- Stars
- 22.8k
- Forks
- 1.9k
- PR merge metrics
- No merged PRs in 30d
Description
**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. (/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. (/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'
```
Contributor guide
Research direction
Start with the reproduction query using sails-mysql and inspect sails-mysql/lib/adapter.js at the __FIND__ entry point shown in the stack trace. Verify the generated WHERE clause against MySQL 5.7; done means a value such as "john.doe" is treated as a string rather than a column and the query succeeds.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, mysql, node.js
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 45/100