Tag Archives: Security

Lightning Talk: SELECT Doesn’t Always Return the Expected Number of Columns

, ,

Last night, I had the privilege of sharing a lightning talk at the OKC SQL Server User Group (OKCSQL). This 17-minute presentation touches on four scenarios where the way columns are returned by SELECT might not match what you expect.

When you write SELECT statements, you probably have expectations like:

  • SELECT * returns all columns in the referenced object(s).
  • Each column is returned in a separate column.
  • Each column returned in visible in the result set.

However—at least in the world of Microsoft SQL Server/Transact SQL (T-SQL)—these assumptions aren’t always true.


Continue reading

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