2.x.x regression: custom SQL queries

[Stefan Winter]

Hi,

I think I found a regression in the 2.x.x branch. I used to use 2.2.0 until I needed the SSHA1 patch from branch; but with branch code, something totally unrelated now stopped working.

I have a custom SQL query in post-auth, it calls an SQL module instance "sql-webmailsso-users" and does an INSERT. Here is what works with 2.2.0 code:

2.2.0:

expand: INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', tim
eout=TIMESTAMPADD(hour, 12, NOW()) -> INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('swinter','620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06', timeout=TIMESTAMPADD(hour, 12, NOW())
rlm_sql (sql-webmailsso-users): Reserving sql socket id: 1
rlm_sql (sql-webmailsso-users): Released sql socket id: 1
        expand: %{sql-webmailsso-users:INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', timeout=TIMESTAMPADD(hour, 12, NOW())} -> 1
+++[expand] returns noop
        expand: urn:restena:magicmushroom=%{control:RESTENA-Rand-Source} -> urn:restena:magicmushroom=620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06620413e46406a7c05e84253cd75fcb06

I.e. expansion gets done, and the SQL query gets executed. Note that there is a line "+++[expand] returns noop" in the middle.

With 2.x.x branch code, the identical config does not actually perfom the SQL query; even though things still get expanded as they should. Ominously, that "noop" line is NOT showing up in the two 2.x.x branch snapshots I tested with. Here are two examples:

20130610:

        expand: INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', tim
eout=TIMESTAMPADD(hour, 12, NOW()) -> INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('swinter','8570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c5131',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='8570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c5131', timeout=TIMESTAMPADD(hour, 12, NOW())
rlm_sql (sql-webmailsso-users): Reserving sql socket id: 6
rlm_sql (sql-webmailsso-users): Released sql socket id: 6
        expand: %{sql-webmailsso-users:INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', timeout=TIMESTAMPADD(hour, 12, NOW())} -> 1
        expand: urn:restena:magicmushroom=%{control:RESTENA-Rand-Source} -> urn:restena:magicmushroom=8570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c51318570ceee00c6d8ed3f1c56e2d47c5131

20310704:

        expand: INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', tim
eout=TIMESTAMPADD(hour, 12, NOW()) -> INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('swinter','af571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6d',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='af571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6d', timeout=TIMESTAMPADD(hour, 12, NOW())
rlm_sql (sql-webmailsso-users): Reserving sql socket id: 8
rlm_sql (sql-webmailsso-users): Released sql socket id: 8
        expand: %{sql-webmailsso-users:INSERT INTO check_webmailsso_users (user,token,timeout) VALUES('%{User-Name}','%{control:RESTENA-Rand-Source}',TIMESTAMPADD(hour, 12, NOW())) ON DUPLICATE KEY UPDATE token='%{control:RESTENA-Rand-Source}', timeout=TIMESTAMPADD(hour, 12, NOW())} -> 1
        expand: urn:restena:magicmushroom=%{control:RESTENA-Rand-Source} -> urn:restena:magicmushroom=af571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6daf571d6c1794a2e491091ac6990d5d6d

Everything looks "pretty much" okay - but my database never gets the INSERT. The only difference I really see in the log is that the "noop" line doesn't show up.

Hope that explains the problem, and helps in finding the problem.

Stefan

-- 
Stefan WINTER
Ingenieur de Recherche
Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche
6, rue Richard Coudenhove-Kalergi
L-1359 Luxembourg

Tel: +352 424409 1
Fax: +352 422473

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 263 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20130704/4827a0d2/attachment.pgp>