fix: v3.16.4 — JSON command params accept json/jsonb/text (not just j… · sdaves/NpgsqlRest@0c35408 · GitHub
Skip to content

Commit 0c35408

Browse files
vbilopavvbilopav
committed
fix: v3.16.4 — JSON command params accept json/jsonb/text (not just json)
JSON payloads passed to user-authored SQL commands were bound with a hardcoded NpgsqlDbType.Json, so a function declaring the matching parameter as jsonb or text failed with PostgreSQL 42883 "function does not exist" — despite the docs advertising text/json/jsonb. PostgreSQL function-overload resolution uses only implicit casts, and there is no implicit cast from json to jsonb or text. Bind these values as NpgsqlDbType.Unknown instead. PostgreSQL resolves an untyped parameter server-side via the target type's input function, so json, jsonb, and text all work. The value is already a string (or DBNull) at every site, so this is a drop-in, strictly-additive change: existing json-typed functions are unaffected. Sites fixed (21 bindings): - ExternalAuth LoginCommand ($4 provider data, $5 analytics) - CsvUploadHandler / ExcelUploadHandler row-command metadata + json data - Fido2 endpoints (user_context, analytics, claims, challenge body) across login, registration, add-passkey, and challenge-options commands Tests: - JsonParameterBindingContractTests — locks the Npgsql/PG resolution behaviour (json-only for Json binding; json/jsonb/text for Unknown, incl. NULL and round-trip integrity). - CsvUploadMetaParamTypeTests — end-to-end CSV upload with the row-command metadata parameter declared json, jsonb, and text. Full suite: 2100 passing.
1 parent de9545d commit 0c35408

12 files changed

Lines changed: 320 additions & 21 deletions

File tree

‎NpgsqlRest/UploadHandlers/Handlers/CsvUploadHandler.cs‎

Lines changed: 4 additions & 1 deletion

‎NpgsqlRestClient/ExcelUploadHandler.cs‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -110,15 +110,17 @@ public async Task<string> UploadAsync(NpgsqlConnection connection, HttpContext c
110110
{
111111
if (dataAsJson is true)
112112
{
113-
command.Parameters.Add(NpgsqlRestParameter.CreateParamWithType(NpgsqlDbType.Json));
113+
// Unknown (not Json): let the row_command's data parameter be json, jsonb or text.
114+
command.Parameters.Add(NpgsqlRestParameter.CreateParamWithType(NpgsqlDbType.Unknown));
114115
}
115116
else
116117
{
117118
command.Parameters.Add(NpgsqlRestParameter.CreateParamWithType(NpgsqlDbType.Text | NpgsqlDbType.Array));
118119
}
119120
}
120121
if (paramCount >= 3) command.Parameters.Add(new NpgsqlParameter());
121-
if (paramCount >= 4) command.Parameters.Add(NpgsqlRestParameter.CreateParamWithType(NpgsqlDbType.Json));
122+
// Unknown (not Json): let the row_command's metadata parameter be json, jsonb or text.
123+
if (paramCount >= 4) command.Parameters.Add(NpgsqlRestParameter.CreateParamWithType(NpgsqlDbType.Unknown));
122124

123125
// Build user claims JSON once (reused for all rows)
124126
string? userClaimsJson = null;

‎NpgsqlRestClient/ExternalAuth.cs‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -392,7 +392,10 @@ private async Task ProcessAsync(
392392
if (paramCount >= 4) command.Parameters.Add(new NpgsqlParameter()
393393
{
394394
Value = infoNode is not null ? infoContent.ToString() : DBNull.Value,
395-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
395+
// Unknown (not Json): PostgreSQL resolves it server-side via the target type's input
396+
// function, so the LoginCommand's data parameter may be declared json, jsonb OR text
397+
// (text/json/jsonb as documented). A hardcoded Json matches only a json parameter.
398+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
396399
});
397400
if (paramCount >= 5)
398401
{
@@ -409,7 +412,8 @@ private async Task ProcessAsync(
409412
command.Parameters.Add(new NpgsqlParameter()
410413
{
411414
Value = analyticsData.ToJsonString(),
412-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
415+
// Unknown (not Json): allow the analytics parameter to be json, jsonb or text.
416+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
413417
});
414418
}
415419
}

‎NpgsqlRestClient/Fido2/Endpoints/AddPasskeyEndpoint.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
210210
storeCommand.Parameters.Add(new NpgsqlParameter
211211
{
212212
Value = request.UserContext,
213-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
213+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
214214
});
215215
}
216216

@@ -228,15 +228,15 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
228228
storeCommand.Parameters.Add(new NpgsqlParameter
229229
{
230230
Value = analyticsData?.ToJsonString() ?? (object)DBNull.Value,
231-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
231+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
232232
});
233233
}
234234
catch
235235
{
236236
storeCommand.Parameters.Add(new NpgsqlParameter
237237
{
238238
Value = DBNull.Value,
239-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
239+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
240240
});
241241
}
242242
}
@@ -245,7 +245,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
245245
storeCommand.Parameters.Add(new NpgsqlParameter
246246
{
247247
Value = DBNull.Value,
248-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
248+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
249249
});
250250
}
251251
}

‎NpgsqlRestClient/Fido2/Endpoints/AddPasskeyOptionsEndpoint.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -94,15 +94,15 @@ await WriteErrorResponseAsync(context, HttpStatusCode.Unauthorized,
9494
command.Parameters.Add(new NpgsqlParameter
9595
{
9696
Value = claimsParam,
97-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
97+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
9898
});
9999
}
100100
if (paramCount >= 2)
101101
{
102102
command.Parameters.Add(new NpgsqlParameter
103103
{
104104
Value = bodyParam ?? (object)DBNull.Value,
105-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
105+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
106106
});
107107
}
108108

‎NpgsqlRestClient/Fido2/Endpoints/LoginEndpoint.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -229,7 +229,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.Unauthorized,
229229
completeCommand.Parameters.Add(new NpgsqlParameter
230230
{
231231
Value = userContext ?? (object)DBNull.Value,
232-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
232+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
233233
});
234234
}
235235
if (paramCount >= 4)
@@ -246,15 +246,15 @@ await WriteErrorResponseAsync(context, HttpStatusCode.Unauthorized,
246246
completeCommand.Parameters.Add(new NpgsqlParameter
247247
{
248248
Value = analyticsData?.ToJsonString() ?? (object)DBNull.Value,
249-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
249+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
250250
});
251251
}
252252
catch
253253
{
254254
completeCommand.Parameters.Add(new NpgsqlParameter
255255
{
256256
Value = DBNull.Value,
257-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
257+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
258258
});
259259
}
260260
}
@@ -263,7 +263,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.Unauthorized,
263263
completeCommand.Parameters.Add(new NpgsqlParameter
264264
{
265265
Value = DBNull.Value,
266-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
266+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
267267
});
268268
}
269269
}

‎NpgsqlRestClient/Fido2/Endpoints/LoginOptionsEndpoint.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -85,7 +85,7 @@ public async Task InvokeAsync(HttpContext context)
8585
command.Parameters.Add(new NpgsqlParameter
8686
{
8787
Value = bodyJson ?? (object)DBNull.Value,
88-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
88+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
8989
});
9090
}
9191

‎NpgsqlRestClient/Fido2/Endpoints/RegistrationEndpoint.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -202,7 +202,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
202202
storeCommand.Parameters.Add(new NpgsqlParameter
203203
{
204204
Value = request.UserContext,
205-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
205+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
206206
});
207207
}
208208

@@ -220,15 +220,15 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
220220
storeCommand.Parameters.Add(new NpgsqlParameter
221221
{
222222
Value = analyticsData?.ToJsonString() ?? (object)DBNull.Value,
223-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
223+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
224224
});
225225
}
226226
catch
227227
{
228228
storeCommand.Parameters.Add(new NpgsqlParameter
229229
{
230230
Value = DBNull.Value,
231-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
231+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
232232
});
233233
}
234234
}
@@ -237,7 +237,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
237237
storeCommand.Parameters.Add(new NpgsqlParameter
238238
{
239239
Value = DBNull.Value,
240-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
240+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
241241
});
242242
}
243243
}

‎NpgsqlRestClient/Fido2/Endpoints/RegistrationOptionsEndpoint.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -112,7 +112,7 @@ await WriteErrorResponseAsync(context, HttpStatusCode.BadRequest,
112112
command.Parameters.Add(new NpgsqlParameter
113113
{
114114
Value = jsonParam,
115-
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json
115+
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Unknown
116116
});
117117

118118
CommandLogger.LogCommand(command, ctx.Logger, LogRegistrationOptions);

‎NpgsqlRestTests/ParamTypeTests/JsonParameterBindingContractTests.cs‎

Lines changed: 131 additions & 0 deletions

0 commit comments

Comments
 (0)