Blog Archives

mssql aes function encrypt decrypt in microsoft sql

Simple symmetric encryption in MSSQL. Copied from MS references below:

USE AdventureWorks2012;
--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CreditCardID)));
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted 
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 , 
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

References:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

C# MySQL On Windows Named Pipes Development Mode

While outlining the scope for a new project I’ve considered the possibility of using MySQL for budgeting purposes with respect to the potential size and growth rate of the database.

I downloaded and ran through the latest setup for MySQL on Windows (x64 although I opted for the 32 bit binaries) and popped in the MySql Connector DLL (ADO not ODBC) into my project and began whipping up some code in no time.

However, this little trick seemed to be not well known after some google searching on why MySQL Workbench (great tool btw) can connect, but my app could not.

Being a security conscious developer, when installing SQL I selected developer mode and opted for a safer named pipes connection for local development vs socket network connection looping through my localhost via TCP.

If you also selected this option, you may have had trouble getting your app to connect as well.

After searching through various (not necessarily directly related) sites, I found the answer.

See simple connection string snippet below.

Pipe=MySQL;Protocol=pipe;Host=.;Database=[yourdb];User ID=[youruser];Password=[yourpass]

To my surprise, the little note about specifying a period “.” as the host for named pipe connections was not easily found in the references below..

References
PhalangerMySQL (Java2s), https://www.java2s.com/Open-Source/CSharp/Database/PhalangerMySQL/MySql/Data/CatalogData.htm
.Net MySQL Connector Documentation (MySql), http://dev.mysql.com/doc/refman/5.1/en/connector-net.html
Network Traffic Filter Documentation (wireshark), http://wiki.wireshark.org/DisplayFilters
Connecting MySQL via CLI (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connecting.html
Connection String Reference (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html
MySQL Workbench Documentation (huihoo), http://docs.huihoo.com/mysql/refman-5.5-en/wb-intro.html
Connectionstrings.com, http://www.connectionstrings.com/mysql
codemaker.co.uk, http://www.codemaker.co.uk/it/tips/ado_conn.htm#MySQLNETNativeProvider
MySqlConnection in C# (bitdaddys)http://bitdaddys.com/MySQL-ConnectorNet.html
PHP MySQL Reference, http://php.net/manual/en/function.mysql-connect.php
MySqlCommand in C# (stackoverflow) http://stackoverflow.com/questions/2775692/c-and-mysql-net-connector-any-way-of-preventing-sql-injection-attacks-in-a-ge

MySQL Quick Reference

Import CSV:

LOAD DATA LOCAL INFILE '/filename.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1, filed2, field3);

References
http://www.pantz.org/software/mysql/mysqlcommands.html
http://support.modwest.com/content/6/253/en/how-do-i-import-delimited-data-into-mysql.html

Missing MySQL References in PHP

The following can be used for quick LAMP installation and/or if you are missing MySQL references in PHP.

Debian:

sudo apt-get install mysql-server
sudo apt-get install apache2
sudo apt-get install php5
sudo apt-get install php5-mysql
sudo apt-get install phpmyadmin

Redhat:

sudo -c "yum install mysql-server"
sudo -c "yum install apache2"
sudo -c "yum install php5"
sudo -c "yum install php5-mysql"
sudo -c "yum install phpmyadmin"