CodeSOD: Classic WTF: All Pain, No Gain

It's a holiday here in the states. So enjoy this classic from the far off year of 2009. --Remy

"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) ASDeclare @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] Keep the plebs out of prod. Restrict NuGet feed privileges with ProGet. Learn more.