SQL Encrypted Fields

May 25, 2017 / joe

This document shows step-by-step instructions for setting up encrypted fields in a SQL database table.

/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50))
GO
INSERT INTO PasswordTable (SEQ, AdminPassword)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password-1234'
GO

/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'Password-1234'
GO

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY PasswordTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE PasswordTable
ADD EncryptAdminPassword VARBINARY(256)
GO

/* Update binary column with encrypted data created by certificate & key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE PasswordTable
SET EncryptAdminPassword = ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),AdminPassword)
GO

/* DROP original column which was encrypted to protect the data */
USE EncryptTest
GO
ALTER TABLE PasswordTable
DROP COLUMN AdminPassword
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Decrypt the data of the AdminPassword */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT SEQ, CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptAdminPassword)) AS DecryptAdminPassword, EncryptAdminPassword, RegStamp, DateStamp
FROM PasswordTable
GO

/* This is an example of adding a record with an encrypted field */
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
INSERT INTO PasswordTable
(EncryptAdminPassword, RegStamp, DateStamp)
VALUES(ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),'DogBreath91'),'test 2014.01','2014-01-15')
GO

The syntax for truncating a table in SQLite differs from that of SQL or mySQL. Truncation means that all records are deleted but the table structure is left in place. In addition, the record counter is reset to zero.

To truncate a table in SQLite, the following two commands are used:

Delete from TableName;
DELETE FROM SQLITE_SEQUENCE WHERE name='TableName';

SQL Summing daily totals

May 18, 2017 / joe

This example illustrates how to sum values collected daily into monthly categories.

SELECT DISTINCT
CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2) AS PERIOD,
  SUM(Steps) AS TotalSteps,
  SUM(Floors) AS TotalFloors,
  SUM(Distance_mi) AS TotalMiles,
  SUM(Activity) AS ActiveMinutes,
 SUM(Calories) AS TotalCalories
 FROM  dbo.FitBit
 GROUP BY CONVERT(varchar(4), YEAR(Date)) + '-' + RIGHT('00' + CAST(DATEPART(mm, Date) AS varchar(2)), 2)

Replacing a string in each user’s profile

May 18, 2017 / joe

This routine replaces a string in the text file user.js. The file is located in each user’s profile which also contains a random string as part of the path name.

Imports System.Net
Imports System.IO

Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 Dim strPath As String = "c:\users"
 Dim arrFolders() As String = System.IO.Directory.GetDirectories(strPath)
 Dim MozPath As String

'----------------------------------------------------------------
 ' get list of profiles
 '----------------------------------------------------------------
 For Each arrFolder As String In arrFolders
 MozPath = arrFolder & "\AppData\Roaming\Mozilla\Firefox\Profiles\"

If My.Computer.FileSystem.DirectoryExists(MozPath) = True Then
 Dim arrGUIDpath() As String = System.IO.Directory.GetDirectories(MozPath)

For Each arrGUIDfolder As String In arrGUIDpath
 If My.Computer.FileSystem.FileExists(arrGUIDfolder & "\user.js") = True Then
 Console.WriteLine("notepad " & arrGUIDfolder & "\user.js")

'----------------------------------------------------------------
 ' 1. rename the user.js file
 ' 2. replace the proxy defintion string tinto user.js
 '----------------------------------------------------------------
 Try
 My.Computer.FileSystem.DeleteFile(arrGUIDfolder & "\user2.js")
 Catch ex As Exception
 Application.DoEvents()
 End Try

My.Computer.FileSystem.CopyFile(arrGUIDfolder & "\user.js", arrGUIDfolder & "\user2.js")

My.Computer.FileSystem.WriteAllText(arrGUIDfolder & "\user.js", _
 My.Computer.FileSystem.ReadAllText(arrGUIDfolder & "\user2.js").Replace("user_pref(""network.proxy.type"", 4);", _
 "user_pref(""network.proxy.type"", 0);"), False)
 End If

'----------------------------------------------------------------
 ' clean up to avoid confusion in the future
 '----------------------------------------------------------------
 Application.DoEvents()

Try
 My.Computer.FileSystem.DeleteFile(arrGUIDfolder & "\user2.js")
 Catch ex As Exception
 Application.DoEvents()
 End Try
 Next
 End If

Next
 End
 End Sub

End Class

SQL Finding primary keys and missing primary keys

March 20, 2017 / joe

Tables with Primary Keys

SELECT c.name, b.name, a.name
 FROM sys.key_constraints a
 INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID
 INNER JOIN sys.schemas c ON a.schema_id = c.schema_id
 WHERE a.type = 'PK'

Tables Without Primary Keys

SELECT c.name, b.name
 FROM sys.tables b
 INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
 WHERE b.type = 'U'
 AND NOT EXISTS
 (SELECT a.name
 FROM sys.key_constraints a
 WHERE a.parent_object_id = b.OBJECT_ID
 AND a.schema_id = c.schema_id
 AND a.type = 'PK' )

Meta