At a recent team stand up meeting I passed along a joke I caught on twitter:

A SQL Injection walks into a bar, starts to quote something but stops, drops a table, then dashes out.

The joke is a good occasion to refresh our memories on SQL injection attacks. Vulnerable SQL code looks like this:

public void GetCustomerByName(string name) {
   string sql = "select * from Customer where Name = '" + name + "' order by Name";
   // code follows to execute this sql.
}

This function is called by a user entering some text into a website textbox. A user might enter Fred into the textbox, resulting in the following SQL runs against the database, and everything is fine:

If a hacker goes to this website and enters '; drop table Customer -- then the following SQL runs against the database, and the hacker has probably broken the system and caused a lot of heartache for a lot of people:

select * from Customer where Name = ''; drop table Customer --' order by Name

This simple technique was used thousands of time in the previous decade to hack into countless big-name websites.

So the joke is funny because it highlights all the necessary parts to construct a SQL injection attack:

• "starts to quote something" You need a single quote to match up with the starting quote of the original SQL statement.
• "drops a table" One of the nastier things a hacker can do, but they could also put in their own select statement to try to get any data out of the database, or they could update their own record to elevate their privileges – tons of stuff they can do.
  • (For example, you could update the product description text on a site like Amazon.com to add some HTML that redirects the user to your own web page where their computer gets infected with a virus that allows the hacker to take control of your computer and use it in denial of service attacks against other websites and extort money from them. This exact thing happens all the time, even today.)
• "then dashes out"  A double dash is how you do comments in SQL, so this “comments out” the rest of the original SQL the developer had in.

The way to avoid adding sql injection vulnerabilities in your code is to not use string concatenation when building SQL statements. Because SQL injection is so dangerous it's usually appropriate to adopt a policy of "never use string concatenation to build a SQL statement."  The following code is probably safe:

public void GetCustomerByName(string name) {
   string sql = "select * from Customer where Name = @name order by Name";
   sqlCommand.AddParameter("@name", name);
   // code follows to execute this sql.
}

ORMs we use, like NHibernate, also block sql injection attacks unless used in odd ways.