Hi Folks,

For the past couple of weeks one of my clients websites has been under Sql Injection attacks. I would like to share my experience to help others avoid this unfortunate situation.

This site does use ListX all over the place and unfortunalty ListX does not fully protect you from these attacks. I'm not saying it should but because there is a check box saying protect against sql injection this can give a false impression.

My site was compromised when the attacker added additional code to an querystring that was being used to set the ascending or descending sort order of a select statement.

If the querystring was something like

SORT=DESC

All the attacker needed to do was add some sql code to the end, for example

SORT=DESC;DELETE FROM USERS

Now my using that querystring my ListX query becomes something like

Select * from SomeTable order by DESC;DELETE FROM USERS

ListX would pass this query to Sql Server which would run both SQL commands.

In my case the SQL code injected was far more complex and it ended up injecting html code into every text column in every table in my DNN website. This Html code was now included in all of my pages and it would load a javascript which then caused other problems.

An even simpler example would be where you use a numeric field to filter your query

Select * from SomeTable where ID=123

If 123 was sent via a url querystring then you may not think Sql injection is a problem. But the example above still applies

Select * from SomeTable where ID=123;DELETE FROM USERS

FYI: I have only become aware of this over the past 2 weeks as I have been struggling to fix my clients website, twice a day in most cases.

The 'Protect against Sql Injection' option works fine for values that will be enclosed in quotes in the query but not for numeric values or in my case my ascending/descending option.

So I am now amending my listX queries to make sure that in these cases I use the listX REPLACE formatter to replace any ';' with nothing or a space.

Example [FORMAT,@ID,{REPLACE:;, }]

After I do this I am going to have a well deserved beer <img src='desktopmodules/ntforums/images/emoticons/tongue.gif' height='20' width='20' border='0' title='Tongue' align='absmiddle'>

Regards

Tony

Everyone -
We have seen a growing number of attack attempts within our IIS logs due to some inept developers need to be disruptive and maliciously take down sites. This is not a new technology, and has been something that we have primarily remained ahead of throughout the internet age. However, it is important to note the feasibility of the attacks success on your website, and the ways you should be using ListX (and the very shortly arriving OWS) platform to remain in front of the curve.

Internally we created a tool specifically to spider our developed sites and log all possible attack points (urls with querystring parameters). These attack points are then attacked with a script that is similar to the current notorious sql injection content. The difference here is, our attack just injects records into a log table which identifies the exact breach location.

We will be making this a publicly available download shortly, because it is quite useful for this type of detection.

IN ANY CASE: PLEASE READ THE FOLLOWING SO YOU KNOW THE STANDARDS THAT SHOULD BE USED WITHIN LISTX AND OWS.

1. ALWAYS use Query Variables.
2. ALWAYS check the box to protect against SQL Injection.
3. WHEN using SQL Injection protection, ALWAYS place single quotes to the left and right of the consumed value - either IN the Query Variable definition (the main reason for the left and right attributes there), or within the Query itself.
4. NEVER place ANY replacement values that could have been touched by a user directly in a QUERY.

What does #4 mean? It simply means this... If you place [x,Querystring] in your Query directly - "x" is something that the user has access to before it goes to the server for processing. So they can place whatever they want in "x" even if you are anticipating an integer (which is the problem most users experience). This goes for Cookie and form variables also. And, sometimes embedded flow values that have been assigned to the session or actions. Meaning, if you assign an Action variable the value from the querystring, you will still have the same problem. Which is why Query variables are so important.

Steps are being taken to make these a bit more obvious in your configurations, and to provide a bit further protection, but these are the standards and PLEASE ADOPT THEM.

ALSO - some available stored procedures which are intended to be executed to recover your data (which the hackers have appended to) work well in some cases. However please consider the following. The hackers code script is imperfect and has the tendency to truncate values that are longer than it anticipated (4000 or 8000 characters). We have witnessed places where configurations have been truncated. The recovery scripts remove the appended value from the end, however this leaves configurations corrupted due to the truncation.

ALWAYS backup your databases regularly to avoid needing a recovery script. While the these recovery scripts *may* work well - the hackers script is not considerate of your database and hard work.

Thanks - and we welcome any ideas you may have to help avoid these issues. To note a few changes that will be made to the interface to further support these issues:

1. We will make single quotes the default value in the left and right attributes of your query variables.
2. We will add a data type selection within query variables so that the left and right arent necessary, instead you could simple say - this is numeric. And when it is not numeric, it assumes it to be empty.
3. We are looking into locations to place alerts when query variables are not being utilized within your condigurations.

Keep up the good work! - And Keep any ideas coming that you may have!

I had a SQL Injection attack on a classic asp site. There is a useful free tool from HP that you can also use to check for vulnerabilities. Being new to dnn/listx I cant vouch for its capabilities here but certainly it won't check any links behind login pages.

However, might be of use to someone...

google "hp scrawlr"

For anyone wanting further detail on this either to know how these attacks work, how to defend against them, or how to use our tool to find and isolate the problems using our tool:
http://lab.r2integrated.com/Wiki.aspx?topic=SQL_Injection

The link for the Checkpoint application is broken.

Could you please revise?

Hi all

The problem is concatenating strings together for submission to SQL Server. Something for the ListX people to be aware of IMO.

var = "user content"
cmd = "select * from table where field = " + var
is easily injectable. The simple answer is to parameterize

cmd = "select * from table where field = @var"

It will stop hackers from augmenting our SQL commands with their SQL. It also means we don't have to escape single quotes or any of the normally recommended countermeasures.

in ListX terms, creating a Variable @var is the easiest way to protect yourself, as long as you correct escaping features.