Sql Server injection

Let's say you are building a web application that sells music. You have a database table called Albums, and a web page that lets the user browse those albums. You might have a page populated with data from a query like this:

SELECT Title, Year, AlbumName, CoverArtURL FROM Albums

Great so far. Now what if you want the user to be able to search? Okay, so you add a search box, and when they submit it, you take the value they submitted, put it in a variable called UserSearchValue, and construct your query with code that looks something like this:

query = "SELECT Title, Year, AlbumName, CoverArtURL FROM Albums"
if (UserSearchValue.Length > 0)
    query += " WHERE Title LIKE '%" + UserSearchValue + "%'"
// ... submit query

So if the user puts in "Greatest Hits", you will get this query:

SELECT Title, Year, AlbumName, CoverArtURL FROM Albums<br />
WHERE Title LIKE '%Greatest Hits%'

Still so far so good.

But what if the user does something unexpected? What if the user enters this search string:
"'; DROP TABLE Albums; --"

Now what is the resulting query that your application is submitting to the database server? It will look like this:

SELECT Title, Year, AlbumName, CoverArtURL FROM Albums<br />
WHERE Title LIKE '%'; DROP TABLE Albums; --%'

The user has managed to add an entire query to your query, telling your server to drop the Albums table, just by finishing off the original query, inserting (or "injecting") extra code, then commenting out everything following. This is quite a disaster! This is what is known as "SQL injection", the technique whereby a user can cause his own code to be executed on your server. I would hope that the dangers of users being able to make your server do whatever they want are pretty obvious. It is a complete breach of system security and integrity. One clever person can destroy everything, through the user interface that you created! This is why you will hear repeated again and again that you must use techniques such as parameterized queries and cleansing of input rather than ever letting user-submitted text directly become a part of your SQL queries.