Fog Creek Software
Discussion Board

Client-Server Software Vulnerability

How do you smart people handle & safegaurd against the following software vulnerabilities:

1. The plain-text database communication between the client (software) and server (remote database) can be logged/traced using a eavesdrop proxy tool.

2. The plain-text SQL queries (even in the binary executable) can be overwritten with malicious code using any Hex Editor.

Tuesday, December 23, 2003

don't send query over the internet

the artist formerly known as prince
Tuesday, December 23, 2003

1. Use a communications protocol that uses sealed (encrypted) traffic. You can use SSL with SQL Server, for instance.

2. Don't trust your clients. On the server side, assume all input is malicious and validate it appropriately. Hex editors are the least of your worries - if someone can hex edit your client executable and do bad things, they can probably whip up their own evil client app as well to talk to your server and do even more bad stuff. Start with well thought-out input validation and good authentication / authorization and you should be on the right path.

Mike Treit
Tuesday, December 23, 2003

Another day for Web Services to come to rescue!

Tuesday, December 23, 2003

Related question (I don't program database code for a living so excuse my ignorance):

How does one typically manage passwords on an RDBMS?

e.g. I have a timesheet application.  Now, does every employee in the company get a login on the RDBMS, which the timesheet app simply passes through?  If so, then any employee can arbitrarily diddle the database.

Alternatively, if there is only one account for the timesheet app, and the app maintains a database table with username/password and does the authentication manually, then the app still needs a login to the RDBMS, which must perforce be contained within the app and therefore available to the client.

How does one maintain security in this situation?  Other than by having two separate networks, with the app running on the one host connected to both networks, with the DB in the back end and the web browser in the front.

This is the one weakness I found with RDBMS technology back in 1990 and still unsolved to this day...

David Jones
Tuesday, December 23, 2003

Which RDBMS are you talking about? They all have different security models.

Stephen Jones
Tuesday, December 23, 2003

Any.  Doesn't matter.

Oracle, Informix and Sybase (I'm dated from 1990, I know) all require username and password.  So does MySQL.

So, if you make an app, you need to provide a login to the DB.  Somehow.


David Jones
Tuesday, December 23, 2003

Sure; what's wrong with that?  How else would you get access to the DBMS system?

Tuesday, December 23, 2003

In the world of DB2 (and Oracle I think), you can use static SQL. It solves this problem of direct database access and random data manipulation.

The idea is:
-At compilation you create both an executable and a SQL package (that contains all of the SQL you want to allow execution of).
-The executable has burned into it the SQL package identifier (but not the actual SQL).
-You place/bind the SQL package into the DBMS and grant table authority rights to the package.
-You create user IDs/groups that have authority rights to that particular packages.

Now when the user fires up the executable, they'll be prompted for their DB2 user name and password. The burned in code in the executable will load the package and use that user's authority to execute statements/functions inside of that package. The user does not have direct access to the tables so that can't just fire up Access, link the tables, and have a field day (access denied).

SQL Server doesn't really have a model like this.

Mark Smith
Tuesday, December 23, 2003


Sounds a lot like stored procedures in SQL Server/Sybase ASE.  You can do the same thing with those, although it doesn't sound quite as automatic.

Tuesday, December 23, 2003

Aside from encryption and such, you don't send anything like a query directly to the database, you send it to some doorkeeper server that checks out 'who' you are (ip address, session id, state of underwear, note from your mother, etc, etc), converts your plea for data (which doesn't look at all like a query but something like "GETProduct "search string" ) into a call into the database which hopefully has stored queries on it so you don't have any hard wired queries going across the wire.

The database serves up the data in some nice neat and without context steaming pile of XML which the doorkeeper sends on back as encrypted HTTP.

The database server is unlikely to be the same piece of hardware as the doorkeeper, and there may be many copies of the doorkeeper running (there might be many copies of the database as well, but that's by the by).

Simon Lucy
Tuesday, December 23, 2003

> 1. The plain-text database communication between the client (software) and server (remote database) can be logged/traced using a eavesdrop proxy tool.

There's a bunch of ways to mitigate that threat.  Encryption is one way, but there are other techniques as well.

The first thing that comes to my mind is "why are you sending information at all?"  I assume from your question that you're sending database queries in SQL.  Is it really the case that the client can send any query, and the server will handle it?  Or is it the case that there is a small number of queries that you're intending to handle?  In the latter case, don't send the queries at all.  Write the queries as stored procedures in the database, and send magic numbers -- "1" means "fetch me the product list", "2" means fetch me the price list, whatever.  Don't send the query over the internet, send enough information for the server to deduce the query.

Once you do that, lots of problems start to go away.  Now hostile clients cannot run queries you don't want them to run.  Stored procedures mean that SQL injection attacks stop working. 

Also, you should make sure that the server process is running with only those permissions required to do by-design queries.  Too many people give their server process admin access to the DBMS, when all the server needs to do is read a couple of tables.  Make a DB account that only has the rights to read the tables you want the client to read, and make the server process use that account.  That way, if you do end up with a hostile client, the hostile client cannot possibly get write access to your database.

In short, follow good security principles:

* implement defense in depth: require attackers to defeat _many_ security systems to get at sensitive data

* localize sensitive data to one place

* don't trust the client, particularly when injecting queries

* use least privilege: do not grant more privileges to any system than are necessary for it to do its job

Eric Lippert
Tuesday, December 23, 2003

As for security models and RDBMS.

Don't confuse users of applications with individual users of the database.  For the most part the application user is a class of database user and their password and account information is held within the application database and resolves into a class of database user with an account and password.

The application's security system logs in the application user and behind the scenes (pay no attention to the man behind the curtain), logs that user as a database user with the known internal password.  For security's sake one would hope that internal password doesn't fly across any wire though in practice I've found people haven't done that.

If the RDBMS doesn't like the same database user logged in multiple times then you don't thread that but cluster the processes logging and share the session of a particular class of user.  Alternatively if its an extremely licence picky RDBMS you create aliases to map application user to database user.

But the rule remains constant, you never give out a real database id and password you always separate the application security from the database security model, they obviously intersect but that intersection has to stay internal.

Simon Lucy
Tuesday, December 23, 2003

What type of applications require life on the client but communicate with a DBMS server across the internet?

Tuesday, December 23, 2003

What kind of applications wouldn't want that that hold data remotely that has to be accessible, possibly updated and manipulated remotely?

Simon Lucy
Tuesday, December 23, 2003

I'm just trying to figure out what applications live on a client (e.g. non-web apps)
require non-trusted networks for authentication/data storage

I'm looking for examples of commercial products.

Tuesday, December 23, 2003

On Windows (2000 and SQL Server 2000) you can delegate identity flow to the OS (at each of the stages the user is identified through his windows account). Problem there is that for high performance apps you can not do connection pooling AFAIK.

Just me (Sir to you)
Tuesday, December 23, 2003


I have a situation like this at a client site like that with outsourced data entry: they have a full blown Windows application (custom data entry software) that does look ups in a database (key in last 4 digits of account number to auto-populate fields) and allows them to insert data entered data into a table. Making it a full blown Windows application is a neccessity because it is a richer user experience than could be delivered otherwise (speed, there's some client side integration with our imaging viewer, etc...).

The setup looks like this:
-Outside data entry firm workstations connect to my client's network over the internet on a VPN.
-My client's hosting VPN is configured to allow these particular workstations access to very few IP addresses and port ranges (basically, just the DB2 server and LiquidDocument ( ) imaging servers).
-Even though a VPN is involved, we don't consider these workstations "trusted" since they are a third-party firm. We use static SQL to control access to the database; keeping an outside firm from being able to poke around was a very important requirement (i.e. want the auto populate feature but don't want them to be able to see the whole table).

Mark Smith
Tuesday, December 23, 2003

As far as the username and password question several posts above goes...

It sounds like you thinks you need to map usernames and passwords entered by the users to actual SQL database usernames and passwords.

If that's what you're thinking, don't do that. Instead manage the users yourself with your own users table of some kind.

Tuesday, December 23, 2003

I've just gone through a large security exercise with my C-S app.

Although it's a Windows based app the C and S domains may not have trust, therefore Windows authentication is out.

I'm not so interested in encryption of the traffic, what I want to ensure is that only genuine users can update the system.

My priorities in terms of security are: passwords must never be sent in clear text across the network and it must not be possible to replay security information.

I do not want the overhead of asymmetric security with public and private keys (cos then you need a key authority).

I'm happy to use triple DES or AES as long as any information that can be obtained through standard cracking will not be useful after a couple of days (because it'll generally take longer than that to break them).

So.. the client makes a logon request to the server (passing userid)

The server creates a random session id (guid), a new symmetric cryptographic key (the session key) and encrypts both plus the current date/time with a key derived from the users password and sends it back to the client. It also puts an entry in an in-storage "Session" object, keyed by the session id, with a "non-authenticated" status holding the session key, the user's credentials and the date/time sent to the client

The client decrypts the session id, session key and the time/date with the user's password and then encrypts the time/date with the session key and sends back to the server, passing the session id in clear

The server uses the session id to look up the "session" object. If it is still in a "non-authenticated" status and the session key decrypts the time/date correctly then it marks the "session" object as authenticated.

Now every time the client wants to go do something it passes the session id and time/date in clear text and encrypts the time/date with the session key. If the server can decrypt the date time successfully then the user must be ok. It then checks to make sure that the date/time passed is greater than the date/time previously sent (as stored in the "session" object). This prevents against replay.

There are things that this doesn't protect against (e.g. interception and amending the original package) although this could be improved by including a check-sum in the encrypted package. By understanding what the encryption protocol is and monitoring all client-server traffic and a couple of lengthy decryptions then the user's password could eventually be derived.... so of course you might want to ensure the user changes it every so often. At this point I think this is enough! It meets my criteria.

All security is risk assessment (in fact most security is compromised by human engineering or dictionary password attacks rather than sophisticated hacking) but I think you need to make security stronger than some of the obfuscation techniques mentioned in this thread.

Of the technical attacks sniffing is the easiest. And sniffing will reveal clear text passwords (don't do it!) and will easily reveal information that can be attempted for replay.

By the way this stuff is piss-easy using .NET - all the building blocks are there.

Tuesday, December 23, 2003

*  Recent Topics

*  Fog Creek Home