Database Security

I saw the following on the webappsec list at Security Focus:

| I've heard this preached before.
|
| Using JDBC properly can help protect against SQL Injection.
|
| What protections does JDBC provide?
|
| Does java encode the input to not be malicious?
|
| I'm curious where in the java source/libraries does jdbc help
| to mitigate malicious input when using jdbc.
|
This preach is applicable for any programming language. It
all depends on how well you have done input & output
validation. As in what input you expect & what input is
malicious for your app. If all goes well you can make SQL
injection very difficult or even impossible . The reason I
say difficult, because it all depends on how well the SQL
injection is crafted. As far as I recollect I don't think
JDBC or for that case even java gives you predefined class
for doing that. But there is quite a possibility that some
one on the internet must have surely written these classes.
--
Taufiq
http://www.niiconsulting.com/products/iso_toolkit.html

I don't disagree with Taufiq's assessment. However, I do disagree with his acceptance of the status quo. I wrote a rant on this blog responding to a complaint that security professionals are not taken seriously. In it, I pointed out that the security industry should promote improving the security climate, not just react to it with solutions 'for a price'. The example I gave was *DBC libraries.

The JDBC package, java.sql, does not supply any security parsing. This is not the real workhorse, but it should at least provide a method for this. Each database supplies a jar that java.sql classes call to access the specific database. This is where security parsing must be handled.

The thing is that parsing input is tricky. The first step is to validate that the input is correct for the column data type. This is reasonably straightforward for simple types like integer and varchar. But the way different databases support binary data and very large fields is not consistent. There is also support for non-standard data, such as PostgreSQL's support for the inet data type.

The JDBC Connection interface includes the getMetaData method, which returns the information supplied by the specific database library, some of which is unique to that database. There are not only differences between databases, it is even possible that there are differences between versions of the same database. This could be an issue for an application because:

Some DatabaseMetaData methods return lists of information in the form of ResultSet objects. Regular ResultSet methods, such as getString and getInt, can be used to retrieve the data from these ResultSet objects.

All unique information must be verified for every version of the database supported. And if you are supporting multiple databases, it is that much more difficult.

The next step is to escape all characters that have special meaning, such as single quote and backslash. But again, each database has its own special characters that must be accounted for, such as ampersand in Oracle, and the E'...' escape pattern in PostgreSQL.

Update: Eric Kerin points out in his comments that the PreparedStatement interface does this, and after some testing I have found that this is the case. My excuse is that there is nothing in the javadoc for the SQL package or the PreparedStatement interface that explains this. Instead the documentation promotes it for optimizing frequently used statements. See my reply below for further responses to comments.

Also, there is a good article on this issue at the Open Web Application Security Project, which I found by googling for java and "sql injection".


The current situation places the responsibility for security on the thousands of application programmers, who must now dig into the internals of the database(s!) on the backend of their applications. If instead, the database development teams provided a parser for each field of data, it would be possible to determine if the input would result in a message, like this one that I was able to create from testing various input sequences:

WARNING: nonstandard use of \' in a string literal

ERROR: relation "foobar" does not exist

STATEMENT: select foo from foobar

I'm still working on parsing that construct and reworking it in a way that does not reject the data out of hand, because it might be a legitimate description of an Event, or possibly a Trigger definition. I am fortunate, because the input is not read from the network. You might not be so lucky.

And before I leave this topic/rant, I must point out that application programmers need to work closely with their DBAs to be sure that permissions are set on tables to allow only as much access as absolutely necessary and no more. If you don't have a DBA and/or maintain the database yourself, you need to become very familiar with the levels of GRANTing access and the use of roles to at least limit the damage when SQL injection attacks succeed. In my own experience, as well as reports from others, the attacks on applications, and databases especially, is continuing to increase.

If anyone is interested in the database security in the Realeyes UI, check out the Database and ValidatorDBForm modules, and then see how they are used in any of the Window*Base.java modules at the UI subversion repository. The ValidatorDBForm class includes the InterfaceAdminExtendedProcessing interface to do extra contextual error checking, which really is the job of the application. There are some pretty good examples of its use in the WindowsRules(Triggers/Actions/Events).java modules.

I'm pretty sure I'm talking for all application developers when I say (as a security guy), "Hey database developers, a little help!"

Later . . . Jim

Whatcha Doin'?

As soon as I put the latest download on SourceForge, I started working on the user interface. I am hoping to bring it up to a version 1.0 level of usability. At the rate I am going, I expect to have a download ready in about a month.

So that's what's coming. But what I barely mentioned, was a new feature in the last package that I added a week or so before it was built. The reason was, I wanted to see how well it worked in the pilot project before explaining it. But now I have done that, and I am pretty happy with it.

The feature is a comparison of the client and server session size. When a TCP session ends, the client data is multiplied by a factor, and if that is larger than the server data, it is reported. The multipliers are specified by port, so each protocol can be handled uniquely.

In the pilot, I have set port 80 to have a multiplier of 1, just as a proof of concept. There have not been too many reports on it, and those can be grouped in two categories:

• Document uploads: This was what I was hoping to see. And I believe that it means if there is an exploit that loads a rootkit, that would be reported, as well.


• Large cookies: I never realized how much is saved in some cookies. I have seen several (Facebook, I'm looking at you!) that are over 4Kbytes.


• Miscellaneous: The rest of the reports are usually client requests for many files, but some of them don't get sent for some reason.

Again, this is mostly a proof of concept feature and I hope to expand on it down the road. But it gives me the sense that the Realeyes IDS is capable of detecting behavior. I think that's pretty cool.

Later . . . Jim

More Results from Realeyes

For the past few weeks, I have been learning a lot about the site where the Realeyes pilot project is being run. After seeing several reports of incidents from Europe and Asia, it occurred to me that I could create a rule to monitor non-US IP addresses.

To do this, I got the IANA IPv4 Address Assignments, and created a list of the high order octet assigned to each of Europe, Asia, Africa, and Latin America. The rule was simply a SYN packet and any match on the first octet of the source address with a value in the list.

At first, I simply turned it loose, which generated over 20,000 reports. I was able to reduce that quickly by filtering on the "Referer:" field. First were the requests being referred by one of the site's own web servers. Then I found other sites, such as Google, that were referring browsers to the monitored network. These were all defined in a single Action , which was then defined in the Event with the 'NOT' flag set. This resulted in about 5,000 reports which have been further reduced by adding some of the site's commonly requested web pages to the filter.

The rule is now: Any connection requested by an IP address in Europe, Asia, Africa, or Latin America that is not referred by a site server or one in a list of other servers, and is not requesting one of a list of web pages. If a match is found, the first 64k of both halves of the session are reported. I was thinking of adding a filter where the web server responds with a 200 message, but that could miss a successful exploit.

Of the reported sessions, many are web crawlers for various international search engines. A large number are being referred by other servers. And a fair number appear to be overseas students. Many of the web crawler and overseas student connections consisted of over 100 sessions. Using the 'Ignore Source Address' option, I could close the incidents for a single source IP address without creating a report in a single click. This allowed me to reduce the reports by 2,000 - 3,000 fairly quickly.

And that left me with about 1,000 connections of 1 - 5 sessions each. It was easy to display the playback window and see the client request and the server response and make a decision on whether it was valid or not. Usually, the server responded with a 200 code and sent the requested page. I was able to check about 10 of these per minute, so it only took a couple of hours to run through the entire list.

As far as invalid activity, there have been several targetted scans. By this I mean that the requests are only sent to web servers, and they actually make HTTP requests. These were easy to see by sorting the reports on the source IP address and looking for connections to multiple servers.

The most interesting one was 'GET /manager/html'. This appears to be a Tomcat exploit which tries to gain access to the administrator account. Of the dozen web servers that received this request, all but one replied with 404 "Not Found". The other one replied with 401 "Unauthorized" and the source host then sent over 150 variations of the authorization code field. The codes were mixtures of numbers and mixed case letters that looked like they were taken from a table. Some were as long as 25 characters, while others were only 5 or 6 characters. Fortunately, none were successful.

Another interesting discovery was that one of the monitored site's web servers was being used to store data. An application to allow students to participate in workgroup activities had been broken into and data was stored for some of those sites that are links in spam. It was the response from the server that alerted me to this. I saw a list of keywords meant to generate a lot of hits in search engines. I was then able to report the full path of the request to the web administrator and the server was cleaned of this and a few other pages.

The lesson I take from this is that Realeyes is capable of collecting a broad range of data, filtering it effectively, and providing enough information to analysts to very quickly determine the severity level of incidents. The rules for monitoring can be customized and tuned for the site's requirements, giving analysts and administrators a deep view of their environment. And since that is what I set out to do with this project, I am quite pleased.

Later . . . Jim

My App Fails the LSB

My position on the Linux Standard Base has evolved. When I first heard about it, I was all for it. The LSB as a standard could be useful to some, but now I disagree with the goals of the LSB working group. To be sure, this post is not about dissing the Linux Foundation. They have many worthwhile projects.

What follows is my experience with the LSB Application Checker, my take on the purpose of the LSB, and my own suggested solution for installing applications on GNU/Linux distributions. The Realeyes application failed to certify using the checker v2.0.3, which certifies against the LSB v3.2. Everything that it called out could be changed to pass the tests, but I will only consider correcting a few of the 'errors'.

After building the Realeyes v0.9.3 release, I collected all executable files in a common directory tree, downloaded the LSB application checker, and untarred it. The instructions say to run the Perl script, app-checker-start.pl, and a browser window should open. The browser window did not open, but a message was issued saying that I should connect to http://myhost:8889. This did work, and I was presented with the Application Check screen.

There was a text box to enter my application name for messages and one to enter the files to be tested. Fortunately, there was a button to select the files, and when I clicked on it a window opened that let me browse my file system to find the directories where the files were located. For each file to be tested, I clicked on the checkbox next to it, and was able to select all of the files, even though they were not all in the same directory. Then I clicked on the Finish button and all 87 of the selected files were displayed in the file list window.

When I clicked on the Run Test button, a list of about a dozen tasks was displayed. Each was highlighted as the test progressed. This took less than a minute. Then the results were displayed.

There were four tabs on the results page:

• Distribution Compatability: There were 27 GNU/Linux distributions checked, including 2 versions of Debian, 4 of Ubuntu, 3 of openSUSE, 3 of Fedora, etc. Realeyes passed with warnings on 14 and failed on the rest.


• Required Libraries: These are the external libraries required by the programs written in C. There were nine for Realeyes, and three (libcrypto, libssl, and libpcap) are not allowed by the LSB. This means that distros are not required to include the libs in a basic install, so they are not guaranteed to be available.


• Required interfaces: These are the calls to functions in the libraries. There were almost a thousand in all, and the interfaces in the libraries not allowed by the LSB were called out.


• LSB Certification: This is the meat of the report and is described in some detail below.

The test summary gives an overview of the issues:

• Incorrect program loader: Failures = 11


• Non-LSB library used: Failures = 4


• Non-LSB interface used: Failures = 60


• Bashism used in shell script: Failures = 21


• Non-LSB command used: Failures = 53


• Parse error: Failures = 5


• Other: Failures = 53

The C executables were built on a Debian Etch system and use /lib/ld-linux.so.2 instead of /lib/ld-lsb.so.3. The Non-LSB libraries and interfaces were described above, but there was an additional one. The Bashisms were all a case of either using the 'source' built in command or using a test like:

  while (( $PORT < 0 )) || (( 65535 < $PORT )); do

which, in other Bourne shells, requires a '$(( ... ))'. The parse errors were from using the OR ("||") symbol.

The fixes for these are:

• Use the recommended loader


• Statically link the Non-LSB libraries


• Use '.' instead of 'source'


• Rework the numeric test and OR condition

So far, all of this is doable, sort of. But every time a statically linked library is updated, the app must be rebuilt and updates sent out. Also, the additional non-LSB library is used by another one. So I would have to build that library myself and statically link the non-LSB library (which happens to be part of Xorg) in it (and that one is part of the GTK). The reason I am using it is because the user interface is built on the Eclipse SWT classes, which uses the local graphics system calls to build widgets.

The non-LSB commands include several Debian specific commands (such as adduser), and for my source packages I had to rework the scripts to allow for alternatives (such as useradd). But the other disallowed commands are:

• free: To display the amount of free memory


• sysctl: To set system values based on the available memory


• scp: Apparently the whole SSL issue is a can of worms


• java


• psql

Finally, all of the other 'errors' are: Failed to determine the file type, for the file types:

• JAR


• AWK


• SQL


• DTD and XML

Part of the problem with the LSB is that it has bitten off more than it can chew. Apparently Java apps are non-LSB compliant. So are apps written in PHP, Ruby, Erlang, Lisp, BASIC, Smalltalk, Tcl, Forth, REXX, S-Lang, Prolog, Awk, ... From my reading, Perl and Python are the only non-compiled languages that are supported by the LSB, but I don't know what that really means (although I heartily recommend to the Application Checker developers that they test all of the executables in their own app ;-). And I suspect that apps written in certain compiled languages, such as Pascal or Haskell, will run into many non-LSB library issues.

Then there are databases. Realeyes uses PostgreSQL, and provides scripts to build and maintain the schema. Because of changes at version 8, some of these scripts (roles defining table authorizations) only work for version 8.0+. The LSB Application Checker cannot give me a guarantee that these will work on all supported distros because it didn't test them. I have heard that there is some consideration being given to MySQL, but from what I can tell, it is only to certifying MySQL, not scripts to build a schema in MySQL.

After all this kvetching, I have to say that the Application Checker application is very well written. It works pretty much as advertised, it is fairly intuitive, and it provides enough information to resolve the issues reported by tests. My question is, "Why is so much effort being put into this when almost no one is using it?"

An argument can be made that the LSB helps keep the distros from becoming too different from each other and without the promise of certified apps, the distros would not be motivated to become compliant. But I only see about a dozen distros on the list, with Debian being noticeably absent. And yet, there is no more sign of fragmentation in the GNU/Linux world than there ever was.

My theory on why UNIX fragmented is that proprietary licenses prevented the sharing of information which led to major differences in the libraries, in spite of POSIX and other efforts to provide a common framework. In the GNU/Linux world, what reduces fragmentation is the GPL and other FOSS licenses, not the LSB. All distros are using most of the same libraries, and the differences in versions are not nearly as significant as every UNIX having libraries written from scratch.

I have to confess, I couldn't care less whether Realeyes is LSB compliant, because it is licensed under the GPL. Any distro that would like to package it is welcome. In fact, I will help them. That resolves all of the dependency issues.

While I am not a conspiracy theorist, I do believe in the law of unintended consequences. And I have a nagging feeling that the LSB could actually be detrimental to GNU/Linux. The only apps that benefit from LSB compliance are proprietary apps. The theory behind being LSB compliant is that proprietary apps can be guaranteed a successful installation on any LSB compliant GNU/Linux distro. I'm not arguing against proprietary apps. If a company can successfully sell them for GNU/Linux distros, more power to them. However, what if proprietary libraries manage to sneak in? This is where the biggest threat of fragmentation comes from.

But even more importantly, one of the most wonderful features of GNU/Linux distros is updates, especially security updates. They are all available from the same source, using the same package manager, with automatic notifications. If the LSB is successful, the result is an end run around package managers, and users get to deal with updates in the Balkanized way of other operating systems. That is a step in the wrong direction.

The right direction is to embrace and support the existing distro ecosystems. There should be a way for application teams to package their own apps for multiple distros, with repositories for all participating distros. The packages would be supported by the application development team, but would be as straightforward to install and update as distro supported packages.

There is such a utility, developed by the folks who created CUPS. It is called the ESP Package Manager. It claims to create packages for AIX, Debian GNU/Linux, FreeBSD, HP-UX, IRIX, Mac OS X, NetBSD, OpenBSD, Red Hat Linux, Slackware Linux, Solaris, and Tru64 UNIX. If the effort that has gone into LSB certification were put into this project or one like it, applications could be packaged for dozens of distros.

And these would not just be proprietary apps. There are many FOSS apps that don't get packaged by distros for various reasons, and they could be more widely distributed. Since the distros would get more apps without having to devote resources to building packages, they should be motivated to at least cooperate with the project. And don't forget the notification and availability of updates.

As a developer and longtime user of GNU/Linux ('95), I believe that all of the attempts to create a universal installer for GNU/Linux distros are misguided and should be discouraged. I say to developers, users, and the LSB working group, "Please use the package managers. A lot of effort has been put into making them the best at what they do."

Later . . . Jim

Testing, testing

I have been testing the Realeyes IDS at a local college for about 8 months now. However, it took almost 10 months of planning before the testing began. I contacted a half dozen sites and only the one felt capable of letting me set up the system in their environment. Looking at this from the point of view of the sites contacted, I consider myself quite lucky to have had this response. My advice to anyone looking for a similar situation is:

• Be clear: Write a letter or email that explains what you would like to do concisely and give some background on yourself to build credibility.


• Be professional: In your first face to face meeting, have a presentation that covers the main points of your project, explain how your project might provide the site something in return, and dress conservatively (I wore my best suit). We have even signed an agreement which spelled out what was to be provided by both parties, including hardware, software, and time. Now that we have been working together for a while, the relationship has become less formal, but initially I believe the formality gave them confidence that they would not be creating problems for themselves.


• Don't get frustrated: The site I am working with handled several major tasks while I was waiting for them to provide me with a single host and a single connection to the monitoring port of a switch. To me, it wasn't asking for much, but now that I have been there, I can see that for them it was quite a bit of time and planning.


• Be gracious: I have found opportunities to thank the people I am working with, including management and sysadmins, at least twice a month. I have also pointed out how well run their operation is (and it is, so I'm not just brown-nosing).
  

What they provided for me was a 733MHz CPU with 2Gig of RAM, a 100Mbps network interface, and a 16Gig hard disk. The most important issue for me was the memory, so 2Gig of RAM is fantastic. As far as everything else goes, I would rather test on moderate equipment and make my code more efficient to get adequate performance than have the platform hide problems.

Of course, the least of my worries was about problems being hidden. In the first couple of weeks, the IDS failed within less than 1 hour. First it was buffer space issues, next it was bugs, then it was buffer space issues again. But after a month, I had it running long enough to actually detect a few incidents. Then, over the next several months I cleaned up formatting issues, improved the user interface, and fixed more IDS bugs.

In the meantime, I have been able to give the site some feedback on their environment. I have not created a lot of rules, but there is a fair amount of variety to those that are in use, and some were in response to information that they wanted to collect. The most interesting ones, for all of us, have been:

• Non-http traffic on port 80: This reported very few hits, but the ones it did report gave them enough info to correct the use of a couple of applications


• Brute force FTP logins: This just gave them more detail than what they were already seeing in logs, but at least it showed that none of the attempts were successful


• Activity at unusual times: By monitoring Email servers between midnight and 5:00 am, we have seen a few cases of spam from site hosts, and some other activity that led to them discuss policy


• Invalid TCP options: We are working on this one, stay tuned


• Rules for specific exploits: Between the low number of these and the sysadmins' efforts to harden their site, there have not been any serious (or from my perspective, spectacular) hits on these, which from their perspective is good news
  

Overall, I count this experience as a huge success. And the best news is that a couple of months ago, I finally fixed the main buffering issue. So while there are still some bugs, the system has been stable enough to run for days in a row (as opposed to hours). And now it is reassembling and analyzing as many as 35,000 simultaneous TCP sessions.

I never would have reached this point without the help of the people at the test site. So once again, I want to say, "Thanks."

Later . . . Jim

Oh bother!

I have spent the past couple of weeks preparing and presenting a demo for my local Linux user group and working on a user manual, not to mention the routine things like checking on the pilot project and mowing the grass. During this time, I have found a few bugs in some of the download files and replaced the ones with errors. None of them are catastrophic, but they do affect some functionality.

This makes me wish for a full-time QA team. When I was working for a company that sold a TCP/IP stack for IBM mainframes, we had one and I came to appreciate what they did, even though they made me rework several fixes. The time and effort it takes to create automated tests, or worse run them manually, is huge.

Realeyes is fairly complex, in that it contains:

• Source packages


• Debian packages


• Configuration scripts for both packages


• C programs


• A database with SQL scripts for building the schema


• A database interface written in Java


• A user interface written in Java


• Configuration file definition forms in the user interface

Thanks to the pilot project, I get to see most of this in use regularly. And the changes I am making these days are mostly in response to issues that come up there. But it would be a fine thing indeed to be able to have an actual QA team.

Later . . . Jim