"The mind of a Mad Tech!"

* If you are not already familiar with SQL injection I would recommend reading http://en.wikipedia.org/wiki/SQL_injection *

The recent injection attacks that have been seen against ASP and ASP.Net coded sites takes advantage of vulnerabilities in improperly coded sites. These attacks can be mitigated by simply running any user input that can come in contact with the database through a sanitization process, and this does not apply to .Net and ASP code but any language. Below are examples of how to protect your code from Injection attacks.

If you have are dealing with a URL like http://mysite.com/somepage.aspx?page=2 instead of doing:

Dim page_num as String
Dim SqlQuery as String

SSN = Request.QueryString(“page“)
SqlQuery = “SELECT au_lname, au_fname FROM authors WHERE au_id = ‘” + page_num + “‘“

1. Write your dynamic queries using parameterized queries

Dim SSN as String = Request.QueryString(“page“)

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id”)
Dim param = new SqlParameter(“p_id“, SqlDbType.Int)
param.Value = SSN
cmd.Parameters.Add(param)

2. Set the length of the input data

If you know that the length of the input data will not be longer than a set amount then you should constrain it to that length. In this case we are going to constrain the page length to 4 characters which will give us up to 9999 pages.

Dim page_num as String = Request.QueryString(“page“)

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id“)

‘Here we are setting the max length to 4 characters
Dim param = new SqlParameter(“p_id“, SqlDbType.Int, 4)
param.Value = page_num
cmd.Parameters.Add(param)

3. Sanitize any escape characters from the SQL query

Dim page_num as String = SafeSQLLiteral ( Request.QueryString(“page“) )

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id“)
Dim param = new SqlParameter(“p_id“, SqlDbType.Int, 4)
param.Value = page_num
cmd.Parameters.Add(param)

.

.

.

Private Function SafeSQLLiteral(ByVal inputSQL as String) as string

Return inputSQL.Replace(“’”, “’’”);

End Function

More information and code examples available at:

Coding techniques for protecting against SQL injection – http://forums.asp.net/t/1254125.aspx

SqlParameter Class - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx?wt.slv=rightrail

SqlDbType Enumeration – http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

Protect From SQL Injection in ASP.NET - http://msdn.microsoft.com/en-us/library/ms998271.aspx

Go to http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx and place the code for the SQL Checker into SqlCheckInclude.asp with one edit, remove `”@”,` from the end of the first line in the BlackList Array. Next place the error page code in ErrorPage.asp at the root of your site. These two file can also be found at http://jamiemoney.com/downloads/files/ASPFormChecker.zip

Place an include for the SQL Checker at the top of any ASP form page, for example

<!–#include file=”SqlCheckInclude.asp”–>

<%

response.write(request.form(“FName“))

response.write(” ” & request.form(“LName“))

%>

<FORM METHOD=”POST“>

First Name: <INPUT NAME=FName></INPUT><BR>

Last Name: <INPUT NAME=LName></INPUT><BR>

<BUTTON TYPE=SUBMIT>Submit</BUTTON>

</FORM>

This will catch anything in the black list array and throw and redirect to the error page if it is detected including SQL commands and Escape characters.

More information and code for preventing SQL Injection and Cross Site Scritping:

How to prevent cross-site scripting – http://support.microsoft.com/kb/252985/

PHP like all other languages needs to have any user input sanitized there is a library available at http://superb-west.dl.sourceforge.net/sourceforge/owasp/owasp-php-filters.zip when included with your code will provide a simple way of sanitizing data for different uses. This can be used for interaction with a SQL database but its recommended that any user input field be sanitized as it will help prevent not only SQL injection but Cross Site Scripting as well.

Using the sanitization library above is very simple If you have are dealing with a URL like http://mysite.com/somepage.php?page=2

Instead of doing:

<?php

$con = mysql_connect(“db_server”, “user”, “pass”);

mysql_select_db(“DatabaseName”, $con);

$result = mysql_query(“SELECT * FROM Pages Where PageID=’“.$_GET['page'].”‘“);

mysql_close($con);

?>

Include the Library in your code:

<?php require_once(“includes/sanitize.php“); ?>

Pass the data you want to sanitize to the proper filter:

<?php

$con = mysql_connect(“db_server”, “user”, “pass”);

mysql_select_db(“DatabaseName”, $con);

//We pass the value for page from the Get to an sanitize for only integers

$page = sanitize_int($_GET['page']);

$result = mysql_query(“SELECT * FROM Pages Where PageID=’“.$PageID.”‘“);

mysql_close($con);

?>

Now the get field has been stripped of anything that is not an integer.

PHP Sanitize Library Function Listing

· sanitize_paranoid_string($string) — returns string stripped of all non alphanumeric

· sanitize_system_string($string) — returns string stripped of special characters

· sanitize_sql_string($string) — returns string with slashed out quotes

· sanitize_html_string($string) — returns string with html replacements for special characters

· sanitize_int($integer) — returns ONLY integer (no extraneous characters)

· sanitize_float($float) — returns ONLY float (no extraneous characters)

Suppose we have a main page that lists news articles for an organization and links to the articles with a URL similar to this: http://mysite.com/articles.cfm?articleid=2

On your articles.cfm page you might have a query that looks like this:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = #URL.articleid#

</cfquery>

This is a pretty standard select, except for the where clause. Because we are using a URL variable it would be easy for someone to add on to our URL: http://mysite.com/articles.cfm?articleid=2;DROP TABLE Articles

Now your cfquery looks like this:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = #URL.articleid#; DROP TABLE Articles

</cfquery>

With this method, someone can easily drop tables, or insert mallicious scripts into you article’s body. There are two methods to protect yourself from this.

DataSource Setup

Probably the easiest solution in ColdFusion without having to rewrite all your queries is to setup a separate DataSource or DSN. Your application would then have a DSN for password protected pages where authenticated users can make changes to the database that would have full permissions. A second DSN would then be created which only has SELECT rights to be used on your public facing pages. This can be done by following these steps:

1. Open your DataSource in the ColdFusion Administrator and click the “Show Advanced Settings” button.

2. Under the Allowed SQL section, make sure only SELECT is checked off

3. Click Submit to save your DataSource.

If anyone tries to add something other than a Select statement to your query strings, ColdFusion will throw an error before reaching your database.

Cfqueryparam

The second option is to use the cfqueryparam tag whenever you are getting dynamic data from a form or URL and passing it to your database.

Our example from before would turn out to be:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = <cfqueryparam value=”#URL.articleid#” cfsqltype=”cf_sql_integer”>

</cfquery>

Now if some unscrupulous visitor attempts to add an Insert or Drop command, they will be given a ColdFusion error message instead. As always, surrounding queries in cftry tags is useful to keep prying eyes form seeing the details of your error message.

The cfqueryparam tag supports the following data types:

• CF_SQL_BIGINT
• CF_SQL_BIT
• CF_SQL_CHAR
• CF_SQL_BLOB
• CF_SQL_CLOB
• CF_SQL_DATE
• CF_SQL_DECIMAL
• CF_SQL_DOUBLE
• CF_SQL_FLOAT
• CF_SQL_IDSTAMP
• CF_SQL_INTEGER
• CF_SQL_LONGVARCHAR
• CF_SQL_MONEY
• CF_SQL_MONEY4
• CF_SQL_NUMERIC
• CF_SQL_REAL
• CF_SQL_REFCURSOR
• CF_SQL_SMALLINT
• CF_SQL_TIME
• CF_SQL_TIMESTAMP
• CF_SQL_TINYINT

· CF_SQL_VARCHAR

This entry was posted on Saturday, June 14th, 2008 at 8:39 am and is filed under MSSQL 2000/2005, Technology Related. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.