Building your own IDS tripwire.
Overview
In this article I will be looking at how to build a tripwire into your webserver. The examples that I give here are using IIS / ASP / MSSQL, but the theory remains vitually unchanged for any webserver / ISAPI langauge / SQL database combination. The concepts covered in this tutorial are just as applicable to an Apache / PHP / MySQL platform as they are to a Microsoft one. (Thanks to Jethro for confirming that for me!)
The only 100% secure webserver is one with port 80 closed, but if you intend to host a public web service that just isn't possible. My objective here was to provide an additional layer of security in handling requests received by my webserver by providing a simple means of logging any attempts to interfere with or abuse my webserver.
These sort of intrusions are not repelled by your a firewall because they attack a port that, on a webserver, is open by design. The sort of exploits can include office extension scans, traversal scans, buffer overflow exploits, mail script attacks and SQL injection, all of which use your own web service against you. Without some sort of filter to detect such attempts the webmaster of a busy site can quite happily spend entire days trolling through logs.
You can also use the information collected to prepare abuse reports for a users ISP. See my earlier tut on Hunting down skript kiddies for more details.
What is a tripwire?
In this context of this document a tripwire refers to a mechanism by which you can flag suspicious events in your web log file. The simplest way of doing this is by using an SQL datasource to store the web logs. Then, using SQL statements, we have the ability to apply SQL filters to webserver log information and pull out information relating to potential intruders.
Configuring SQL logging
The process for enababling SQL logging on IIS is as follows:
1...Create an SQL Datasource in MSSQL. I will refer to this database as the WebLog Database
2...Create a table within the WebLog database called InternetLog using the script below
3...Create an ODBC datasouce (via the control panel) pointing to the WebLog Databse called Weblog
4...Set the logging options in the site properties of the MMC to SQL logging
5...Configure the service to save data to the Weblog ODBC Datasource in the table of Internetlog
You can repeat actions 4 and 5 for each of the web services that you wish to monitor, or alternatively set this up as the default propeties for your master website and all subsequently created site will inherit this characteristic.
The InternetLog Table
You can create the InternetLog table described above by running the following script against the WebLog database in iSQL or WiniSQL. This creates all of the fields of correct type and length required by IIS SQL logging.
Quote:
if exists (select * from sysobjects
where id = object_id(N'[dbo].[Internetlog]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Internetlog]
GO
CREATE TABLE [dbo].[Internetlog] (
[ClientHost] [varchar] (255) NULL ,
[Username] [varchar] (255) NULL ,
[LogTime] [datetime] NULL ,
[Service] [varchar] (255) NULL ,
[Machine] [varchar] (255) NULL ,
[ServerIP] [varchar] (50) NULL ,
[ProcessingTime] [int] NULL ,
[BytesRecvd] [int] NULL ,
[BytesSent] [int] NULL ,
[ServiceStatus] [int] NULL ,
[Win32Status] [int] NULL ,
[Operation] [varchar] (255) NULL ,
[Target] [varchar] (255) NULL ,
[Parameters] [varchar] (255) NULL
) ON [PRIMARY]
GO
Testing the configuration
Restart your webserver from the command prompt with the NET START|STOP w3svc command.
Once your webserver has restarted, if you did everything right, you should now be able to see a new record created in this log for each hit received by your site. If you are using Apache please refer to your product documentation as to what fields are required for SQL logging.
Filtering the web log
I decided that I would filter suspect records out to a seperate table, called intruder log for analisys by an operator. This IntruderLog table should have the same fields, types and lengths as the InternetLog table described above. The secondary reason for this was so that the operator was not working on the live data. The primary reason for this design choice however was that on my system the data in the InternetLog is parsed to provide customer site statistics (which I intend to cover in a later tutorial - bear with me - there's method in my madness ;)).
I also decided that my tripwire should do the following:
1...Maintain a list of suspect IP addresses that an operator was currently monitoring
2...Allow the removal of IP addresses from the monitoring list without deleting the actual log entries from the IntruderLog' table
3...Operator to be able to analyse this information via a web browser.
The IntruderIP list described above has a couple of benefits. First I can stop monitoring an IP without deleting the log entries, but, secondly, if I do stop monitoring an IP, and that IP returns the system will flag up not only their current log, but also their previous visits aswell. You can use this information to profile your attackers, their capabilities and next likley move.
The script to create this IntruderIP table is given below.
Quote:
if exists (select * from sysobjects
where id = object_id(N'[dbo].[IntruderIP]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IntruderIP]
GO
CREATE TABLE [dbo].[IntruderIP] (
[IntruderID] [int] IDENTITY (1, 1) NOT NULL ,
[IntruderIP] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
The Filter
I actually get SQL Server Agent to run this job at timed intervals, but you could just as simply write a Pearl or WSH script to use with the AT command to schedule the execution of this SQL filter.
This filter is a fairly basic one, but I'm not going into a lesson in SQL in this tut. If you want to impliment this type of system in a production enviroment you should consider the sorts of things you want to track and write your own 'filter'.
The premise for the following filter is based on the fact that most web style attacks (especially traversal scanners and SQL injection techniques) create dirty logs. Most, if not all of these attempts will cause at least some errors in the server logs. I have therefore based my filter around finding users that have created error and dropping their entire transaction history to the IntruderLog Table.
Quote:
--Build new Intruder IP list from
--Existing list and new data
--This function ensures that
--there are no duplicate IPs
--in the Intruder IP list.
--Clear the Tempoary IP list
delete from tempIP
--**This bit is the Filter!!
--Insert all the IPs of browsers with:
--Requests with return codes other that 200 (ok) and 302 (redirected)
--GET Requests with parameters or POST requests
--from the InternetLog file to the TempIP table
INSERT INTO tempip ( IP )
SELECT ClientHost AS IP
FROM Internetlog
where ((parameters <> '-' ) or (operation='post'))
and servicestatus <> '200'
and servicestatus <> '302'
GROUP BY ClientHost
--Copy all the existing monitored
--IPs into the TempIP table
INSERT INTO tempip ( IP )
SELECT IntruderIP AS IP
FROM IntruderIP
GROUP BY IntruderIP
--Delete the old monitored list
delete from IntruderIP
--Create a new IntruderIP list based on
--grouped new and old data
INSERT INTO IntruderIP ( IntruderIP )
SELECT ip AS IntruderIP
FROM tempip
GROUP BY ip
--Insert new data for suspect IPs
--into IntruderLog table
INSERT INTO intruderlog ( clienthost,username,logtime,service,machine,serverip,processingtime,
bytesrecvd,bytessent,servicestatus,win32status,operation,target,parameters )
select intruderip.IntruderIP as clienthost,
internetlog.username,
internetlog.logtime,
internetlog.service,
internetlog.machine,
internetlog.serverip,
internetlog.processingtime,
internetlog.bytesrecvd,
internetlog.bytessent,
internetlog.servicestatus,
internetlog.win32status,
internetlog.operation,
internetlog.target,
internetlog.parameters
from intruderip join internetlog on intruderip.intruderip = internetlog.clienthost
order by intruderip
--Cleaning up the IntruderLog Table:
--Last I heard there aren't any remote explots for images
--so we ignore requests for this type of file
DELETE from intruderlog
where right(rtrim(target),3) = 'gif'
or right(rtrim(target),3) = 'jpg'
or right(rtrim(target),4) = 'jpeg'
or right(rtrim(target),3) = 'png'
or right(rtrim(target),3) = 'bmp'
Creating a web based monitoring system
Ok - so now we our filter set up to run every x minutes using either a script with the AT command or using the SQL Server Agent, we want to create a new table and fill it with some information about the sites that we are monitoring. This is so as to make this information availible to the operator alongside the IntruderLog information via a web based interface. To do this I used one more lookup table to list the site host name against its service tag. I called this table SVC. It looks like this:
Quote:
if exists (select * from sysobjects
where id = object_id(N'[dbo].[SVC]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SVC]
GO
CREATE TABLE [dbo].[SVC] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Service] [varchar] (10) NULL ,
[IP] [varchar] (15) NULL ,
[Port] [varchar] (6) NULL ,
[Host] [varchar] (200) NULL
) ON [PRIMARY]
GO
I use another script to maintain the information in this table, but again, that's for another tutorial. For now lets just assume that we maintain this list manually, so that it contains the relevant information about our web services.
The Code!
Now I just need a script that brings all this information together for the operator. Here we go... I decided to link the IP addresses contained in the IntruderIP table directly to samspade so the operator doesn't have to think too much ;). Again, this is an ASP script, but would be fairly simle to write in any ISAPI lauguage.
Code:
<html>
<head>
<link REL="stylesheet" HREF="CSS/page.css">
<title>.:Server Logs:.</title>
</head>
<body>
<%
'Set a high script time out -
'This make take a while for some of the larger logs
Server.ScriptTimeout = 3600
Set cn = server.CreateObject("adodb.connection")
cn.Open "PROVIDER=SQLOLEDB;DATA_SOURCE=;UID=;PWD=;DATABASE=weblog"
select case request("action")
case "details"
sqlq = "select intruderlog.*, svc.host as svchost " & _
"from intruderlog join svc on (rtrim(('W3SVC' + svc.service)) " & _
"= rtrim(intruderlog.service)) " & _
"where rtrim(clienthost) = '"& trim(request("ip")) &"' order by logtime"
loglist sqlq,1
case "deleteall"
deleteLog request("ip"), 1
listips
case "delete"
deleteLog request("ip"), 0
listips
case else
listips
end select
cn.Close
Set cn = Nothing
sub listIPs
sqlq = "select count(intruderip) from intruderip"
set rs = cn.execute(sqlq)
response.write "<h1>Monitoring "& cstr(rs.fields(0)) & _
" network intrusion attempt(s).<h1>"
sqlq = "select intruderIP.intruderip, max(intruderlog.logtime) as lastlog " & _
"From intruderIP join intruderlog " & _
"on intruderip.intruderip=intruderlog.clienthost " & _
"group by intruderip.intruderip " & _
"order by lastlog desc"
set rs = cn.execute(sqlq)
while not rs.eof
response.write "<h2><a href='http://www.samspade.org/t/lookat?a=" & _
trim(rs.fields(0)) & "'>"&trim(rs.fields(0))&"</a></h2>" & _
"
(Last log entry: "& rs.fields(1) &")</p>" & _
"<a href='?action=details&ip=" & trim(rs.fields(0)) & _
"'>Show Log Details</a>" & _
" | <a href='?action=delete&ip=" & _
trim(rs.fields(0)) &"'>Delete IP</a>" & _
" | <a href='?action=deleteall&ip=" & _
trim(rs.fields(0)) &"'>Delete From Log</a>"
sqlq = "select intruderlog.*, svc.host as svchost " & _
"from intruderlog join svc on (rtrim(('W3SVC' + svc.service)) " & _
"= rtrim(intruderlog.service)) " & _
"where rtrim(clienthost) = '" & trim(rs.fields(0)) & _
"' and ((parameters <> '-' ) or (operation='POST')) " & _
"and servicestatus <> '200' " & _
"and servicestatus <> '302' " & _
"order by logtime"
'response.write sqlq
LogList sqlq,0
rs.movenext
wend
end sub
sub loglist(sqlq,all)
set rs1 = cn.execute(sqlq)
response.write "<table border='1' width='100%'>"
DrawHeaders(all)
while not rs1.eof
response.write "<tr>"
if all = 1 then response.write "<td>" & rs1.fields("clienthost") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("username") & "</td>"
response.write "<td nowrap width='10%'>" & rs1.fields("logtime") & "</td>"
response.write "<td nowrap width='10%'>"
if rs1.fields("svchost") & "" <> "" then
response.write rs1.fields("svchost")
else
response.write "195.8.181.206"
end if
RESPONSE.WRITE "</td>"
if all = 1 then response.write "<td>" & rs1.fields("machine") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("serverip") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("processingtime") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("bytesrecvd") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("bytessent") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("servicestatus") & "</td>"
if all = 1 then response.write "<td>" & rs1.fields("win32status") & "</td>"
response.write "<td nowrap width='10%'>" & rs1.fields("operation") & "</td>"
response.write "<td width='10%'>" & rs1.fields("target") & "</td>"
response.write "<td width='60%'>" & rs1.fields("parameters") & "</td>"
response.write "</tr>"
rs1.movenext
wend
response.write "</table>
"
end sub
sub DrawHeaders(all)
response.write "<tr>"
if all = 1 then response.write "<th>clienthost</th>"
if all = 1 then response.write "<th>username</th>"
response.write "<th>logtime</th>"
response.write "<th>service</th>"
if all = 1 then response.write "<th>machine</th>"
if all = 1 then response.write "<th>serverip</th>"
if all = 1 then response.write "<th>processingtime</th>"
if all = 1 then response.write "<th>bytesrecvd</th>"
if all = 1 then response.write "<th>bytessent</th>"
if all = 1 then response.write "<th>servicestatus</th>"
if all = 1 then response.write "<th>win32status</th>"
response.write "<th>operation</th>"
response.write "<th>target</th>"
response.write "<th>parameters</th>"
response.write "</tr>"
end sub
sub deleteLog(ip,all)
sqlq = "delete from intruderip where rtrim(intruderip) = '"& ip &"'"
cn.execute(sqlq)
if all = 1 then
sqlq = "delete from intruderlog where rtrim(clienthost) = '"& ip &"'"
cn.execute(sqlq)
end if
end sub
%>
</body>
</html>
/me stops to draw breath.
Ok. Thats all for now... In my next IIS/SQL article I intend to show you what happens to the rest of the information in the InternetLog table and publish the code for my simple stats package, but as I say - I'll save that for another day :)
If you enjoyed this article check out these others also by ntsa:
Credit card security
Dumping SQL data to a text file
Hunting down skript kiddies
Search Engine submission 'exploit'
Forced shutdown of a remote nt/2k server
Securing an installation of IIS 4. (No, seriously)
Remote DSN Connections, using WinAPIs and the registry
Scripting Internet Connections Under Window$
