Astricon 2009 - Open Source Maximus

I'm at Astricon 2009, the Asterisk Development conference. Asterisk is an internet based, free phone system. Quite an interesting crowd of open source developers, solution providers, VOIP resellers, and hardware manufacturers; about 500 persons in total.

Chris DiBona of Google gave the keynote this morning and focused on Open Source. He pointed out that the majority of time that Google is interested in a technology company that they fund/support the company to get to a stable build and then make it available to the entire Open Source community. There are somewhere around 32 billion lines of open source code; a code base that will outgrow and out-maneuver most proprietary software applications. Ironically Google does not use Asterisks; Google Voice is a proprietary application that is specifically works of off Google's core servers; which Asterisk will not. So much for embracing open source!

I've been hanging out in the Coders Zone most of the day. The breadmaker and soda's are keeping the me and the developers here happy. Half the people here seem to either work for Digium (the inventor of Asterisk) or are a reseller of Digium. Although I've been a customer of Digiums; they are too big these days to care for the little guy; nevertheless, in my quest to find a phone system consultant they referred me to Leif, a fellow that literally wrote the book on Asterisks, and hopefully he can build our custom solutions.

The exhibit hall has shown a few phone system solutions of interest:

• Presenceco.com - a whole customer service platform (really sweet/complete system)
  
• VICIdial.com - call center management system
• Loquendo.com - text to speech solutions
• Braxtel.com - contact center people
• LumenVox.com - Speech-to-text provider

All-in-all; It's been a successful days quest here in Glendale, AZ.

Asterisk: Identify and Hanging Up a Call

When using an Asterisk with a SIP client like X-Lite; sometimes a disruption in the X-Lite client may occur, but Asterisks keeps the call active. If you're paying by the minute, this can be an expensive annoyance.

Identify if Asterisk is still on the line by running the command:
core show channels

pbx*CLI> core show channels
Channel Location State Application(Data)
Zap/16-1 (None) Up Bridged Call(SIP/7118-b6b1c5d8
SIP/7118-b6b1c5d8 s@macro-dialout-trun Up Dial(ZAP/g0/48###71268|300|tT)
Zap/13-1 (None) Up Bridged Call(SIP/7112-b6b0d0f8
SIP/7112-b6b0d0f8 s@macro-dialout-trun Up Dial(ZAP/g0/8884588762|300|tT)
Zap/7-1 (None) Up Bridged Call(SIP/7103-b6e0dc68
SIP/7103-b6e0dc68 s@macro-dialout-trun Up Dial(ZAP/g0/60###77511|300|tT)
Zap/11-1 (None) Up Bridged Call(SIP/7114-b6b2b8b0
SIP/7114-b6b2b8b0 s@macro-dialout-trun Up Dial(ZAP/g0/61###02929|300|tT)
Zap/6-1 (None) Up Bridged Call(SIP/7104-b6efd110
SIP/7104-b6efd110 s@macro-dialout-trun Up Dial(ZAP/g0/50###8698|300|tT)
Zap/1-1 (None) Up Bridged Call(IAX2/freepbx-3125
IAX2/freepbx-3125 s@macro-dialout-trun Up Dial(ZAP/g0/80###75414|300|tT)
Zap/2-1 (None) Up Bridged Call(SIP/7113-b67f3ed8
SIP/7113-b67f3ed8 s@macro-dialout-trun Up Dial(ZAP/g0/41###16000|300|tT)
Zap/10-1 (None) Up Bridged Call(SIP/7106-b67241a0
SIP/7106-b67241a0 s@macro-dialout-trun Up Dial(ZAP/g0/95###26252|300|tT)
Zap/4-1 (None) Up Bridged Call(SIP/310-b607b0d0)
SIP/310-b607b0d0 s@macro-dialout-trun Up Dial(ZAP/g0/3083446400|300|tT)
SIP/323-08806ba8 (None) Up Bridged Call(Local/317@from-in
Local/317@from-inter s@macro-dial:7 Up Dial(SIP/323|22|trTM(auto-blkv
Local/317@from-inter 1001@from-internal:1 Up Bridged Call(Zap/9-1)
Zap/9-1 1001@ext-queues:20 Up Queue(1001|t||custom/RelevantA
Zap/5-1 33BBACE092CA83BA9E39 Up MeetMe(33BBACE092CA83BA9E39E8C
Zap/pseudo-331706926 s@from-zaptel:1 Rsrvd (None)

In the above, I determined that the MeetMe call was our stale conference call session. You then, hangup a line, with the command:
soft hangup Zap/5-1

Killing Excessive Blocking Transactions in SQL

I recently made a small error in a SQL Server parsing function that caused an endless loop. Unfortunately SQL doesn't escalate an error and will allow these transactions to go on endlessly.

I quickly realized the error and attempted to correct the SQL function. However since there were users stuck in this endless loop, my ALTER FUNCTION transaction was being blocked.

KILLing individual SPIDs didn't work since I could not keep up with the number of backup of user requests.

To circumvent this, I ran a process to KILL all queries blocking my SPID:

declare @bCont bit, @i int, @s varchar(100)
set @bCont = 1

while @bCont = 1
Begin
select @i = blocked
from master..sysprocesses
where spid = 113

if isnull(@i , 0) <> 0
BEGIN
set @bCont = 1
print @s
SET @s = 'kill ' + convert(varchar(12), @i)
exec (@s)
waitfor delay '00:00:00.05'
END
else
SET @bcont = 0
end


ASP.net Session Error remedy

Has your SQL server restarted lately; well if you're ASP.net application is reporting errors like these it might need some reconfigurations:

SELECT permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.
INSERT permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.
UPDATE permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.


The cause of this is that your ASP.net application uses SQLServer session storage. This is generally a good design pattern; the SQLServer session storage allows you to have multiple webserver and thereby scale your infrastructure. SQLSession is one of three possible session storage method and is defined in the web.config with a directive like:

<sessionstate mode="SQLServer" timeout="1440" sqlconnectionstring="Data Source=RelevantYellow.sql.relevantads.com;User ID=WebSession;Password=password" cookieless="false"></sessionstate>

However it relies upon a temporary database in SQL server. Each time the server is rebooted, the entire database is recreated along with the access permissions. Your database server is doing its rightful job of blocking non-administrative accounts from doing things without authorization.

Solution
Instant Fix. Grant permissions to you WebSession database user to tempdb. Simply make it a db_owner. This is okay, however, you'll need to repeat the step each time the server is restarted.

USE [tempdb]
GO
EXEC sp_addrolemember 'db_owner', 'WebSession'


Temporary Relief.
Grant sysadmin privledges to the WebSession account:

EXEC master..sp_addsrvrolemember @loginame = 'WebSession', @rolename = 'sysadmin'


However granting such copious rights to this account is dangerous. Should you web application be compromised, hacked or be exposed, you could be caught with your shorts down.

Best Solution
Instead of relying on tempdb, the session data can be stored in permanent tables. Install the SQL script:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallSqlState.sql


Alternatively, from the command prompt, run:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -E -sstype p


Hyperlink behavior - forcing popup window

"I'm clicking the link, but nothing happens"

Many websites use popup hyperlinks. While that has been fine in the past, with the advent of tabbed browsers, hyperlink popup windows will not behave as predictable as they once did. Furthermore, since users may have several websites open, it adds to the possibility that another site is using the same windows entitled "new".

The problem is that the hyperlink uses the directive target="new" to cause the popup window. Subsequent clicks to that hyperlink will cause the new windows to be refreshed. Microsoft Windows will bring that Internet Explorer to focus or cause it to flash until clicked. However, with tabbed browsing only newly opened tabs will come into focus. So when a user clicks on an already opened tab, it will not be flashing or be focused on.

If the web experience intent is to guarantee that the user will be shown the hyperlink in a popup form; instead use target="_blank". However if you want the end-user to use a single browser window, use the "new" declaration of another name.

Example:

Target Link Test	Experience
new	Subsequent clicks does not yield new popups.
_blank	Each click gives its own popup.

Note that "_new" is treated the same as "new".

Blocking brute force administrator account attack via FTP

Dissection of the ASP SQL Injection Outbreak

Several website owners this past week, including the United Nations and the UK Government, were left with a compromised database after being hit with the notorious SQL injection bug. This is a flaw in which a basic ASP webpage can run malicious script on a SQL server.

The mass attack hit a number of website that were ASP driven and supported querystring paramters for database lookup. Webpages can allow for dynamic retrieval of information based upon querystring parameters; however if programmed in a basic manner, it might allow for a malicious script to be run.

Here is a basic example of a page that is vulnerable:

Page: BadCode.asp

Dim myVar
myVar = Request("input")

...
objComm.CommandText = "Select * from myTable where ID=" & myVar
objRS.Open objComm.Execute

...


In the normal situation, this will query the database for the specified record. However if the value of input is passed in as a malicious script, it could be run by the SQL server and run amok! Lets have a look...

Example SQL ASP Malicious Script

Given our BadPage.asp example, consider what the following HTTP request would do:
http://samplesite/BadCode.asp?input=555;EXECUTE("MALICIOUS SCRIPT")

In the mass attacks we've seen from China (IP=222.91.65.191) in the past week, the actual querystring script was encoded into hexadecimal so that it was interpreted by SQL, but not munged by ASP.

***REMOVED PER GOOGLE EDITORIAL GUIDELINES***

Here is the script as interpreted by SQL:

***REMOVED PER GOOGLE EDITORIAL GUIDELINES***


The outcome of this executing the SQL command would result in modifying all the text content in the entire database. The text content would direct your website users to download malware to their desktops.

Identifying Websites Vulnerable To SQL/ASP Bug

A basic query for ASP pages to Google can be performed to identify potential sites with the flaw.
Combine this script with a mass crawler to identify the querystring parameters, then hit the pages in mass coordination worldwide; pretty cleaver stuff indeed... The actual purpose of this latest infection from China was even more impressive as it ran a javascript to end-user coming to the website. That javascript contained several other fun forms of malware; which fortunately my browser decided to pass on...

SQL Injection Fix

There is no patch that prevents this flaw. A fully patched, secure and locked down server can be susceptible to this problem. It is only eliminated with good coding practices. I recommend exclusively using stored procedures to gain access to the database; in that way you tightly control all access levels and can avoid the pitfalls of rouge or poor programmers and basic exploits.

In short, don't allow uncontrolled, dynamic SQL statements to be run from the web application.