Microsoft SQL Server 2008 R2 Unleashed- P171:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1644 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks LISTING Sample Procedure to Demonstrate SQL Injection use bigpubs2008 go create proc @type varchar 12 null @pubdate varchar 10 null @price varchar 6 null @title varchar 80 null as declare @where varchar 4000 set @where 1 WHERE 1 1 1 if @type is not null set @where @where 1 AND type 111 @type 1111 if @pubdate is not null set @where @where 1 AND pubdate 111 @pubdate 1111 if @price is not null set @where @where 1 AND price 1 @price if @title is not null set @where @where 1 AND title like 111 @title 1111 exec select left title 60 as title convert char 10 pubdate 101 as pubdate from @where return Following is an example of a typical execution of this procedure if the end user enters business in the book type search field exec @type business go title pubdate The Busy Executive s Database Guide Cooking with Computers Surreptitious Balance Sheets You Can Combat Computer Stress Straight Talk About Computers 06 12 2004 06 09 2004 06 30 2004 06 22 2004 To understand how to prevent SQL injection attacks let s look at a way that a hacker might attempt a SQL injection attack. What a hacker attempting a SQL injection attack might typically do with a web application is to seek out a search field and try to inject some SQL code into it to see if the application is susceptible to an attack. This would typically be done by inserting a single quotation mark into the search field to close out the string and appending a SQL statement after it. For example the hacker may enter the string OR 1 1 -- into the search field. This search condition contains a single quotation mark and an end comment marker -- . The quotation mark would have the effect of closing the search string and the comment marker would comment out the rest of the query to prevent syntax errors caused by the SQL injection. The additional search condi- General T-SQL Coding Recommendations 1645 tion appended to .
