Encrypt text in PHP and decrypt in MS SQL

Question

As the title of the post says, I am testing a bit of code where I enter data on my form, then encrypt all the fields using MD5 (or whatever you think is best) and send it to my MS SQL Database.

$query = "INSERT INTO table_hide(firstname,last) 
VALUES('".md5('Gary')."','".md5('Long')."');

Of course the database data is encrypted and I would like to read it.

I am also aware of using EncryptByPassPhrase and DecryptByPassPhrase in MS SQL to Encrypt and Decrypt a password. However, I would like assistance in using the same "key" in the website to encrypt and on the SQL server to decrypt

So my question is, how would I encrypt my data I'm sending to my database and then have that same key in my database to decrypt it.

unless your a bank 99% of people should not store data encrypted in the db — , Aug 20 '15 at 04:03
md5 is not an encryption algorithm.. it is a hashing algorithm which means it only works one way, you can't get the source data from the resultant hash. — Orangepill, Aug 20 '15 at 04:09
@Dagon I am just making an effort for my insert statement not to be transfered in plain text. — Niana, Aug 20 '15 at 04:28
@Niana A better option would be to connect to your database via ssl if you want to prevent mitm viewing of your insert statements. see http://stackoverflow.com/questions/9738712/connect-to-remote-mysql-server-with-ssl-from-php — Orangepill, Aug 20 '15 at 04:31
it will be 'transferred' on the server so um what are you scared of? — , Aug 20 '15 at 04:37
@Orangepill - best suggestion, but that link relates to MySQL, not MsSql. [This question](http://stackoverflow.com/questions/22294221/php-pdo-connect-to-ms-sqlserver-express-using-ssl/33322487#33322487) is related to mssql. — Kyle Wiering, Oct 31 '15 at 15:39

score 0 · Answer 1 · edited May 23 '17 at 12:07

I am testing a bit of code where I enter data on my form, then encrypt all the fields using md5 (or whatever you think is best) and send it to my MS SQL Database.

MD5 doesn't provide encryption, it's a one-way cryptographic hash function. The difference between hashing and encrypting is very important.

$query = "INSERT INTO table_hide(firstname,last) 
VALUES('".md5('Gary')."','".md5('Long')."');

You might want to read this answer on preventing SQL injection. String concatenation is less reliable than prepared statements, so you might want to review your other code.

So my question is, how would I encrypt my data im sending to my database and then have that same key in my database to decrypt it.

You could, for example, have a stored procedure that decrypts it with a constant key. However, what exactly are you hoping to accomplish by encrypting records in the first place?

Encrypt text in PHP and decrypt in MS SQL

1 Answers1