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 we get into the facts, some definitions:
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
- An unhappy user deliberately executes the code (probably before
being laid off).
- A user will execute some code of uncertain origin containing
- An intruder gained access to the database and executed the
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.
Or changes in size:
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
3) Compare tables from backups with new ones if they seem to have
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
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.
DECLARE @i int, @j int, @current_proc varchar(255),@current_text
DECLARE _Cursor CURSOR FORSELECT o.name,c.textFROM sysobjects
o INNER JOIN syscomments c ON c.id =o.id
WHERE o.type='p' and o.category=0 and encrypted=0
OPEN _CursorFETCH NEXT FROM _Cursor
INTO @current_proc, @current_text
WHILE @@FETCH_STATUS = 0
lblAgain: set @i=CHARINDEX('exec',@current_text,@i)
print ' '+SUBSTRING(@current_text, @i, @j)
FETCH NEXT FROM _Cursor INTO @current_proc,@current_text
CLOSE _CursorDEALLOCATE _Cursor