Protecting against TSQL virii, worms and time bombs

Yes, the first virus made in TSQL has been created! But even

more dangerous, worms can be made applying similar but simpler

techniques. What could be worse than that? Time bombs hidden somewhere

in the code, waiting…

Screenshots of the TSQL virus in action

Before

After

Before we get into the facts, some definitions:

From cybercrimes.net

Definition of virus

"A computer virus is a self-replicating program that invades

and attaches itself to computer programs. Virii can interfere

with the operations of their host program or alter operations

of their host computer."

Definition of worm

"A worm is a program whose primary task is to move copies

of itself between computers connected by network. Though worms

do not try to cause damage to a computer, by causing copies of

itself to be made a worm can disrupt the operation of computers

and computer networks."

Definition of time bomb

"A time or logic bomb is a specific program feature. A

program with a time bomb will "explode" upon the occurrence

of a certain event - often the occurrence of a predetermined date.

The explosion can be anything from a display of messages on the

computer screen to the complete wipe of the computer's system."

The most complex of those three entities is the virus, which

requires intrusion, execution and replication of its code. The

intrusion is theoretically impossible in a SQL Server database

properly secured. As TSQL has no "low level" features,

port scanning and intrusion are not possible.

How can the virus infect a database?

It will have to be executed by a user. There are three possible

scenarios:

• An unhappy user deliberately executes the code (probably before

  being laid off).

• A user will execute some code of uncertain origin containing

  the virus.

• An intruder gained access to the database and executed the

  viral code.

TSQL virii are not a threat

This is very clear from the above scenarios. Scenario a) requires

more effort and is more likely to be detected than a time bomb.

It makes no sense to do an inside job that is so visible and complicated.

Scenario b) would be possible if the user had permissions to run

the code, knowing enough TSQL as to create a stored procedure

but not enough as to understand what the code really does. This

is very unlikely to happen. Scenario c) is obviously very far

from reality. An intruder would go through a lot of work to gain

access to the database and dropping some tables or the entire

database could be done immediately, why wait?

But there's more: TSQL data types used in stored procedures can't

go over 8 Kb. This is a great obstacle because the virus code

takes some room and so, the virus can only replicate to "small"

stored procedures, which makes it more visible and easier to detect.

TSQL worms are not a threat

A worm would face the same problems that a virus would but

it would be detected much faster and easily stopped. The "standard"

worm that replicates constantly would be simply coded as a stored

procedure that makes copies of itself with a random name. That

is easy to create and easy to remove. The best approach would

be a stored procedure that consumes resources by creating, filling

with data from system tables and then dropping lots of temporary

tables constantly. Why bother with a stored procedure that lays

among others but with code that wouldn't be so easy to disguise

when this code could be hidden in some other stored procedure?

Conclusion: time bombs are the most real and dangerous threat

The three scenarios for delivering a virus are perfectly possible

and quite easy and effective for a time bomb. Let's rewrite them

for this situation:

• An unhappy user deliberately hides the time bomb code in a

  section of a big stored procedure.

• A careless user copies code from an uncertain origin that has

  the time bomb hidden.

• An intruder was able to gain access to the database and, instead

  of causing an immediate destruction, the intruder decided to place

  a time bomb that would slowly and randomly corrupt data so that

  even the backups would be storing corrupted versions of the database.

This is the most dangerous and most realistic attack that I

can think of, after all bad coding can have an impact on the server

as negative as a sneaky and pernicious worm.

How to prevent TSQL virii, worms and time bombs' attacks

• No guest accounts and no accounts with null passwords.
• Make sure all user passwords are safe.
• User permissions and roles are very effective when used wisely.
• Check database objects regularly.
• Do not allow user passwords that are not encrypted.
• Check the system databases and objects not only for changes

  but also for the inclusion of new objects that could have dangerous

  code. A user could create a system stored procedure by knowing

  of an exploit before it was patched and obtaining the necessary

  rights with privilege escalation. Later the user could run it

  from any database. Another possibility would be to use the tempdb

  to store or execute malicious code.

Practical solutions for each of the above ideas

1) Carefully examine user permissions and roles. Restrict access

to everything but the necessary for the user to work. Look for

null or non encrypted passwords: SELECT [name], dbname, [password],CONVERT(VARBINARY(256),

password) FROM syslogins

2) Look for size, complexity and effectiveness of user passwords.

The best solution is to create random passwords for the users

but forcing the users to have long passwords is fine too. You

can use some free tools from SQLServerCentral.com to assist you.

3) Create a huge and unreadable SA password. Make sure that your

application is safe from SQL injection and be careful with granting

permissions and managing roles. Carefully attribute roles and

permissions. Even the "public role" can be dangerous.

4) Check stored procedures, UDF's and triggers for changes using

CRC32 or checksum.
http://qa.sqlservercentral.com/scripts/contributions/655.asp

Or changes in size:
http://qa.sqlservercentral.com/scripts/contributions/630.asp

5) See 1)

6) See 4)

How to detect data corruption

1) Use the Database Consistency Checker (dbcc).

2) Use TSQL BINARY_CHECKSUM or CHECKSUM. For multiple rows use

CHECKSUM_AGG.

3) Compare tables from backups with new ones if they seem to have

changed drastically.

4) Create views to verify that the numeric data is within "normal

parameters"; look for max and min values and sums of values

to find possibly corrupted data.

5) If the data is alphanumeric look for ASCII or UNICODE codes

that should not be there, empty records or oversized ones'.

6) Look for Nulls, zeros and repeated values.

7) Use CRC32 to validate historic data.

How to detect intrusion

1) Enable login auditing in SQL Server.

2) Use triggers to track changes.

3) Explore the transaction log with a commercial tool.

The softest spot of SQL Server can be Windows

Windows NT/2000 login always have access granted to SQL Server.

This is an extra security risk because breaking into Windows will

provide access to SQL Server and it might be easier (in very particular

situations) to crack Windows security than SQL Server's. Windows

2000 is safer than NT and even NT has very tight security if properly

installed and with the latest service packs and patches applied.

The problem arises from the fact that there might be one machine

with SQL Server but dozens of machines in the network can reach

it and the permissions are loose. It is easier to find out the

password for one out of dozens of machines than the one for the

machine with SQL Server. It is also possible to have one of the

users download or receive by email a Trojan or to run ActiveX

in a web page or any other technique to get access to a machine

and, from there, attack SQL Server. Win 9x/ME is very unlikely

to be used as a server but although it does not provide access

granted to SQL Server it can be hacked and a brute force attack,

sniffing or even key logging are al possible.

Avoid mixed mode and Windows 9x/ME

Usually that is not the case with most real life database implementations,

having a certain number of users, databases and database objects

related to each other in a way that requires careful management

in order to allow access without compromising security. Windows

authentication is the recommended security mode, not only because

of Windows architecture but also because login names and passwords

are not sent over the network. If the OS is not NT/2000 then mixed

mode has to be used but Windows 9x/ME have some many security

flaws that they should be avoided at all cost!

Do not be permissive with permissions

a) Each database has specific user accounts, do not let users

access databases they really have no need for.

b) Do not provide users with permissions and ownership of objects

in the database that they really have no need for.

c) Do not allow one login to have associated users in different

databases if several people share that login, unless absolutely

necessary. Splitting the group of users into smaller groups each

with a different login would be safer and easier to manage in

the future.

In case of doubt, search the code

It is very simple to create code to look for potentially dangerous

keywords in stored procedures, triggers and UDF's. The following

example code looks for "EXEC" in all stored procedures.

References

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

http://cybercrimes.net/98MSCCC/Article4/commentarysection403.html