Thanks Martin, All makes sense now. The update query actually writes the same timestamp format into the table as the default query with MySQL
As you suggested I performed a couple of bad logins to check what would happen. The replay attack gets stopped regardless of the query, since it only uses the last_timestep field for that. But as you guessed, the brute force attack didn't trigger. After updating the query to your suggestion I now see that because of the high bad login count my authentication attempts are blocked as it's detected as a brute force attack. So the end result is, either of these 2 AuthSelect queries work along with the UpdateQuery. AuthSelect select secret, active, pin, digits, bad_logins,datediff(s,'1970-01-01 00:00:00',accessed) as accessed, last_timestep from totpkeys where username=%0 AuthSelect select secret, active, pin, digits, bad_logins,datediff(s,'1970-01-01 00:00:00',accessed), last_timestep from totpkeys where username=%0 UpdateQuery update totpkeys set accessed=CURRENT_TIMESTAMP, bad_logins=%0, last_timestep=%2 where username=%1 Thanks for your time :). Kind regards, Stephan Schwarz -----Original Message----- From: Martin Mersberger <[email protected]> Sent: Sunday, November 4, 2018 12:36 AM To: Schwarz, S. (ICT) <[email protected]>; [email protected] Subject: Re: [RADIATOR] AuthBy SQLTOTP Microsoft SQL Server backend On 03.11.18 22:54, Schwarz, S. (ICT) wrote: Hi Stephan, > Thanks for the example. > I actually used the SQL Server Migration Assistant in order to help me set up > the table on the MS SQL. So the columns where already configured with the > correct types. great! > I've managed to get it working with the following queries: > AuthSelect select secret, active, pin, digits, bad_logins,accessed, > last_timestep from totpkeys where username=%0 UpdateQuery update > totpkeys set accessed=CURRENT_TIMESTAMP, bad_logins=%0, > last_timestep=%2 where username=%1 The UpdateQuery should be OK while I have some doubts on the AuthSelect as no 'cast' to Epoch takes place > One thing I don't quite understand in your example, is what is the use of > "datediff(s,'1970-01-01 00:00:00',accessed)"? I believe it converts the > pretty date time format back to unix time, but how is that value actually > used in the AuthSQLTOTP? ref.pdf says: Field 5 (last_time_accessed) is the unix timestamp of the last authentication attempt. It is used to detect brute force attacks. (radiator uses imho internally epoch to check if an AuthRequest is within BadLoginWindow / BadLoginWindow) > I've received another reply, with another query example and I think the end > result should be as following then (since with your example, the column for > accessed has no name, not sure if that were to matter): > AuthSelect select secret, active, pin, digits, > bad_logins,datediff(s,'1970-01-01 00:00:00',accessed) as accessed, > last_timestep from totpkeys where username=%0 ref.pdf does not require name'd result columns (I assume, DBI fetchrow_array() or some variant of it is used in the modules. So only the order of results is important) > I'd just like to understand what the purpose is of the unix time in the > authselect query, when the regular date time format already seems to work. My > mind is much more at ease when I know why something works, instead of seeing > something work but not understanding it :). If it's really working, try to brute force your account with ie (normal requests will work for sure.. this field is required to find anomalies in auth requests): - replay attack (re-use the same token multiple times => it should work only the first time; afterwards radiator should complain about a replay attack) - use wrong tokens/passwords for a couple of times in a short time frame. Radiator should log 'MaxBadLogins' Since ref.pdf is very explicit in requesting a unix time stamp in field 5, I would provide one and datediff is one option to do so. If I remember correctly, the MaxBadLogins dedection does not work, if there is no unix time stamp as result on field 5 (as mentioned ealier - I'm using a SQlite backend and had to adjust the AuthSelect to 'strftime('%%s',accessed)' for field 5 to provide a unix time stamp to Radiator internals and replay / brute force attacks are mitigated as documented ;-) ) regards Martin _______________________________________________ radiator mailing list [email protected] https://lists.open.com.au/mailman/listinfo/radiator
