In the previous post here
I did the following three things:
- Described what a SQL Injection Attack is.
- Provided a simple example of a SQL Injection-Susceptible Web Site built using ASP.NET, C#, ADO.NET, SQL Server, and the AdventureWorks sample database.
- Provided some sample scripts to "Hack" or perform SQL Injection Attacks on the Web Site that we built.
From the results of the previous post you can see that if you are part of a company with a web site on the public Internet and that web site is susceptible to SQL Injection attacks, you and your company and your customers could be in a world of some serious hurt.
I had some fun putting together the previous example. It was kinda cool to see what all I could do with the database and database server from just a simple website with a textbox, button, and grid. I can sorta understand the rush that a hacker might get from the process of figuring out about a database and database server from afar - Ooh! Look at that! Look what I can do! But this is serious business. In today's world, data stored in databases and the movement of that data across the Internet is the world's lifeblood. I don't carry cash any more. I carry a debit card. All of my money is a mishmash of ones and zeros stored in some database on some server at some bank. I go online to check my balance. I swipe my card at the grocery store when I buy a gallon of milk. Is it all a house of cards? Is there a hacker out there playing the role of Tyler Durden about to bring the system all tumbling down? What can I as a software developer, a database developer, a Microsoft SQL Server Database Developer do to help protect this system and help build solid, robust, and secure data recording, reporting, and workflow applications and the associated databases such that my employer succeeds? What can I do to help raise my own level of professionalism and work quality, along with possibly helping other database professionals along the way?
Was the previous paragraph too Jerry Maguire-y?
To help fulfill my ambition of building better software and helping stop problems like SQL Injection, I am going to share what I know. I am sure that I have built SQL Injection-Susceptible Web Sites in the past - I won't in the future. My hope is that someone doesn't make the same mistakes that I did. My hope is that it might help someone at some point. I hope other SQL Server professionals and other developers jump in and provide their ideas too!
In this post I am going to provide concrete ways of stopping SQL Injection Attacks. But first I want to clarify things. My focus in on Microsoft technologies so I used Microsoft technologies for my example SQL Injection-Susceptible Web Site. SQL Injections are NOT just a problem for the Microsoft world. All Server-Side Web Development programming languages such as PHP, Perl, Ruby, and C# are susceptible to SQL Injections attacks. All of the various data access APIs such as the PHP:MySQL and ADO.NET libraries are susceptible. All SQL-based databases engines including MySQL, Oracle, and SQL Server are susceptible.
Hackers perform SQL Injection Attacks. They are ultimately responsible for the problems, security violations, unauthorized accesses, loss of data, damaged servers, and loss of creditibility and trust of all business software developers, database professionals, server administrators, and networking gurus. That being said, I believe that on the software developer, database, server, and networking side, the source of the SQL Injection problem is Application Developers. The DBA can mitigate the problem by restricting the App Devs to using only stored procedures which most App Devs will fight tooth and nail, plus the DBA can be ultra-restrictive with the login used for database access which App Devs will fight too - "just make the login dbo (database owner) for simplicity's sake." But ultimately, DBAs cannot do anything practical to stop SQL Injection Attacks (other than shutdown the database server). Ultimately, App Devs are responsible for building a SQL Injection-Susceptible Web Site. If SQL is inline or embedded with their code and string concatenation is used to build SQL statements (INSERTs, UPDATEs, DELETEs, and SELECTs) then you have the strong potential for having a Web Site that is SQL Injection-Susceptible.
Avoid SQL creation statements similar to the following. Avoid SQL built with string concatenation.
cmd.CommandText = "SELECT ProductID, ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE '%" + this.TextBox1.Text + "%'";
Rather than using string concatenation to build your SQL statements, use Parameterized Queries. Parameterized Queries can provide strong type checking (including size) and input isolation (where multiple commands chained together with the command separator (semicolon) are not possible). To see this add another TextBox and Button to the Default.aspx page of the previous ASP.NET/C# Web Application
and add the following code to the Click event of Button2.
protected void Button2_Click(object sender, EventArgs e)
string strConnection = "Data Source=SERVER;Initial Catalog=AdventureWorks;User Id=sa;Password=sa;";
using (SqlConnection cn = new SqlConnection(strConnection))
using (SqlCommand cmd = new SqlCommand())
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ProductID, ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE '%' + @search + '%';";
using (SqlDataReader dr = cmd.ExecuteReader())
this.GridView1.DataSource = dr;
The differences between the Button1_Click and the Button2_Click appear to be minor. I stated that you needed to avoid string concatenation to stop SQL Injection. That is true. [Clarification From The Idiot-Writer Time!] The LIKE command in SQL is a bit problematic and to get it to work with the % (Percent) Wildcard or Any Characters Wildcard you still have to concatenate to build your SQL string. But the Button2_Click code or the Parameterized Query code uses a named parameter or placeholder (@search) that is populated in the subsequent statement - "cmd.Parameters.AddWithValue("@search", this.TextBox2.Text);". This is the key step to stopping SQL Injection attacks. Rather than saying Avoid String Contenation When Building Your SQL I should say Use Parameterized Queries To Build You SQL.
Go back and attempt to "run" the same hacker scripts in this second TextBox using the second Button. Nothing appears to happen. What is happening is that you are now effectively searching for an item in the product table with a name that matches the big long string that you entered into the TextBox. You have stopped the SQL Injection Attack.
In doing some testing I found something - it is likely others have already pointed it out before me. SQL Profiler and possibly the system views can be used by the DBA to find possible SQL Injection risks. If you run and observe the SELECT queries from Button1_Click and Button2_Click you see that both run as the ApplicationName ".Net SqlClient Data Provider". Button1_Click runs the query as "straight" SQL. Button2_Click runs using the sp_executesql stored procedure. This could possibly be leveraged to find ADO.NET applications that are not using Parameterized Queries and could possibly be SQL Injection-Susceptible. But this is not an absolute test for sure. This idea could possibly be extended to other SQL Server access libraries and other applications. Plus possibly used with other databases such as MySQL and Oracle - I don't know.
The use of Parameterized Queries over what I will call manually built SQL in App Dev code is the key to preventing SQL Injection Attacks.
Following are some additional things beyond the use of Parameterized Queries to help stop SQL Injection Attack (I will avoid the mountains of narrative on these items! <grin> ):
- Server-side data validation of all inputs before touching database. Never trust client-side validation exclusively.
- Use an ORM such as LINQ to SQL, Entity Framework, or nHibernate to cross the application code/SQL code boundary.
- Possibly use Stored Procedures for all database access. No direct SELECTs, INSERTs, UPDATEs, and DELETEs.
- Separate your web application code from the data access layer code by using web services. Ensure inputs are heavily validated on the web services.
- GRANT only what is absolutely required to the web application's login. Never use a dbo (database owner) and definitely not a sysadmin login for an application's login. DENY access to everything not absolutely required. Follow the best practice of least priviledge.
- If the web application is an ASP.NET consider using a specialized logging tool such as ELMAH. Can be added on to existing compiled sites very easily and provide view of all errors including notifications. There are commercial equivalents along with equivalents for other platforms also. You can also roll your own error logging and notification system to help keep an eye on SQL Injection Attacks and other penetration attempts.
There are likely several other methods to stopping or helping mitigate the threat of SQL Injection Attacks. But the key is getting Web Application Developers to use Parameterized Queries if they are going to be making direct SQL calls against the database.
If anyone has any additional ideas please pass them along. Any feedback is appreciated. Thanks!