What is SQL Injection
SQL Injection is one of the most dangerous possible attacks we have to deal with as a web application developer, albeit with some very easy fixes. Essentially, SQL Injection involves a vulnerability where a user of the system can cause the system to execute any command against the database. Often, when you start learning about interacting with a SQL server behind your application, you will see examples like this:
Typical .NET SQL Usage
SqlDataAdapter da =
There are several issues here, but essentially this allows an attacker to send any id via the query string and get it back, however this is not necessarily an issue here. C#, being a strongly typed language, will only allow for a numerical id in this case, so while an attacker can use this to get your application to pull any item in the table, it doesn't necessarily allow for arbitrary SQL execution. This is still bad form, even if just for consistency's sake, but if we change that query just a little bit, very bad things are possible:
Typical .NET SQL Usage with Vulnerability
SqlDataAdapter da =
Without the static type check here, an attacker can now pass "Page.aspx?id=1; drop table Table" (or any other nefarious command) and execute anything he wants directly against the database. That's pretty bad. It gets worse.
Many database engines have some specific features that allow them to read and write to the local file system, as well as execute things as well. This can be locked down by permission, true, but once a SQL Injection attack is available to an attacker, it is frighteningly easy to use a tool like Metasploit to escalate privileges automatically to get these to these features, turn them on, and use them to take over an entire system. The attack works something like this:
1. Find SQL Injection vulnerability.
2. Use various exploits to get administrative access to database server, to turn on xp_commandshell.
3. Inject binary data into new table, or into existing table fields as an encoded string.
4. Write out binary table data to a local file, say "exploit.exe".
5. Use xp_commandshell to execute this arbitrary command on the system with SQL Server's permissions.
With that, an attacker can execute arbitrary commands on the server itself with an administrator level account, perhaps opening a reverse shell back to their own machine. At that point, they own the entire server, and all because the developer didn't close a very simple security vulnerability.
The fix for this is never execute SQL commands against the database without escaping user input, but with some caveats. If you read that and you think "I'll just use String.Replace() to get rid of single quote characters and semi-colons, then you are missing the point. Yes, you could try escaping all important SQL characters by changing "'" to "\'", etc., but what happens when an attacker sends a single quote character encoded as Japanese? Now your parsing function will miss it, but SQL Server will kindly convert it back to the local configured encoding, and the attacker has completely bypassed your check.
There are two very simple solutions for this that will fix this completely: Stored procedures (database), and prepared statements (code). Which avenue you choose will depend on several factors, but they both function in a similar manner in regards to SQL Injection. Often, you'll find Database Administrators prefer the stored procedure route, while developers prefer the prepared statements route.
Stored procedures essentially amount to defining a SQL command in the database which can be called with parameters to fill in spots in the query. Since they live in the database, they also tend to execute faster when multiple queries are involved. This is also due to the fact that the database compiles a stored procedure, and develops an execution plan to optimize it's execution (Note that if you use a prepared statement as below, this happens as well). Since they live in the database, DBA's tend to prefer them, since they have control over what gets executed. For the same reasons, developers tend to shy away from them because it means they don't have control over the queries directly without getting a DBA involved usually. Essentially, a stored procedure gets defined in the database like so:
Example Stored Procedure Creation Script
And then gets called from code like so:
Example of Stored Procedure Usage in .NET
SqlCommand cmd =
cmd.CommandType = CommandType.StoredProcedure;
rdr = cmd.ExecuteReader();
The passed parameter, location, gets automatically escaped on the SQL end, so this approach is completely invulnerable to SQL Injection. The only other thing to be wary of is if you stored procedure in turn generates SQL that it executes from your parameters. The downside here is that you have to come up with a way to version your stored procedures as well, as they are no longer in your code directly. This barely touches the stuff you can do with stored procedures, and in a way, database programming is as nuanced as regular .NET development. You can actually move your entire business logic layer into the database if you so desire, though I don't like it for the aforementioned versioning concerns.
Prepared Statements / Paramaterized Queries
Prepared statements (or Paramaterized Queries by another name) work the same way as a stored procedure, but from the code side (meaning the database remains a dumb store of data instead of any of the application logic living there). Instead of defining a paramaterized query in the database, we do it in our code. SQL will compile this query and create an optimized execution plan for it just like a stored procedure, so a lot of the speed difference for a single query goes away, and this method allows a developer to keep business logic and data access code in the application code itself.
In fact, the syntax itself should look very similar as well:
Example of .NET Prepared Statements
SqlCommand cmd =
rdr = cmd.ExecuteReader();
This is also not vulnerable to SQL Injection attacks.
A Quick Note on ORMs
Object relational mappers, such as NHibernate and Microsoft's own Entity Framework, abstract some of this concern away. They will implement the prepared statement methodology behind the scenes to protect you automatically for most use cases, except where you might execute your own arbitrary SQL through the ORM, in which case the same rules apply. They are great tools for certain uses, but as with anything you have to know how your ORM is handling things, and how to properly use it, to make sure you're protected there as well.
SQL Injection attacks are a first line threat to not only application security, but system and network security. There are other possible options, like so-called "SQL Firewalls" which sit in between your code and the SQL server itself to filter out possible threats (Which may be good if you're using a commercial product that you don't control the code on), but the best defense is always going to be using one of the two techniques above. The rule here is always use prepared statements or stored procedures, but even more than that, know how to recognize SQL Injection vulnerability, because even with a given tool, if used incorrectly you can still shoot yourself in the foot.