TUCoPS :: Web :: General

Are other SQL Servers (Sybase, Oracle, DB2) subject to SQL injection?

Yes, to varying degrees. Here is a site that can get you more details on some of the issues with other SQL Servers. http://www.owasp.org

What is SQL Injection and why is all this information not included in the regular FAQ?

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.

What causes SQL Injection?

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 are the effects?

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:

Privilege Level	Consequences
sa	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.
db_owner	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.
normal user (no fixed server or database roles) (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.

How can I prevent SQL Injection in my applications?

Here are some tips for avoiding SQL injection issues:

If a user is asked to input a number, verify the data type using ISNUMERIC or equivalent functions
For string data, replace single quotes with two single quotes using the replace function or equivalent

goodString = replace(inputString,','')

Use stored procedures to abstract data access so that users do not directly access tables or views
When using stored procedures, implement them using the ADO command object so that variables are strongly typed
Establish strong coding standards involving code review and peer-test often

How do I test if an existing application is subject to SQL Injection?

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.

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....