I heard this last week about an application that was attached by SQL Injection. The application wasn’t very old. I’m quite surprised at this turn of events. How SQL Injection works is well documented all over the web. Ways to exploit SQL databases through injection are simple. Blocking SQL Injection is simple as well. This is a topic I have intentionally avoided, thinking it is no longer relevant. I was clearly wrong. So, today I’m going to provide a high level primer on SQL Injection. Once you know what it is, take time to learn how to make it go away.
SQL injection occurs when you build your SQL statements dynamically by concatenating SQL statements with user input. If you have a text field in a web app, where the user may enter free form text, that is a great place to test injection. Here is how it works.
Even though you don’t HAVE TO end an SQL statement with the semi-colon “;” the syntax allows for it. This may not be true in every SQL engine…but many popular ones don’t require it. Attackers take advantage of this fact. What an attacker can do is type something like the following in a text field:
'; SELECT * FROM INFORMATION_SCHEMA.TABLES; --
If your application does something like the following:
int noteID = 1928;
string sqlStatement = "UPDATE Notes SET NoteText = '" + noteText
+ "' WHERE NoteID = " + noteId.ToString();
Then the final sqlStatement that will be sent to your SQL Engine is:
UPDATE Notes SET NoteText = '';
SELECT * FROM INFORMATION_SCHEMA.TABLES;
-- ' WHERE NoteID = 1928
The UPDATE statement is correct. The new SELECT Statement is correct. Depending on your error handling it may or may not be displayed in the web site, because your application won’t know what to do with the resulting query results.The where clause is simply commented out so it doesn’t matter what it does.
Tricky hackers will figure out your column names, and modify the injection to put the contents into the text field they are manipulating. If they can do that, then they can send results from their query into the update, and see the results in the browser. Sometimes they can do negative hacking by running statements and waiting to see if an error occurs. If the app works, then they know their syntax is valid. If that syntax is attempting to discover information about your schema, the lack of an error confirms their guess without having to see the results.
Here is another problem with the example I provided. Because the where clause is commented out, what records will be updated? Every single row in the Notes table will have the NoteText set to empty string. That kind of thing can be really bad for your database. I hope you have backups. The guys on Channel 9 call that an RGE (Resume Generating Event).
Tomorrow I’ll demonstrate some techniques on how to thwart SQL Injection. Can you believe this is still and issue today? Why not share your experience with SQL Injection in our comments.
Cheers,
Ben