Jun 22

Fun Code Friday – SQL Binary Generator

I was daydreaming about some SQL code the other day, and wrote a note to myself to investigate.

I’ve always been interested in writing loops to output sequential data. I don’t know why, I just do.

So, here is some SQL that will generate some binary.

DECLARE @b TABLE (c VARCHAR(1))
INSERT INTO @b VALUES (0)
INSERT INTO @b VALUES (1)

SELECT	b1.c + b2.c + b3.c + b4.c + b5.c + b6.c + b7.c + b8.c
FROM	@b b1, @b b2, @b b3, @b b4, @b b5, @b b6, @b b7, @b b8
ORDER BY 1
Jun 20

Invalid Verizon Wireless Bill – Phishing Scam

Hello,

You are no doubtedly here because you clicked a link in an email in regards to something about your verizon bill.

This was a phishing attempt, and was thwarted (hopefully) before too many people got caught.

At about 12:45pm EST, I received a phone call from the Titan America Help Desk, informing me of the offense. Unfortunately, that only gave me 15 minutes to find the issue and thwart it, as I had an important client meeting to get to.

I was not able to find the problem within this time frame, as it wasn’t visible in the WordPress utility. During the meeting, however, I thought of another way this could be occurring. Once the meeting was over, I verified that it was indeed the cause, and removed access to it.

Somehow, and I’ll be investigating how for a little bit, 2 files were created on my server in the root directory. Both of them were identical. The root file was “wless.html” (how you got here), and the other was “zion.html”. The zion file had zero access permissions, which leads me to believe it’s the master copy.

The HTML file contained a basic (ugly) layout, but quite an advanced javascript algorithm that loaded an iframe element into the code. This, no doubt, would have made it appear that I was behind the phishing attack.

Let me assure you that I was not.

I have reported back to the representative who contacted me about the issue, and hopefully he was able to stop it at the root.

You’re safe for the moment, but please use caution when dealing with sensitive information.

You can read about how to detect and prevent phishing scams in the future.

So, take a deep breath and hold it… hold it… keep holding it… and exhale. You can relax.

Stay cool in this warm weather. And if you’re in need of some cool shades, check out these really cool and popular Wayfarer Sunglasses by Ray Ban.

Ray Ban Wayfarer Sunglasses

Jun 04

SQL Server Case Sensitive Queries

Recently, I’ve been tasked to create a data mask function.

They want:

  • A number will be a number
  • An upper case letter will be upper case
  • A lower case letter will be lower case
  • Punctuation and symbols will remain the same

An example would be:

123-00-6789 would become 456-88-4321
Mark would become Trxz

To enhance obfuscation, it will only be used one-way, and a 1:1 character swap is not necessary. Therefore, an uppercase A could be an upper case Z and an upper case X in the same sentence.

Thanks to this article at Novick Software, I was able to come up with the following example for future use.


--Create a table variable
DECLARE @Tbl TABLE (Name VARCHAR(100))

--Insert same name with different case
INSERT INTO @Tbl VALUES ('mark')
INSERT INTO @Tbl VALUES ('Mark')

–Gets the 1st (case-insensitive) result
SELECT DISTINCT Name FROM @Tbl

–Gets case-sensitive distinct results
SELECT DISTINCT Name COLLATE sql_latin1_general_cp1_cs_as FROM @Tbl

–Gets the distinct count (case-insensitive)
SELECT COUNT(DISTINCT Name) FROM @Tbl

–Gets the distinct count (case-sensitive)
SELECT COUNT(DISTINCT NAME COLLATE sql_latin1_general_cp1_cs_as) FROM @Tbl