"My company has very strict policy on direct access to the database," Steve writes, "no hand-built SQL in the front-end code and always use a stored procedure to access the data. The reasoning behind this was simple and sound: Avoid SQL injection attacks and increase database performance. "

"The execution, however, was not so simple and sound. They went through all the pain of needing to use stored procs, but none of the gain. I'll leave it as an exercise to the reader to see why."

Create Procedure [dbo].[Authenticate] 
  @StartDate datetime, 
  @EndDate datetime, 
  @UserID numeric(18,0),
  @Password char(50),
  @DatabaseName char(50)

  AS

Declare @strSQL char(2000)

Set @strSQL =   
     ''Select count(c.ID) as Count, sum(sc.Total) as Users''
     + '' from Users sc ''
     + '' inner join UserRelationship pr on pr.PartyIDChild = sc.OrganizationID ''
     + '' inner join Batch b on b.BatchID = sc.BatchID ''
     + '' inner join '' + ltrim(rtrim(@DatabaseName)) + ''.dbo.Users c on sc.UserID= c.ID ''
     + '' where b.SentDate between '''''' 
              + ltrim(rtrim(@StartDate)) + '''''''' 
	      + '' and '''''' + ltrim(rtrim(@EndDate)) + ''''''''
     + '' and c.UserID  = '' + ltrim(rtrim(str(@UserID))) 
     + “and c.Password = “ + ltrim(rtrim(str(@Password))) 

Exec (@strSQL)

Steve continues, "the most amazing thing is that when I pointed this out to the senior developer who designed this, he didn't understand what the problem was, and therefore management wouldn't even talk to me about it. Thankfully, I don't work there anymore."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!