Category: Security

David Fowler has an interesting article on a simple cryptographic algorithm in SQL Server:

As this post was prompted by my post on bitwise logic, we’re going to base our algorithm around the XOR cypher.  Basically this cypher works by taking a key which for simplicity sake will be a single byte and XOR-ing that against the message (or plain text) that we want to encrypt.

Let’s look at an example of how this is going to work.

Let’s say that we want to encrypt the plain text ‘SQLUndercover’.  How are we going to do this?  Firstly we need to remember that all text characters are represented by a single byte as an ASCII code.  ‘SQLUndercover’ is represented by the following set of ASCII codes, 83 81 76 85 110 100 101 114 99 111 118 101 114.

I definitely recommend reading this article for two reasons:  first, because it’s interesting; second, because it shows how easy it is to break amateur crypto.  If you feel the need to roll your own cryptographic algorithm, follow my Official Crypto Flow Chart (patent pending):  Do you have a PhD in mathematics with a specialty in cryptanalysis?  If not, don’t do roll your own crypto; if so, maybe do it but even then probably not.

Duncan Greaves explains Privacy Impact Assessments as part of the GDPR:

The processes and practices implemented by organisations should reflect the methodology of using a Privacy by Design approach to business systems. Undertaking a PIA/DPIA is not a mandatory part of the GDPR, but in doing so, organisations can show that they are compliant with the Act.

Conducting a PIA is designed to accomplish three main goals:

• Ensuring conformance with applicable legal, regulatory, and policy requirements for privacy.

• Determining the risks and effects.

• Evaluating protections and alternative processes to mitigate potential privacy risks.

Worth reading if you’re based in Europe or do business with European customers.

Wayne Sheffield has a post explaining what SQL injection is and discussing how to stop it:

Me: Umm, boss… Does this report allow users to enter in search criteria?

Boss: But of course!

Me: Well, I really hate to tell you this, but we have a SQL Injection problem.

And after a bit of back and forth where the developers were insisting that no way was there a SQL Injection problem, I sat down with the dev team lead and the boss and proved it to them. We created a dummy table in the database, went to the report criteria form, and I dropped the table.

Wayne: +1000

Development Team: -1000

Injection attacks are still the most common form of attack out there.  Sadly.

Arun Sirpal points out an oddity in sys.databases:

If you query sys.databases, such as:

SELECT is_encrypted,name,user_access_desc FROM sys.databases WHERE database_id = 2 OR database_id = 7

It “might” throw you off. Would you not expect to see is_encrypted set to 1 for TempDB?

I thought I remembered earlier editions of SQL Server showing is_encrypted = 1 for tempdb, and I definitely remember 2016 showing 0 even when the database is encrypted.