[strongSwan] attr-sql - case insensitive?

[Sven Anders]

Hello!

I'm using the "attr-sql" plugin to make static user IP assignments.

The database matches the CN in the certificate.
Is it possible to match here case insensitive?

Regards
Sven Anders
--
Sven Anders <***@anduras.de> () UTF-8 Ribbon Campaign
/\ Support plain text e-mail
ANDURAS intranet security AG
Messestrasse 3 - 94036 Passau - Germany
Web: www.anduras.de - Tel: [phone] - Fax: [phone]

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

[Sven Anders]

Hello!

I'm using the "attr-sql" plugin to make static user IP assignments.

WRONG: The database matches the CN in the certificate.

The name in the database matches the name the user configured in
their settings. So this is very error-prone.

Is it possible to match here case insensitive?
Or any other ideas?


Regards
Sven Anders

PS: Sorry for the first wrong posting...
--
Sven Anders <***@anduras.de> () UTF-8 Ribbon Campaign
/\ Support plain text e-mail
ANDURAS intranet security AG
Messestrasse 3 - 94036 Passau - Germany
Web: www.anduras.de - Tel: [phone] - Fax: [phone]

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

[Tobias Brunner]

Hi Sven,

Post by Sven Anders
The name in the database matches the name the user configured in
their settings. So this is very error-prone.
Is it possible to match here case insensitive?
Or any other ideas?

The `data` column of the `identities` table in the default schema uses a
binary type (BLOB or varbinary), same goes for the queries where the
argument is of type DB_BLOB (and not DB_TEXT). So the comparison here
will always be via an exact binary match. I guess that's mostly for
ASN.1 DNs, which couldn't be stored/compared directly as text.

I suppose, if you only use e.g. email addresses as identities (i.e. no
binary DNs) you could probably change the type of that column (e.g. to
TEXT or varchar) and when using MySQL add `collate utf8_unicode_ci` for
a case insensitive comparison. But you'd also need to change the type
in the queries (in the attr-sql plugin and the pool tool, if you use
it). At least I guess that's required, I didn't try what happens if the
argument's type doesn't exactly match that of the column.

Another option is probably to convert the identities to text and store
and compare them as such, but that would also require several code changes.

Regards,
Tobias

[Sven Anders]

Post by Tobias Brunner
Hi Sven,

Post by Sven Anders
The name in the database matches the name the user configured in
their settings. So this is very error-prone.
Is it possible to match here case insensitive?
Or any other ideas?

The `data` column of the `identities` table in the default schema uses a
binary type (BLOB or varbinary), same goes for the queries where the
argument is of type DB_BLOB (and not DB_TEXT). So the comparison here
will always be via an exact binary match. I guess that's mostly for
ASN.1 DNs, which couldn't be stored/compared directly as text.
I suppose, if you only use e.g. email addresses as identities (i.e. no
binary DNs) you could probably change the type of that column (e.g. to
TEXT or varchar) and when using MySQL add `collate utf8_unicode_ci` for
a case insensitive comparison. But you'd also need to change the type
in the queries (in the attr-sql plugin and the pool tool, if you use
it). At least I guess that's required, I didn't try what happens if the
argument's type doesn't exactly match that of the column.
Another option is probably to convert the identities to text and store
and compare them as such, but that would also require several code changes.
Regards,
Tobias

Thank for the answer!

In other words:
I have to change to code to make it work this way.
At least the attr-sql plugin code.

Is this correct?


Regards
Sven Anders
--
Sven Anders <***@anduras.de> () UTF-8 Ribbon Campaign
/\ Support plain text e-mail
ANDURAS intranet security AG
Messestrasse 3 - 94036 Passau - Germany
Web: www.anduras.de - Tel: [phone] - Fax: [phone]

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

[Tobias Brunner]

Hi Sven,

Post by Sven Anders
I have to change to code to make it work this way.
At least the attr-sql plugin code.
Is this correct?

You can try and see what happens if you don't (i.e. just change the DB).
But I guess you have change some code.

Regards,
Tobias

[Sven Anders]

Post by Tobias Brunner
Hi Sven,

Post by Sven Anders
I have to change to code to make it work this way.
At least the attr-sql plugin code.
Is this correct?

You can try and see what happens if you don't (i.e. just change the DB).
But I guess you have change some code.
Regards,
Tobias

Hello.

I'm using sqlite3 here, because I only need this for static IP assignments.

And I tried your idea, but the database code stores the data in a binary format
(for example: X'294130303030... ).

So no chance here, even if I set the data field to "TEXT NOT NULL COLLATE NOCASE".

But thanks for the tips!

Regards
Sven Anders
--
Sven Anders <***@anduras.de> () UTF-8 Ribbon Campaign
/\ Support plain text e-mail
ANDURAS intranet security AG
Messestrasse 3 - 94036 Passau - Germany
Web: www.anduras.de - Tel: [phone] - Fax: [phone]

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin