Q. What is SQL injection? A. SQL Injection is simply a term describing the act of passing SQL code into an application that was not intended by the developer. Since this topic is not specifically restricted to SQL Server it is not included in the normal FAQ. In fact, much of the problems that allow SQL injection are not the fault of the database server per-se but rather are due to poor input validation and coding at other code layers. However, due to the serious nature and prevalence of this problem I feel its inclusion in a thorough discussion of SQL Server security is warranted. How do I prevent SQL injection in my applications? A. I used to recommend multiple ways to help prevent SQL injection (isnumeric checks, replacement for single quotes, etc) but over time I have come to the conclusion that there is only one way to do it right - parameterized queries combined with the close scutiny of dynamic queries in stored procedures (EXEC, sp_excutesql). If you are not using parameterized queries for all data access then you are begging for trouble. Even if you use them, you need to be very careful that EXEC or sp_execute statements in your stored procedures don't re-mainifest the SQL injection problem on the back-end. Here is an example of the correct way to perform data access without a stored procedure: SqlDataReader rdr = null; SqlConnection con = null; SqlCommand cmd = null; string ConnectionString = "server=yourserver;Integrated Security=SSPI; database=northwind"; con = new SqlConnection(ConnectionString); con.Open(); // Set up a command with the given query and associate // this with the current connection. string CommandText = "SELECT FirstName, LastName" + " FROM Employees" + " WHERE (LastName LIKE @Find)"; cmd = new SqlCommand(CommandText); cmd.Connection = con; // Add LastName to the above defined paramter @Find cmd.Parameters.Add( new SqlParameter( "@Find", // The name of the parameter to map System.Data.SqlDbType.NVarChar, // SqlDbType values 20, // The width of the parameter "LastName")); // The name of the source column // Fill the parameter with the value retrieved // from the text field cmd.Parameters["@Find"].Value = txtFind.Text; // Execute the query rdr = cmd.ExecuteReader(); Are other database servers (Oracle, MySQL, Sybase) subject to SQL injection? A. Yes, to varying degrees. Here are some sites that can get you more details on SQL Injection as well as information on SQL Injection on other platforms. _http://www.owasp.org _http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf _http://www.nextgenss.com/papers/advanced_sql_injection.pdf Q. How do attackers exploit SQL injection problems? A. SQL injection is usually caused by developers who use "string-building" techniques in order to execute SQL code. For example, in a search page, the developer may use the following code to execute a query (VBScript/ASP sample shown): Set myRecordset = myConnection.execute("SELECT * FROM myTable WHERE someText ='" & request.form("inputdata") & "'") The reason this statement is likely to introduce an SQL injection problem is that the developer has made a classic mistake - poor input validation. We are trusting that user has not entered something malicious - something like the innocent looking single quote ('). Let's consider what would happen if a user entered the following text into the search form: ' exec master..xp_cmdshell 'net user test testpass /ADD' -- Then, when the query string is assembled and sent to SQL Server, the server will process the following code: SELECT * FROM myTable WHERE someText ='' exec master..xp_cmdshell 'net user test testpass /ADD'--' Notice, the first single quote entered by the user closed the string and SQL Server eagerly executes the next SQL statements in the batch including a command to add a new user to the local accounts database. If this application were running as 'sa' and the MSSQLSERVER service is running with sufficient privileges we would now have an account with which to access this machine. Also note the use of the comment operator (--) to force the SQL Server to ignore the trailing quote placed by the developer's code. Single quotes are not the only problem. Consider a search where this input is a number rather than a string. If the user instead places SQL in the input and the developer does not check the input data type then the SQL Server will likely execute it. What is the impact of a SQL injection attack? A. This depends. The injected code runs with whatever SQL Server context the application is configured to use. Unfortunately many applications run with an over-excessive level of privilege such as 'sa' or an account with database-owner privileges. In many cases developers do this in order to avoid having to configure and maintain permissions for their database objects. This is a mistake. Here is a chart of the possible effects of running SQL Server applications with excessive privileges: Application accesses SQL Server using "sa" privileges ----------------------------------------------------------------------- Total control of the SQL Server with an operating system shell at the level of privilege of the MSSQLSERVER service using the xp_cmdshell extended stored procedure. Ability to read, write, and mutilate all data stored on the SQL Server databases. Application accesses SQL Server using "db_owner" privileges ----------------------------------------------------------------------------------- Ability to read/write all data on the affected database. Ability to drop tables, create new objects, and generally take total control of the affected database. Application accesses SQL Server using normal user privileges ---------------------------------------------------------------------------------- (recommended) Ability to natively access all objects in the database to which this account has been given access. At best, this may mean only being able to run some stored procedures. At worst, this means possible read/write access to all tables and views. This it why stored procedures can be very helpful to security. If you only allow users to manipulate data using stored procedures and deny direct access to tables then it severly limits what attackers can do. Q. How do I test if my application is subject to SQL injection attacks? A. A method I recommend is that you briefly (on a test platform) disable error handling so that ODBC errors or SQL Server errors are displayed. Then, you can simply try inputting single quotes into your application to see if you can cause it to fail. A failure is usually indicative of poor validation and corruption of the SQL string. These are good hot-spots for the application. A better method for checking code is to use a tool like FxCop that checks assemblies looking for database access code that does not use parameterized queries. If you have the source code to your application then you should be able to seek out all data access code to verify how it is being done. If you see any area where a string is being concatenated to be sent along to the database then be suspicious and replace the code with a parameterized query. As always, good code review is the best method. Take the time to do it or hire someone to do it. Better to pay now than later when the costs are not known.... (c) sqlsecurity.com ----------------------------- Any questions about SQL injection post here
