SQL Injection

General Information
, ,
#1

What is SQL Injection

SQL injection is used to describe attacks on a web application’s database by using vulnerabilities within the Web Application itself to execute malicious SQL statements. These vulnerabilities could possibly be present in any web application that utilizes a SQL database. This attack can be used to circumvent authentication and authorization methods. This can lead to unauthorized access to sensitive data (customer data, trade secrets, personally identifiable information, etc…).

Tips to Prevent SQL Injection Attacks

To sanitize form input for sending to a database, always be sure to escape the single quote by searching and replacing it with two single quotes. This will cause the database to send the quote string as a literal character rather than interpreting it as the closing of a string. Be aware, however, that since numeric input does not require quotes, this technique will not be effective. In the case of numeric input, simply check that the form input is indeed numeric.

One method of preventing SQL Injections is to avoid the use of dynamically generated SQL in your code. By using parameterized queries and stored procedures, you then make it impossible for SQL injection to occur against your application.

  • Escape single quotation marks Include code within your web applications that replace single apostrophes with double apostrophes. This will force the database server to recognize the apostrophe as a literal character rather than a string delimiter.
  • Filter user input Filtering user input before it is passed into a SQL query will prevent the possibility of executing SQL commands. (ex. Strip input strings of all characters that are not alphanumeric (a-z, A-Z, or 0-9)).
  • Limit the privileges available to the account that executes web application code If an account only had permission to perform the intended action (retrieving records from a table), then delete, insert or create queries would not be possible.
  • Reduce or eliminate debugging information When an error condition occurs on your server, the web user should not see the technical details of the error. This type of information could aid an intruder seeking to explore the structure of your database.
  • For PHP users connecting to MySQL databases, use mysql_real_escape_string on each variable You can view additional information in PHP’s official documentation for this function.
  • For PHP users connecting to MySQL databases, use prepared statements You can do this using the MySQL imporved Extension in PHP 5. You can find additional information in PHP’s official documentation.

Preventing SQL Injection in Coldfusion

To prevent SQL injection within a coldfusion application you will need to take advantage of the cfqueryparam tag. You can find additional information about this tag in our article covering this topic.

Cleaning your MSSQL Database after a SQL Injection Attack

To clean a MSSQL Database from SQL injection, a stored procedure can be used.

  1. Using SQL Server Manager Studio, copy and paste the code below into the SQL tool and execute. This will create the SearchAndReplace stored procedure that will allow you to clean your MSSQL Database.
CREATE PROC SearchAndReplace
(
	@SearchStr nvarchar(100),
	@ReplaceStr nvarchar(100)
)
AS
BEGIN

	-- Copyright c 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string and replace it with another string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 2nd November 2002 13:50 GMT

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	SET @RCTR = 0

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				SET @SQL=	'UPDATE ' + @TableName + 
						' SET ' + @ColumnName 
						+ ' =  REPLACE(' + @ColumnName + ', ' 
						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				EXEC (@SQL)
				SET @RCTR = @RCTR + @@ROWCOUNT
			END
		END	
	END

	SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

GO
  1. Within Management Studio type or copy/paste the following to replace all occurrences of ‘badurl.com’ with:
EXEC SearchAndReplace '<script>badurl.com<script>', ''
  1. Execute the command above, note for smaller databases this will run quickly but for larger databases, it could take a long time to complete.