EF Core + Pomelo fails to query PostLogoutRedirectUris stored as JSON array

[chrisjohnagencybloc]

Confirm you've already contributed to this project or that you sponsor it

• I confirm I'm a sponsor or a contributor

Version

6.1.1

Describe the bug

When using Pomelo with EF Core and OpenIddict, the following query fails to return the expected results when PostLogoutRedirectUris is stored as a JSON array (e.g. ["mytitlehere:\logout"]):

SELECT `o`.`Id`, `o`.`ApplicationType`, `o`.`ClientId`, `o`.`ClientSecret`, ...
FROM `OpenIddictApplications` AS `o`
WHERE `o`.`PostLogoutRedirectUris` LIKE @_uri_0_containsT

This is likely because Pomelo (MySQL provider) doesn’t automatically translate EF Core LINQ queries to use JSON_CONTAINS or equivalent for JSON columns.

Additionally, I originally had the column type for "PostLogoutRedirectUris" set to LONGTEXT, but the query doesn't include wildcards on the front or back (it looks the same as the above query).

In either case, as a result, OpenIddict fails to match the logout URL and throws an error during sign-out.

Likely location in code (but I'm not certain):
OpenIddictEntityFrameworkCoreApplicationStore.cs

Suggested Fix:
Add provider-specific handling (e.g., using EF.Functions.JsonContains() or raw SQL) to ensure proper querying of JSON arrays in MySQL via Pomelo. Or give me the ability to override the query so I can write it correctly for my infrastructure.

To reproduce

Not sure I need to provide a whole repo, but here are the dependencies:

• MySQL 8
• Dotnet 8
• OpenIdDict.AspNetCore 6.1.1
• OpenIddict.EntityFrameworkCore 6.1.1
• Pomelo.EntityFrameworkCore.MySql 8.0.3

Here is my table definition:

CREATE TABLE `OpenIddictApplications` (
    `Id` VARCHAR(450) NOT NULL,
    `ClientId` VARCHAR(100) NOT NULL,
    `ClientSecret` LONGTEXT NULL,
    `ConcurrencyToken` VARCHAR(50) NULL,
    `ConsentType` VARCHAR(50) NULL,
    `DisplayName` VARCHAR(100) NULL,
    `DisplayNames` LONGTEXT NULL,
    `Permissions` LONGTEXT NULL,
    `PostLogoutRedirectUris` LONGTEXT NULL,
    `Properties` LONGTEXT NULL,
    `RedirectUris` LONGTEXT NULL,
    `Requirements` LONGTEXT NULL,
    `Type` VARCHAR(50) NOT NULL,
    `ApplicationType` VARCHAR(50) NULL,
    `JsonWebKeySet` LONGTEXT NULL,
    `Settings` LONGTEXT NULL,
    `ClientType` VARCHAR(50) NULL,
    PRIMARY KEY (`Id`),
    UNIQUE INDEX `IX_OpenIddictApplications_ClientId` (`ClientId`)
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've also tried "PostLogoutRedirecUris" as a JSON column type.

Exceptions (if any)

The exception I get is that the PostLogoutURL cannot be found and therefore it errors and doesn't allow the logout.

[kevinchalet]

Hey,

Additionally, I originally had the column type for "PostLogoutRedirectUris" set to LONGTEXT, but the query doesn't include wildcards on the front or back (it looks the same as the above query).

In either case, as a result, OpenIddict fails to match the logout URL and throws an error during sign-out.

Your issue here isn't how the PostLogoutRedirectUris column is represented, it's your use of wildcards.

For security reasons, "wildcard URIs" are deliberately not a supported thing in OAuth 2.0 and OpenID Connect, that both require an exact match (i.e using simple string/byte-to-byte comparison, which is what the stores do):

The OP also MUST NOT perform post-logout redirection if the post_logout_redirect_uri value supplied does not exactly match one of the previously registered post_logout_redirect_uris values.

The OpenIddict EF 6 and EF Core application stores treat the PostLogoutRedirectUris column as a regular string and use a LIKE-based query to "pre-filter" the applications before making a second pass using StringComparer.Ordinal on the parsed JSON array:

openiddict-core/src/OpenIddict.EntityFrameworkCore/Stores/OpenIddictEntityFrameworkCoreApplicationStore.cs

Lines 322 to 355 in 99fca3f

	public virtual IAsyncEnumerable<TApplication> FindByPostLogoutRedirectUriAsync(
	[StringSyntax(StringSyntaxAttribute.Uri)] string uri, CancellationToken cancellationToken)
	{
	if (string.IsNullOrEmpty(uri))
	{
	throw new ArgumentException(SR.GetResourceString(SR.ID0143), nameof(uri));
	}
	// To optimize the efficiency of the query a bit, only applications whose stringified
	// PostLogoutRedirectUris contains the specified URI are returned. Once the applications
	// are retrieved, a second pass is made to ensure only valid elements are returned.
	// Implementers that use this method in a hot path may want to override this method
	// to use SQL Server 2016 functions like JSON_VALUE to make the query more efficient.
	return ExecuteAsync(cancellationToken);
	async IAsyncEnumerable<TApplication> ExecuteAsync([EnumeratorCancellation] CancellationToken cancellationToken)
	{
	var context = await Context.GetDbContextAsync(cancellationToken);
	var applications = (from application in context.Set<TApplication>().AsTracking()
	where application.PostLogoutRedirectUris!.Contains(uri)
	select application).AsAsyncEnumerable(cancellationToken);
	await foreach (var application in applications)
	{
	var uris = await GetPostLogoutRedirectUrisAsync(application, cancellationToken);
	if (uris.Contains(uri, StringComparer.Ordinal))
	{
	yield return application;
	}
	}
	}
	}

As hinted by the comment, it's not exactly a perfect solution, but it was pretty much the best option when the model was created (EF Core didn't support JSON columns and support in database software was extremely lacking at the time). Potential future improvements are tracked by #1917.

Since wildcard URIs are a very common source of security issues, I strongly encourage you to avoid wildcards in both redirect_uris and post_logout_redirect_uris.

Or give me the ability to override the query so I can write it correctly for my infrastructure.

It's already possible: like any other store method, the OpenIddictEntityFrameworkCoreApplicationStore.FindByPostLogoutRedirectUriAsync() method is virtual and can be overridden if you need to customize the query.

Note: if your clients support the most recent versions of the https://openid.net/specs/openid-connect-rpinitiated-1_0.html specification, consider configuring them to send the optional client_id parameter: it wasn't a thing in the original specification but it was added in subsequent versions.

If you do that, the IOpenIddictApplicationStore.FindByPostLogoutRedirectUriAsync() method isn't used and the URI is validated using the more efficient IOpenIddictApplicationManager.FindByClientIdAsync() + IOpenIddictApplicationManager.ValidatePostLogoutRedirectUriAsync() combo (just like how redirect_uris are validated during an authorization dance).

Hope it'll help.

[chrisjohnagencybloc]

Thanks for your reply. The client is using React Native and so I'll check to see if they can send along the clientId.

I do have a question though about something you said.

The OpenIddict EF 6 and EF Core application stores treat the PostLogoutRedirectUris column as a regular string and use a LIKE-based query to "pre-filter" the applications before making a second pass using StringComparer.Ordinal on the parsed JSON array:

If the query is using just a LIKE without any wildcards and the column has extra characters in it (because it is being stored as a json array), how would the query ever return a result?

Here is what is being stored in the DB:
["amsplusphone://"]

Here is the query (my approximation, I know EF Core doesn't SELECT *):
SELECT * FROM openiddictapplicationsWHEREPostLogoutRedirectUris LIKE 'amsplusphone://'

That returns zero results because of no wildcards. I can understand that you cannot use them in order to be more secure, but I'm curious how that query would ever return a result? Does SQL Server treat that differently than MySQL?

Also, if you could provide example code of how to override the method that would be ideal. I tried a few things in the past few days and didn't have any luck.

[kevinchalet]

Thanks for your reply. The client is using React Native and so I'll check to see if they can send along the clientId.

👍🏻

Does SQL Server treat that differently than MySQL?

It's possible, I'll have to test that.

For reference, here's the parameterized query generated with SQL Server:

SELECT [o].[Id], [o].[ApplicationType], [o].[ClientId], [o].[ClientSecret], [o].[ClientType], [o].[ConcurrencyToken], [o].[ConsentType], [o].[DisplayName], [o].[DisplayNames], [o].[JsonWebKeySet], [o].[Permissions], [o].[PostLogoutRedirectUris], [o].[Properties], [o].[RedirectUris], [o].[Requirements], [o].[Settings]
FROM [OpenIddictApplications] AS [o]
WHERE [o].[PostLogoutRedirectUris] LIKE @__uri_0_contains ESCAPE N'\'

And with SQLite:

SELECT "o"."Id", "o"."ApplicationType", "o"."ClientId", "o"."ClientSecret", "o"."ClientType", "o"."ConcurrencyToken", "o"."ConsentType", "o"."DisplayName", "o"."DisplayNames", "o"."JsonWebKeySet", "o"."Permissions", "o"."PostLogoutRedirectUris", "o"."Properties", "o"."RedirectUris", "o"."Requirements", "o"."Settings"
FROM "OpenIddictApplications" AS "o"
WHERE instr("o"."PostLogoutRedirectUris", @__uri_0) > 0

I just tested and in both cases, the expected result is correctly returned for amsplusphone://.

Also, if you could provide example code of how to override the method that would be ideal. I tried a few things in the past few days and didn't have any luck.

Sure, here's an example of a custom application store derived from the EF Core one:

// Create a class derived from OpenIddictEntityFrameworkCoreApplicationStore (which uses the default application model):
internal sealed class MyApplicationStore : OpenIddictEntityFrameworkCoreApplicationStore
{
    public MyApplicationStore(
        IMemoryCache cache,
        IOpenIddictEntityFrameworkCoreContext context,
        IOptionsMonitor<OpenIddictEntityFrameworkCoreOptions> options)
        : base(cache, context, options)
    {
    }

    public override async IAsyncEnumerable<OpenIddictEntityFrameworkCoreApplication> FindByPostLogoutRedirectUriAsync(
        [StringSyntax("Uri")] string uri, [EnumeratorCancellation] CancellationToken cancellationToken)
    {
        // Your custom implementation here.
    }
}

services.AddOpenIddict()
    .AddCore(options =>
    {
        options.UseEntityFrameworkCore()
               .UseDbContext<ApplicationDbContext>();

        // Register your custom store here:
        options.ReplaceApplicationStore<OpenIddictEntityFrameworkCoreApplication, MyApplicationStore>();
    });

Note: this snippet is for OpenIddict 7.0: the 6.x branch has reached EoL and significant changes have been introduced in 7.0 to make OpenIddict trimming/NativeAOT-compatible, which required introducing changes to the stores and core APIs.

Cheers.

[chrisjohnagencybloc]

Appreciate everything! Yeah, I will put it on my plate to get things upgraded to version 7 shortly. And we must have drawn the short straw with MySQL because that query does not return the expected results. Thanks again!!

[kevinchalet]

Yeah, I will put it on my plate to get things upgraded to version 7 shortly.

Note: you'll likely hit this regression if you use string primary keys (the default type): #2366. If so, you can reduce the max length of the PK columns yourself to fix that:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // Note: this will no longer be required in OpenIddict 8.0+:
    builder.Entity<OpenIddictEntityFrameworkCoreApplication>()
        .Property(static application => application.Id)
        .HasMaxLength(100);

    builder.Entity<OpenIddictEntityFrameworkCoreAuthorization>()
        .Property(static authorization => authorization.Id)
        .HasMaxLength(100);

    builder.Entity<OpenIddictEntityFrameworkCoreScope>()
        .Property(static scope => scope.Id)
        .HasMaxLength(100);

    builder.Entity<OpenIddictEntityFrameworkCoreToken>()
        .Property(static token => token.Id)
        .HasMaxLength(100);
}

And we must have drawn the short straw with MySQL because that query does not return the expected results.

Interestingly, I just tested and I can't reproduce it (i.e the expected client application is correctly returned).
The Pomelo provider generates the following query:

SELECT `o`.`Id`, `o`.`ApplicationType`, `o`.`ClientId`, `o`.`ClientSecret`, `o`.`ClientType`, `o`.`ConcurrencyToken`, `o`.`ConsentType`, `o`.`DisplayName`, `o`.`DisplayNames`, `o`.`JsonWebKeySet`, `o`.`Permissions`, `o`.`PostLogoutRedirectUris`, `o`.`Properties`, `o`.`RedirectUris`, `o`.`Requirements`, `o`.`Settings`
FROM `OpenIddictApplications` AS `o`
WHERE `o`.`PostLogoutRedirectUris` LIKE @__uri_0_contains

If I turn on sensitive data logging, I can see the correct parameter being sent to MySQL (i.e @__uri_0_contains='%amsplusphone://%):

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (23ms) [Parameters=[@__uri_0_contains='%amsplusphone://%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT `o`.`Id`, `o`.`ApplicationType`, `o`.`ClientId`, `o`.`ClientSecret`, `o`.`ClientType`, `o`.`ConcurrencyToken`, `o`.`ConsentType`, `o`.`DisplayName`, `o`.`DisplayNames`, `o`.`JsonWebKeySet`, `o`.`Permissions`, `o`.`PostLogoutRedirectUris`, `o`.`Properties`, `o`.`RedirectUris`, `o`.`Requirements`, `o`.`Settings`
FROM `OpenIddictApplications` AS `o`
WHERE `o`.`PostLogoutRedirectUris` LIKE @__uri_0_contains

Are you 100% sure you were sending the correct parameter in your handmade query? Notably, the URI must be surrounded by two % as it can be anywhere in the string, not just at the start or end.

I'll also try with the Oracle provider to see if it generates the same query.

[kevinchalet]

I'll also try with the Oracle provider to see if it generates the same query.

The official provider generates a very different query, using both LIKE and LOCATE (tho' it still returns the correct result):

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (6ms) [Parameters=[@__uri_0='amsplusphone://' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT `o`.`Id`, `o`.`ApplicationType`, `o`.`ClientId`, `o`.`ClientSecret`, `o`.`ClientType`, `o`.`ConcurrencyToken`, `o`.`ConsentType`, `o`.`DisplayName`, `o`.`DisplayNames`, `o`.`JsonWebKeySet`, `o`.`Permissions`, `o`.`PostLogoutRedirectUris`, `o`.`Properties`, `o`.`RedirectUris`, `o`.`Requirements`, `o`.`Settings`
FROM `OpenIddictApplications` AS `o`
WHERE (@__uri_0 LIKE '') OR (LOCATE(@__uri_0, `o`.`PostLogoutRedirectUris`) > 0)

It's still unclear why it works for me but not for you. Do you use any custom mod/setting for your MySQL server?

[chrisjohnagencybloc]

Now I’m just more confused—mostly at myself. I could have sworn this wasn’t happening a few days ago, but it does look like the EF Core query is correct.

Just to clarify: I’ve never overridden the OpenIddict functionality for logout. Sorry if that caused any confusion earlier. As part of troubleshooting, I did attempt to override the query, but I was never able to get it to compile successfully.

Here’s the query being executed (with detailed logging enabled):

Executed DbCommand ("4"ms) [Parameters=["@__uri_0_contains='%amsplusphone://logout%' (Size = 4000)"], CommandType='Text', CommandTimeout='300']"
""SELECT `o`.`Id`, `o`.`ApplicationType`, `o`.`ClientId`, `o`.`ClientSecret`, `o`.`ClientType`, `o`.`ConcurrencyToken`, `o`.`ConsentType`, `o`.`DisplayName`, `o`.`DisplayNames`, `o`.`JsonWebKeySet`, `o`.`Permissions`, `o`.`PostLogoutRedirectUris`, `o`.`Properties`, `o`.`RedirectUris`, `o`.`Requirements`, `o`.`Settings`
FROM `OpenIddictApplications` AS `o`
WHERE `o`.`PostLogoutRedirectUris` LIKE @__uri_0_contains

This query is returning the correct client entry, which includes the expected post-logout redirect URI. Despite that, I’m still getting the following error immediately afterward:

The end session request was rejected because the specified post_logout_redirect_uri was invalid: "amsplusphone://logout".

Error response:
{ "error": "invalid_request", "error_description": "The specified 'post_logout_redirect_uri' is invalid.", "error_uri": "https://documentation.openiddict.com/errors/ID2052" }

And here’s the request payload I see logged right before the error occurs:

{
  "login_hint": "abc",
  "state": "Kh-AD6jdtstsQaaRMMplxxwiCirGovckv8VVe-fupXU",
  "prompt": "login",
  "id_token_hint": "[redacted]",
  "post_logout_redirect_uri": "amsplusphone://logout",
  "max_age": "0"
}

Is there anything else OpenIddict is validating behind the scenes that could be triggering this error? I don’t see anything obvious in the logs that would explain why it’s failing.

[kevinchalet]

Is there anything else OpenIddict is validating behind the scenes that could be triggering this error?

Has your client application been granted the end session endpoint permission? When OpenIddict falls back to the "is there any client app allowed to use the end session endpoint with that URI attached" logic (i.e because the client_id is not present), this generic error can be returned instead of the more specific "this client application is not allowed to use the end session endpoint" error you'd get with the client_id present.

And here’s the request payload I see logged right before the error occurs:

Hum, that request looks super weird: login_hint (!= logout_hint), prompt and max_age are not standard end session parameters: https://openid.net/specs/openid-connect-rpinitiated-1_0.html#RPLogout.

Are they attached/sent by the client library the React app is using or do you manually attach them?

[chrisjohnagencybloc]

We did run into the client_id not being present as the library wasn't sending it by default so I bet that was it (we have since added it). I just assumed it was the SQL error because clearly I didn't look at the logs close enough (my apologies for sending you down the wrong road there). We did, after we added the client_id, get the "not allowed to use the end session endpoint", which I also fixed.

I think/hope we are on the home stretch here. I just put a fix in to respond with the state query string variable on my custom logout url, which should hopefully provide the last fix needed. I'll let you know how it all turns out. Thanks.