Tag Archives: Security

Security Concern: Hidden Resultset Columns

,

The resultset grid shows all columns sent by the server, right? Not always! Under certain circumstances, Microsoft® SQL Server® transmits columns that weren’t referenced in the SELECT statement and that don’t show up in tools like SSMS.

How many columns does the below view return? Four seems like the obvious answer but it’s only sometimes correct. This view sometimes returns four columns—and other times returns nine!

CREATE VIEW dbo.EmployeeSummary AS
SELECT
  e.FirstName,
  e.LastName,
  dl1.MonthName + ' ' + dl1.DayString AS Birthday,
  dl2.YearString AS EmployedSince
FROM Employee e
  JOIN UserAccount u ON e.UserName = u.UserName
  JOIN EmploymentHistory h ON e.SSN = h.SSN
  JOIN DateLookup dl1 ON h.HireDate = dl1.Date
  JOIN DateLookup dl2 ON u.Birthdate = dl2.Date;

The extra five columns (shaded in yellow, below) are returned as hidden columns. They won’t appear in most query tools, yet the data they contain still crosses the wire and can be accessed programmatically on the client-side.

Resultset Continue reading