knex: Malformed array literal: Knex 2.4.0 regression

Environment

Knex version: 2.4.0 Database + version: postgre 14 OS: windows & unix

Bug

Explain what kind of behaviour you are getting and how you think it should do
Error message

insert into "MetaRequiredFields" ("requiredFields", "tableName") values ($1, $2), ($3, $4) - malformed array literal: "["lineItemText","cost","taxPercentage","priceMarkup","unit"]"
    at Parser.parseErrorMessage (C:\Projects\trackbuild\server\node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (C:\Projects\trackbuild\server\node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (C:\Projects\trackbuild\server\node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (C:\Projects\trackbuild\server\node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 240,
  severity: 'ERROR',
  code: '22P02',
  detail: '"[" must introduce explicitly-specified array dimensions.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: "unnamed portal parameter $1 = '...'",
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'arrayfuncs.c',
  line: '269',
  routine: 'array_in'
}

No testcode included

Hello! due to investion in my own Project I was able to pinpoint issue with Knex in just released 2.4.0 version. One of my table rows uses a text array (_text) as type. Until 2.4.0 I’d inserted js-array as values w/o any problem. However new version introduces the error posted above. I;’ #5321 PR just introduced to be a cause.

About this issue

Original URL
State: closed
Created a year ago
Reactions: 32
Comments: 17 (7 by maintainers)

Commits related to this issue

revert knex to 2.3.0 because of https://github.com/knex/knex/issues/5430 — committed to samc621/SneakerBot by samc621 a year ago
Update Knex to 2.3.0 Not to 2.4.0 because of https://github.com/knex/knex/issues/5430 — committed to FreeFeed/freefeed-server by davidmz a year ago

Most upvoted comments

FYI, this comes from the parameterize function in the client.

From

// json columns can have object in values.
if (isPlainObject(value)) {
  value = JSON.stringify(value);
}

To:

// json columns can have object in values.
if (isPlainObject(value) || Array.isArray(value)) {
  value = JSON.stringify(value);
}

I suppose that the intent of the fix was to allow JSON columns having arrays (as it’s totally legit), but without introspection (or another hint), it’s not possible to distinguish legit array columns from JSON columns with an array value.

+12

salper on Jan 10, 2023

Is there any plan to release a fix for CVE reported in 2.3.0 without the regression introduced in 2.4.0?

florianakos on Jan 11, 2023

Released in 2.4.1

kibertoad on Jan 18, 2023

Just Confirmed that 2.3.0 downgrade fixes the issue

orcwarrior on Jan 6, 2023

thank @caseywebdev. Indeed it’s now much clearer !

seems to me that insert json object (plain and array) tests are missing in integration tests. Maybe I can try to add some.

minh-hoang-trinh on Jan 13, 2023

We prefer to use jsonb[] when we know the top-level value must be an array. It gives us a guarantee that something like {"foo":"bar"} could never be stored in that column. It’s true that jsonb could contain a top-level array, but it could also contain any other value.

Again, knex has serialized this way for so long now, I don’t see another option besides reverting this breaking change.

caseywebdev on Jan 13, 2023

Our app has several instances of storing arrays of objects in json[] columns, which have always worked fine until the recent breaking change. Your proposal would continue to cause our case to be broken. Because this

select '[{"foo":"bar"}]'::json[]

is invalid

SQL Error [22P02]: ERROR: malformed array literal: "[{"foo":"bar"}]"
  Detail: "[" must introduce explicitly-specified array dimensions.
  Position: 8

The correct way to serialize an array with one item of {"foo": "bar"} into a json[] column is with this syntax (which knex has handled correctly forever)

select E'{"{\\"foo\\":\\"bar\\"}"}'::json[]

Hope that clears it up.

caseywebdev on Jan 13, 2023

+1 on this, had to downgrade to 2.3.0.

A workaround for inserting arrays is like so {${array.join(',')}, but I rather not update this all across my codebase for a regression

enchorb on Jan 11, 2023