CTICoder

A random spillage of programming (and other) thoughts

Archive for December, 2007

Danger when using UNION in SQL

Posted by Michael Bray on December 19, 2007

Interesting thing I learned about UNION today – it removes duplicated result set rows.  At first glance, it may not be a big deal – it is clearly documented in the Transact SQL SELECT statement docs, and after all, this matches the concept of UNION in set theory.  However, what I didn’t expect was that not only does it remove the rows that are duplicates BETWEEN the two queries, but it will also remove duplicated rows WITHIN each of the individual queries.

I found this when trying to calculate the sum of a single column, where the data was actually stored in two different tables.  So the query I was trying to execute was:

SELECT SUM(Duration) FROM (SELECT Duration FROM tblA [WHERE…] UNION SELECT Duration FROM tblB [WHERE…]) tmp

The individual result sets were:

tblA.Duration tblB.Duration
8 0
8 2
6 2
6 2
8 0

And the result of the SELECT SUM(Duration) was 16.  Huh??  It’s 16 because the Duration values are selected, then UNIONed, then the duplicates removed, resulting in a list of ‘8, 6, 2, 0’ to be summed.  I would have expected that the UNION find the duplicated rows between the two sets, and then doing the UNION, resulting in something more like ‘8, 0; 8, 2; 6, 2’ to be the rows that are kept after the removal of the duplcates, followed by the sum resulting in 26. 

Of course, this isn’t the number I want either, and the answer is to include the ‘ALL’ keyword along with UNION (“UNION ALL”) which disables the removal of duplicates.  Then the sum works perfectly, giving an answer of 42.  (Of course!!!  :))

Advertisements

Posted in SQL | Leave a Comment »

Authenticating users against Active Directory

Posted by Michael Bray on December 13, 2007

string userName = loginName + "@ctiusa.com"; 
DirectoryEntry de = new DirectoryEntry("LDAP://ldapRootPath", userName, password); 
de.AuthenticationType = AuthenticationTypes.Secure; 
System.DirectoryServices.PropertyCollection props = de.Properties; 
try 
{ 
    int n = props.Count; 
    return StandardServiceResponse.SuccessResponse;   // Success 
} 
catch (Exception ex) 
{ 
    System.Diagnostics.Debug.WriteLine(ex.Message); 
    return StandardServiceResponse.InvalidCredentials;   // Error 
}

Posted in Snippets | Leave a Comment »

Why do Email spam filters remove zip files??

Posted by Michael Bray on December 13, 2007

It doesn’t increase security in any significant way, it only adds pain to users that are legitimately trying to send stuff around…   It’s basically equivalent to the case of “security thru obscurity”,  which is only marginally better than no security at all.  I see it like this:

  1. An EXE is sent – even the highly-dumb users aren’t likely to run this, although maybe by accident.  Some, of course, are that dumb.  No one really needs to send EXE files around anyway.  I grudgingly accept that it makes sense to filter these, although the argument holds for EXE files just as well as for ZIP files.
  2. A renamed EXE is sent – spam filter won’t block it, but most people with any sense probably won’t rename it and run it unless they know where it came from and what it is.  The really dumb users might do it anyway.
  3. A renamed ZIP is sent – spam filter won’t block it, but there is a good chance people will rename it and look into it and run something inside of it
  4. A zip file is sent – spam filter blocks it…   this is only marginally more secure than #3, and only because the receiver has to go to the trouble of renaming it

The point is that it is so easy for a user to rename the file back to it’s original extension and then do whatever dumb things they want with it.  It’s a case of marginal gain for significant cost (in this case, the pain of two people having to rename the file…   perhaps that doesn’t seem significant to some people, but for me, often, I’d rather not have to take the extra 5 seconds to do it, not to mention the round trip time to cover the first email that I send with an un-renamed zip file only to hear back that the user didn’t receive it.).

[OK, so this isn’t really programming, but I needed a test post and this works just fine.]

Posted in Uncategorized | Tagged: | Leave a Comment »