Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Social Authentication SQL parse error with Knex #68

Closed
KNTH01 opened this issue Sep 3, 2016 · 3 comments
Closed

Social Authentication SQL parse error with Knex #68

KNTH01 opened this issue Sep 3, 2016 · 3 comments

Comments

@KNTH01
Copy link

KNTH01 commented Sep 3, 2016

Hello,

I try to setup Feather Authentication with the Google provider.
I encounter many difficulties as there is no enough documentation with feathers-knex. But for this one I think something goes wrong :

I want to get authenticated by Google by going to this route: /auth/google.

I encounter this error:

knex:client acquired connection from pool: __knexUid2 +0ms
knex:query update `users` set `avatar` = ?, `createdAt` = ?, `email` = ?, `github` = ?, `githubId` = ?, `google` = ?, `googleId` = ?, `password` = ?, `updatedAt` = ?, `username` = ? where `id` = ? +1ms
knex:client releasing connection to pool: __knexUid2 +3ms
feathers:rest Error in REST handler: `update `users` set `avatar` = NULL, `createdAt` = NULL, `email` = NULL, `github` = NULL, `githubId` = NULL, `google` = '{my google data}', `googleId` = '10154235...152695121', `password` = NULL, `updatedAt` = NULL, `username` = NULL where `id` = 1 - ER_PARSE_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 ''[object Object]', '[object Object]', `objectType` = 'person', `id` = '104385465' at line 1` +0ms
express:router <anonymous>  : /auth/google/callback?state=true&code=4/lCleOw9x2Z3uBu4SpsDUCfUVDcpCFl9zO4Ix3SApbZ4 +1ms
feathers-authentication:middleware An authentication error occurred. +2ms { Error: ER_PARSE_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 ''[object Object]', '[object Object]', `objectType` = 'person', `id` = '104385465' at line 1
  at Query.Sequence._packetToError (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/sequences/Sequence.js:51:14)
  at Query.ErrorPacket (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
  at Protocol._parsePacket (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/Protocol.js:280:23)
  at Parser.write (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/Parser.js:74:12)
  at Protocol.write (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/Protocol.js:39:16)
  at Socket.<anonymous> (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/Connection.js:109:28)
  at emitOne (events.js:96:13)
  at Socket.emit (events.js:188:7)
  at readableAddChunk (_stream_readable.js:176:18)
  at Socket.Readable.push (_stream_readable.js:134:10)
  at TCP.onread (net.js:543:20)
  --------------------
  at Protocol._enqueue (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/protocol/Protocol.js:141:48)
  at Connection.query (/home/knguyen/web/reveal-your-heroes/node_modules/mysql/lib/Connection.js:214:25)
  at /home/knguyen/web/reveal-your-heroes/node_modules/knex/lib/dialects/mysql/index.js:124:18
  at Promise._execute (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/debuggability.js:299:9)
  at Promise._resolveFromExecutor (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/promise.js:481:18)
  at new Promise (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/promise.js:77:14)
  at Client._query (/home/knguyen/web/reveal-your-heroes/node_modules/knex/lib/dialects/mysql/index.js:118:12)
  at Client.query (/home/knguyen/web/reveal-your-heroes/node_modules/knex/lib/client.js:187:24)
  at Runner.<anonymous> (/home/knguyen/web/reveal-your-heroes/node_modules/knex/lib/runner.js:129:36)
  at Runner.tryCatcher (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/util.js:16:23)
  at Runner.query (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/method.js:15:34)
  at /home/knguyen/web/reveal-your-heroes/node_modules/knex/lib/runner.js:55:21
  at tryCatcher (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/util.js:16:23)
  at /home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/using.js:185:26
  at tryCatcher (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/util.js:16:23)
  at Promise._settlePromiseFromHandler (/home/knguyen/web/reveal-your-heroes/node_modules/bluebird/js/release/promise.js:510:31)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
index: 0 }

{ my google data } is something like :

{
    "kind": "plus#person",
    "etag": "\\"
    xw0en60W6 - NurXn4VBU - CMjSPEw / eQLrqUnFu635 - 6 o1feH - f1c49Ho\\ "",
    "occupation": "xxx",
    "skills": "xxx",
    "gender": "male",
    "urls": [{
        "value": "http://gplus.to/xxx",
        "type": "otherProfile",
        "label": "Kevin Nguyen"
    }, {
        "value": "http://www.youtube.com/user/xxx",
        "type": "otherProfile",
        "label": "Kevin Nguyen"
    }, {
        "value": "http://twitter.com/xxx",
        "type": "otherProfile",
        "label": "kevinnth"
    }],
    "objectType": "person",
    "id": "111xxx",
    "displayName": "Kevin Nguyen",
    "name": {
        "familyName": "Nguyen",
        "givenName": "Kevin"
    },
    "braggingRights": "xxxx",
    "url": "https://plus.google.com/+xxx",
    "image": {
        "url": "https://xxx",
        "isDefault": false
    },
    "organizations": [{
        "name": "xxx",
        "type": "school",
        "startDate": "xxx",
        "primary": true
    }, {
        "name": "xxx",
        "title": "xxx xxxx",
        "type": "school",
        "startDate": "xxx",
        "endDate": "xxx",
        "primary": false
    }, {
        "name": "xxx.fr",
        "title": "xxx xxxx",
        "type": "work",
        "startDate": "xxx",
        "endDate": "xxx",
        "primary": false
    }],
    "isPlusUser": true,
    "language": "fr",
    "circledByCount": 180,
    "verified": false,
    "cover": {
        "layout": "banner",
        "coverPhoto": {
            "url": "https://xxx",
            "height": 530,
            "width": 940
        },
        "coverInfo": {
            "topImageOffset": 0,
            "leftImageOffset": 0
        }
    },
    "accessToken": "ya29.xxx-0ZGJXYm4_SLv3ZA5xxxTv5xxyQ1ZzQg"
}

So, this request should work fine but does not work:

update `users` set `avatar` = NULL, `createdAt` = NULL, `email` = NULL, `github` = NULL, `githubId` = NULL, `google` = '{ my google data }', `googleId` = '104385465479140482121', `password` = NULL, `updatedAt` = NULL, `username` = NULL where `id` = 1
Error: ER_PARSE_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 ''[object Object]', '[object Object]', `objectType` = 'person', `id` = '104385465' at line 1

I execute this in with mysql and the update action is executed : 1 row affected. (Query took 0.0040 seconds.)

Here's my model (should be documented, isn't it?):

knex.schema.createTable('users', function (table) {
      table.increments('id').primary()
      table.string('username')
      table.string('email').index().unique()
      table.string('password')
      table.string('avatar')
      table.string('googleId') // I had to generate a feathers app with sequelize in order to get its model...
      table.json('google') // when attempt to authenticate, there is an insert into this column
      table.string('githubId')
      table.json('github')
      table.datetime('createdAt')
      table.datetime('updatedAt')
    })

The only thing weird in the { my google data } is the etag property, I don't know what it is...

Thanks for help.

@KNTH01
Copy link
Author

KNTH01 commented Sep 4, 2016

Hello,

I have got an idea while sleeping, then I try it:

'use strict'

// src/services/user/hooks/google-auth.js
//
// Use this hook to manipulate incoming or outgoing data.
// For more information on hooks see: http://docs.feathersjs.com/hooks/readme.html

const defaults = {}

module.exports = function (options) {
  options = Object.assign({}, defaults, options)

  return function (hook) {
    if (hook.data.google) {
      // convert JSON object to string
      hook.data.google = JSON.stringify(hook.data.google)
    }
  }
}

This hook (before) for my user service solves my problem.

I let this issue opened, I think there is action to do here, a new comer to FeathersJS shall not have to debug his app. At least giving the users schema table? Any other suggestion?

Thanks.

@daffl
Copy link
Member

daffl commented Sep 22, 2016

The hook is a good idea. Once we finalize the next version of Feathers authentication we'll make sure to document how it should work with all official database adapters.

@daffl
Copy link
Member

daffl commented Oct 31, 2016

We have some pretty big changes coming in feathersjs-ecosystem/authentication#336 that will allow to customize the response. I am going to close this issue since we are now keeping track of the changes necessary for this in the feathers-authentication repository.

@daffl daffl closed this as completed Oct 31, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants