Feathers trying to populate nonexistent columns "0" and "1"

[madebyjono]

I'm using postgresql v15.1 with a fresh installation of feathers Followed the setup instructions to the T In posting to my authentication 'users' endpoint: http://localhost:3030/auth-management/verify-signup-short

I'm getting:

error: error: update "users" set "0" = $1, "1" = $2, "isVerified" = $3, "verifyToken" = $4, "verifyShortToken" = $5, "verifyExpires" = $6, "verifyChanges" = $7 where "users"."id" in ($8) - column "0" of relation "users" does not exist

When I manually create the columns "0" and "1" in the database, the rows are populated with a "{" and "}" for both of them. Been dealing with it for a whole day and can't seem to figure it out!

My migration for users is

`export async function up(knex: Knex): Promise { await knex.schema.createTable('users', (table) => { table.increments('id'); table.integer('cellphone'); table.string('email').unique(); table.string('password'); table.string('verifyShortToken'); table.bigInteger('verifyExpires'); table.integer('resetAttempts'); table.integer('resetExpires'); table.string('resetShortToken') table.string('resetToken'); table.string('verifyChanges'); table.boolean('isVerified'); table.string('verifyToken'); }) }

export async function down(knex: Knex): Promise { await knex.schema.dropTable('users') }`

Expected behavior

Successful verification of short token

Actual behavior

getting error { "name": "GeneralError", "message": "update \"users\" set \"0\" = $1, \"1\" = $2, \"isVerified\" = $3, \"verifyToken\" = $4, \"verifyShortToken\" = $5, \"verifyExpires\" = $6, \"verifyChanges\" = $7 where \"users\".\"id\" in ($8) - column \"0\" of relation \"users\" does not exist", "code": 500, "className": "general-error" }

NodeJS version: 18.15.0

Operating System: MacOS

[claustres]

Hi, I am not familiar with Knex but it might be useful you detail your app setup and the payload sent to the endpoint, thanks.

[madebyjono]

Hi! Thank you for any input and assistance in advance! Initially I tried it with Knex and then set up another instance with express, but still the same issue.

In services/auth-management/auth-management.service.js

const {
    VerifySignupShortService,
} = require("feathers-authentication-management");

const notifier = require("./notifier");

module.exports = function (app) {
    const options = {
        app,
        notifier: notifier(app),
        resetAttempts: 5,
        reuseResetToken: false,
        identifyUserProps: ['cellphone'],
      };
    app.use("auth-management/verify-signup-short", new VerifySignupShortService(app,options));

};

services/index.ts :

import { user } from './users/users'
const authManagement = require("./auth-management/auth-management.service.js");

import type { Application } from '../declarations'

export const services = (app: Application) => {
  app.configure(user)
  app.configure(authManagement);

}

Payload from postman to http://localhost:3030/auth-management/verify-signup-short

{
        "user": {
            "cellphone": "12345678"
        },  
        "token": "301128"
    }

Response: error: error: update "users" set "0" = $1, "1" = $2, "isVerified" = $3, "verifyToken" = $4, "verifyShortToken" = $5, "verifyExpires" = $6, "verifyChanges" = $7 where "users"."id" in ($8) - column "0" of relation "users" does not exist

If I create the 0 and 1 columns in the database:

{
    "0": "{",
    "1": "}",
    "id": 6,
    "cellphone": 12345678,
    "email": "test@tests.com",
    "resetAttempts": null,
    "isVerified": true
}

[OnnoGabriel]

Thank you for the detailed report, @madebyjono! I never used the short token verification, and I am not familiar with Knex... But as far as I can see, you did everything as documented incl. setting identifyUserProps to the user field that identifies the user (which is required for short token verification).

The user object if fetched by f-a-m in this code: https://github.com/feathersjs-ecosystem/feathers-authentication-management/blob/19907975bdbe11ed315b6c66d93bbe823f2353a2/src/methods/verify-signup.ts#L87-L93 where identifyUser should be { "cellphone": "12345678" } in your example.

Maybe this object is not send correctly to Feathers, so that it was interpreted as a String? Can you verify the SQL statement e.g. by enabling logging in Knex?

[madebyjono]

Thank you, @OnnoGabriel ,

With knex debugging on, I'm getting the error below:

info: Feathers app listening on http://localhost:3030
  knex:query select "users".* from "users" where "cellphone" = $1 limit $2 undefined +0ms
  knex:query select "users"."id" from "users" where "users"."id" = $1 undefined +4ms
  knex:query update "users" set "0" = $1, "1" = $2, "isVerified" = $3, "verifyToken" = $4, "verifyShortToken" = $5, "verifyExpires" = $6, "verifyChanges" = $7 where "users"."id" in ($8) undefined +2ms
error: error: update "users" set "0" = $1, "1" = $2, "isVerified" = $3, "verifyToken" = $4, "verifyShortToken" = $5, "verifyExpires" = $6, "verifyChanges" = $7 where "users"."id" in ($8) - column "0" of relation "users" does not exist
    at Parser.parseErrorMessage (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
error: error: update "users" set "0" = $1, "1" = $2, "isVerified" = $3, "verifyToken" = $4, "verifyShortToken" = $5, "verifyExpires" = $6, "verifyChanges" = $7 where "users"."id" in ($8) - column "0" of relation "users" does not exist
    at Parser.parseErrorMessage (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/jono/dev/test/feathers_headache/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

No idea why its looking for colums "0" and "1"!

[OnnoGabriel]

Agreed, the UPDATE statement is wrong. Is should just be:

update "users" set "isVerified" = $1, "verifyToken" = $2, "verifyShortToken" = $3, "verifyExpires" = $4 "verifyChanges" = $5 where "users"."id" in ($6)

Unfortunalety, I do not have any idea, where the fields "0" and "1" could come from. The SQL is created by the patch request here: https://github.com/feathersjs-ecosystem/feathers-authentication-management/blob/5c9b78ec8657bbd991b140764e3ea5e487877d18/src/methods/verify-signup.ts#L120-L141

The only way to extent the patchData with additional fields would be via the VerifyChanges object, but you are not using this.

Sorry, that I cannot be of more help. I am not using f-a-m with Feathers 5 and Knex yet.

[claustres]

Could it be somewhat related to https://github.com/feathersjs-ecosystem/feathers-authentication-management/issues/197 ?

[crklan]

Hey @madebyjono, did you by any chance manage to figure out a workaround for this bug? I have a similar setup as you described and I'm facing the same issue as well. Would appreciate any solutions. Thanks.

[madebyjono]

Hey @madebyjono, did you by any chance manage to figure out a workaround for this bug? I have a similar setup as you described and I'm facing the same issue as well. Would appreciate any solutions. Thanks.

Hi @crklan, I haven't looked at it since, but in #197 as claustres mentioned, the 'hack' could be a workaround. I'll try it out when I have the time - unless you beat me to it.

[closertotheend]

Hi, added fix for this problem in https://github.com/feathersjs-ecosystem/feathers-authentication-management/pull/216 Internally Object.assign({}, '{}') produces {0: '{', 1: '}'} . In docs it is stated that verifyChanges must be of string[] type, however in sqlite for instance there is no array type for single column.