Monday, March 17, 2014

MS SQL Server DATETIME type

This post is from sometime in 2012:

Imagine the following two statements run in SQL Server 2005+:
INSERT INTO tbl (mydate) VALUES ('2000-01-01 00:00:00.001');
UPDATE tbl SET mydate = null WHERE mydate = '2000-01-01 00:00:00.001';

It looks so simple, but the UPDATE statement will update 0 (ZERO) rows.

You might ask why. And the reason is the strange persicion for DATETIME column of SQL Server

http://msdn.microsoft.com/en-us/library/ms187819(v=sql.90)
...

So why does SQL Server round the value in first statement, but fails to do so in the second ?

Workarounds:

  • If you are lucky enough to support only SQL Server 2008+, you might switch to DATETIME2
  • Don't use DATETIME at WHERE clauses
  • Round the value at application stage

Sunday, May 27, 2012

Clever lockout logic on wrong password

A lot of systems are using the logic of locking user account if the user has entered wrong password X times. Each time a wrong password is entered the failed login attempts count increased and when that number reaches X the account is locked. If the user enters the valid password the counter is reset to zero.

But most of the time when the genuine user enters wrong password it's because they simply forget they had changed the password and still trying to use an old one.

Better approach to the lockout logic would be to not increase the wrong login attempts counter if the password was used in the past. There's no drawback in security, because all the information that a malicious person can get is that the password was used in the past, but if it's a wrong assumption the account will be locked.