Introduction
I understand how malicious code can be created. Years ago I, figured out to embed the Michelangelo virus in the old command.com, have command.com open the speaker port and leave a permanent beep on, and even wrote a keyboard sniffer based on BIOS interrupts. This was almost twenty years ago and that’s where I left my curiosity. So, other then helping the occasional friend get rid of bots, worms, and popup cheese whiz, I am by no means an expert at malicious code. In fact, I wish the authors of such code would just get a life.
That said, a small percentage of people do malicious things. Maybe they do them because they are curious or they may be trying to steal from you or your employer. One such form of malice is the SQL injection attack. SQL injection goes something like this: A programmer writes some SQL that is filled in by some kind of user input and the SQL is passed to the database. The malicious user, instead of putting in an expected parameter, puts in a parameter and some home grown SQL tacked on the end.
In this article, you will learn how LINQ to SQL can support fill in the blank queries and block SQL injection, making LINQ a better alternative to passing SQL to the database. So, not only is LINQ to SQL easier than straight ADO.NET/SQL, LINQ to SQL as a by-product of its design foils SQL injections.
Reviewing Malicious SQL Injection
It’s pretty easy in some cases to figure out whether a site is using user input data to build SQL commands. Browse to a website, enter some goofy data like a single quote which will cause an un-terminated string error in SQL, and see if the website blows up. If it does, and you are really lucky and the website is deployed in debug mode, you will probably actually see the ADO.NET code.
Figure 1 shows a very simple website that lets the user look at company names by the customer id. (The Northwind database was used because most of you will have access to it.) However, if a malicious user enters a single quote, the site crashes and in debug mode gives the malicious user enough information to know his code is being passed to SQL Server.
Figure 1: Enter a valid customer id and get the company name back.
Figure 2: The source lines and the unclosed quotation error tell me that this site is passing user input to SQL Server without a lot of error checking.
Now, this person can craft some SQL instead of a customer id and start fishing. A great place to fish is the master table. First, Listing 1 contains the code for this sample website.