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
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.
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..
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
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
LOAD DATA LOCAL INFILE '/filename.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (field1, filed2, field3);
The following can be used for quick LAMP installation and/or if you are missing MySQL references in PHP.
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
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"