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:

feathers-authentication-management/src/methods/verify-signup.ts

Lines 87 to 93 in 1990797

	const users = await usersService.find(
	Object.assign(
	{},
	params,
	{ query: Object.assign({}, identifyUser, { $limit: 2 }), paginate: false }
	)
	);

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:

feathers-authentication-management/src/methods/verify-signup.ts

Lines 120 to 141 in 5c9b78e

	async function eraseVerifyProps (
	user: User,
	isVerified: boolean,
	verifyChanges?: VerifyChanges,
	params?: Params
	): Promise<User> {
	const patchData = Object.assign({}, verifyChanges || {}, {
	isVerified,
	verifyToken: null,
	verifyShortToken: null,
	verifyExpires: null,
	verifyChanges: {}
	});
	const result = await usersService.patch(
	user[usersServiceId],
	patchData,
	Object.assign({}, params)
	);
	return result;
	}
	}

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 #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 #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.