Freeform Friday 7

Simply write about the first thing that comes to your mind …. NOW!

I have two new favorite commands in SQL Server, especially given the insane digging I’ve done in a huge database with oddly named columns and tables:

EXEC sp_fkeys '[Table_Name]'

This gem will give you all of the foreign keys for a given table, and what table they are referencing. Helpful when building things out or tracking them down.

Update: Looking for the opposite? Looking for the tables/keys that are referencing the table you’re inspecting, use this syntax

EXEC sp_fkeys @fktable_name='[Table_Name]'

That will flip the data around.

SELECT * FROM information_schema.COLUMNS WHERE column_name like '%[Column_Name]%'

Wondering where in the world the table that said column in a side referenced documentation / form is actually located at, this will find it for you. It also includes views.

Any other handy SQL Server tricks to share?  Continue reading “Freeform Friday 7”

Advertisement

Quick Tip: Make SQL scripts generate SQL scripts for you.

Quick Tip

So the synopsis is this: sometimes you have a TON of rows you need to insert into a database from a spreadsheet, but you need to do some custom massaging of the data to generate the statements. While a few dozen rows is okay, try doing this with 10,000+ rows! The nice thing is that you can make the database do the work for you. Continue reading “Quick Tip: Make SQL scripts generate SQL scripts for you.”

Soup to Nuts - The Complete Package

Soup to Nuts: Generating a Time Interval Based Count Report in T-SQL

Soup to Nuts - The Complete Package

At the beginning of the week I was tasked with generating a count based report of various records. The tricky part to this was that the report needed to be based over a certain period of time and in a 30 minute time interval. In other words, they wanted to see a report that looked something like.

Time          Count
0:00:00         1
0:30:00         1
1:00:00         5
1:30:00         2

…and so on. Continue reading “Soup to Nuts: Generating a Time Interval Based Count Report in T-SQL”

Sorting Latitude/Longitude Positions by Distance in SQL

Map Points

My recent project involved a database with lots of “location points” that were based on latitude and longitude. A user would be able to specify some pertinent information (such as address, state, or even county) and the location points would be displayed for the user. The results from this search came back in a relatively random order, but what we needed was  a way to sort our search results in relation to the center point in our map “view box”. Continue reading “Sorting Latitude/Longitude Positions by Distance in SQL”

Converting, Formatting, and Padding Values in T-SQL

This is another hat tip (HT) to my fellow coffee drinker co-worker Anne for some hard work in figuring out some tricky code.

Anne is working on a data conversion project between two systems. She needs to take some data and convert it to decimal format, round the value to remove the decimal places, and then pad it to the left to 9 places, to have leading 0s in front of the data.

While this task isn’t difficult, this is a little tricky, especially considering you have to convert the values to various types in order to to parse into other data types and do the padding. PL/SQL has a nice LPAD function for padding strings that T-SQL is currently lacking.

After a bit of testing, tweaking, and brain mungling (a technical term) the following solution was found:

SELECT CAST(REPLACE(STR(CAST(ROUND(TableName.ColumnToParse, 2, 0) AS DECIMAL(9,0)), 9), ' ' , '0') AS CHAR(9))

See, LOTS of casting and wrapping and converting, but it works, and I’m keeping dibs on this for the next time I have to deal with something similar.

Finding and Replacing Text in SQL Server

While cleaning up some WordPress hacks last week, I followed some documentation and noticed that MySQL has a nice function for doing a search and replace with text in a given column. All I had to do was execute the following statement:

UPDATE content set post_content = REPLACE(post_content, 'nasty hackish code', ' ');

This worked out great. I could easily update many rows, or a single one.

The other day at work, I ran into a situation where I needed to update some bad HTML tags that were sitting in a content field in one of our applications. This database was SQL Server, so I checked the documentation, and they had a REPLACE command as well. The only problem was that it didn’t work in a similar manner, it only worked with actual variables or string content.

After digging around, I discovered you can accomplish the same effect by simply wrapping up your update statement around a retrival statement that replaces the text at the same time. This is how I got mine to work:

UPDATE content
   SET content_html =
                     (
SELECT REPLACE
                             (
                              SUBSTRING(content.content_html, 1, DATALENGTH(content.content_html)), 'nbsp' , ' '
                             ) AS UpdatedContent
                        FROM content
                       WHERE content_id = 42986
                     )
WHERE content_id = 42986

Since the replace statement requires a string, we have to use the SUBSTRING command to pull out all of the text of the column in question. Its a little crazy, but hey it works.

SQL Gurus, if you have a better alternative, please share!

Removing rows with duplicate Ids in SQL Server

Yes, you say it can’t be done. You have a table where the Id field is set to be unique and the primary key, yet SOMEHOW there are two rows in a content table that have the same Id. When you try to delete one of the rows, it says it can’t delete the row because there are duplicate unique keys. [Then why did you insert the extra one in the first place!!!!]

<Queue spontaneous head pounding against the wall />

On an interesting side note however [Yes, I tangent a lot], it appears the culprit was SQL Server Management Studio and its ability to allow you to paste data from Excel into the data view, thus inserting extra rows. Maybe it is temporarily disabling all constraints on the table behind the hood, I’m not sure, and I still need to duplicate it, but we saw an instance of this once before that seemed to have the same path.

I’m aware of the internal fields, such as row_number, that are available for queries, but I wasn’t sure how to access it in this case. After passing word on to our almighty DBA, he did some research and came back with a really simple SQL statment that does the job for us:

SET ROWCOUNT 1

DELETE FROM dbo.content WHERE content_id = 40598

Problem solved and we cleaned out a good handful of records in this manner. What the first line of the script does is restrict any operations to stop after the first row is processed. Our second statement deletes a record (which you would expect), but since our rowcount statement halts the gathering of any additional records for processing after the first one, the constraint isn’t triggered.

If you’re stuck in a duplicate Id bind, let this one do the trick for you.

Using an INNER JOIN on a SQL UPDATE Statement

I made a slight blunder in my application that crept up yesterday at work. We have an internal task tracking application and I’ve been writing some supplementary web pages to interface with the application.

It was discovered that in my code, I was incorrectly assigning the department Id of the user when inserting a new time track record into the database. I should have been inserting the Id of the requesting department instead, so that the proper time would be billed.

There were about 1500 records that had been affected over a period of a month or so. While this wasn’t drastic, it was a sufficiently large enough count that it would take me too long to manually update all the records. Some of the time tracking records did have the proper department assignment, since the main web page was also being used to enter time. Finding the bad records in question was simple enough to do:

SELECT dbo.TimeTrack.Id AS TimeId,
       dbo.Task.Id As TaskId,
       dbo.TimeTrack.IdDepartment As TimeDept,
       dbo.Task.IdDepartment AS TaskDept
  FROM dbo.TimeTrack
       INNER JOIN dbo.Task ON dbo.TimeTrack.IdTask = dbo.Task.ID
 WHERE dbo.TimeTrack.IdDepartment <> dbo.Task.IdDepartment

However, updating the records in a SQL statement was a little bit trickier, at least to this non Jedi/Ninja SQL guy. After a little bit of tweaking and testing, I found that I could use an INNER JOIN as part of an UDPATE statement, you just had to “phrase” the INNER JOIN through the FROM clause of the statement. A generic form of this statement looks like this:

UPDATE TableA
   SET TableA.Column = TableB.Column
  FROM TableA
       INNER JOIN TableB ON TableA.IdForeign = TableB.Id

In my case, with the additional WHERE clause to find the mismatching the departments, I was able to update the records quickly with this query:

UPDATE dbo.TimeTrack
   SET dbo.TimeTrack.IdDepartment = dbo.Task.IdDepartment
  FROM dbo.TimeTrack
       INNER JOIN dbo.Task ON dbo.TimeTrack.IdTask = dbo.Task.Id
 WHERE dbo.Task.IdDepartment <> dbo.TimeTrack.IdDepartment

Just another blade to add to your ninja skills, force trick to add to your Jedi skills, or tool to add to your trunk if you don’t have it already. Enjoy!

Tracking down WordPress Hacks

I’ve been volunteering my time over the last month or so helping to debug some spam and performance problems at the Evangelical Outpost blog. The blog is running WordPress, and while I’m no WordPress guru in any regard, I’ve spent enough time on the web that I could lend my expertise to these issues.

The spam issue wound up being a rather nasty exploit that I believe was traced to some file permission hacks in older versions of WordPress. What made them really difficult was that the spam was only showing up in the Google cache and search results, effectively driving the sites search ratings down, but not degrading the post content an active viewer would have. Since the average person would only see the live good content, the issue was unnoticed for a while and more difficult to track down. The full details of this is a post for another day.

However, one thing I did want to share was the process of elimination/cleansing I went through in order to find the issues. After the initial attack I believe there were a few other issues that compounded the problem at multiple levels (application, files, database). Hopefully this list will give you a nice process to go through if you’ve recently had your own WordPress hack and need to flush it out:

1. Go into the admin section and do a search on all comments for the following keywords (in separate searches): “tramadol” (this was the spam vendor ailing us), “javascript”, “hover”, “style=”. Those are typically some of the injection vectors that get used in the comments themselves.

2. Go into the theme editor and browse through all of the files in all of the themes you have installed on your WordPress install, even if inactive. Look for “base64” in there and also look for some blatant links to the spam sites. Sometimes the hackers aren’t picky, if they have file access, they’ll just inject it straight away with none of the encoding shenanigans. Look for other PHP method calls that just don’t seem right for your template. Its hard to make a specific list, but sometimes you’ll get a “code smell” of something that doesn’t seem like it should be happening and is worth investigation.

3. Log into the file manager on your hosted site. Look for any .txt files in the root of your directory and the root of your WordPress install. This is how I found a massive cached dump file of some old E-mails and posts, which had the tramadol keyword in it, and I suspect it was getting added to the cache, or skewing the search results, since there was a generic robots.txt file.

4. Flush out your wp-cache folder, or at least look through them for the spam keywords. Whatever caching program you use is probably rebuilding this folder, so I’m not as worried about this, but flushing this out is helpful. That’s why I really like the W3 Total Cache plug-in I installed for EO.

5. Finally, go directly into the database manager and search through the comments at this level. The reason for this that a lot of the spam injection happens by putting HTML code into the database so that when it comes out, you don’t see it on the page unless you go into the source code, and sometimes that is even filtered down. Use the following query to search your posts and comments for the common attack vectors:

SELECT * FROM wp_posts WHERE post_content LIKE '%<iframe%'
UNION
SELECT * FROM wp_posts WHERE post_content LIKE '%<noscript%'
UNION
SELECT * FROM wp_posts WHERE post_content LIKE '%display:%'
UNION
SELECT * FROM wp_posts WHERE post_content LIKE '%<?%'
UNION
SELECT * FROM wp_posts WHERE post_content LIKE '%<?php%'

SELECT * FROM wp_comments WHERE comment_content LIKE '%<iframe%'
UNION
SELECT * FROM wp_comments WHERE comment_content LIKE '%<noscript%'
UNION
SELECT * FROM wp_comments WHERE comment_content LIKE '%display:%'
UNION
SELECT * FROM wp_comments WHERE comment_content LIKE '%<?%'
UNION
SELECT * FROM wp_comments WHERE comment_content LIKE '%<?php%'

And that covers a pretty wide range of ways your site can get hacked. Last this week I’ll list a few WordPress plug-ins I’ve particularly useful for the EO site, as well as a diagnosis of the particular Google Cache hack, just in case you run into similar issues.