Examples of encryption for database access

[William Prothero]

Folks:
In case you are interested, or if you have any feedback, here is the code I use to test AES encryption for sending posts to interact with a mysql database.

This work is inspired by the excellent dbLib product of Andre Garza, that got me to look into encryption a lot deeper than I had to date.

Perhaps Andre would like to chime in here, as I am a complete novice in this area. What got me started was purchasing his dbLib software and getting warning messages that there was no “iv” vector specified. From internet searching I got that the encryption is vulnerable to a “Dictionary” attack. An “iv” vector is analogous to a “salt”, which make the encryption much more difficult to crack. I’m using php version 5.6.36

This should make transfers to a from a remote database pretty secure. It is different from password security, where only the encrypted password needs to be compared with the encrypted db value. Here (I think) both the server and the client need to have the key and iv values.

Here is the code that I used to test the encryption. If I am wrong about any of this, please let me know. An example like this would have saved me a bunch of time, so I hope it will be useful to somebody else on the list.

————Testing iv for encryption
--To test this on your own server, upload the php script where you put cgi's
-- and modify the myURL setting
on testEncryption
   put "http://earthexplorer.earthlearningsolutions.org/scgi-bin/wpEncryptionTest.php" into myURL
   put "AES-256-CTR" into tCipher
   put "AFBDDFCFBDBBDDCCFFACGHDFFFFEEDCC" into tEncryptionKey
   put "ABCDEEABCDEEAA%A" into tIV
   put "The php should return this text." into tPostA["theQuery"]
   put "query" into tPostA["type"]
   put ArrayToJSON(tPostA,"string",pPretty) into tJson
   encrypt tJson using tCipher with key tEncryptionKey and iV tIV
   put base64encode(it) into tMyEncryptedData
   post tMyEncryptedData to url myURL
   put it into tRet
   put tRet into fld "status"
   put cr&"num chars: "&(the number of chars in tRet) after fld "status"
   put cr&base64decode(tRet) after fld "status"
end testEncryption
   
----------php script, on server ---------------------------
--Note:  you can run the above script on my server,
--to test the LC script.  
<?php
//file: wpEncryptionTest.php
//external function
 function debug($msg) {
     $debug = false;
     if ($debug) {
         error_log("[DB LIB] $msg");
         echo "$msg.\n";
     	}
 	}
//php code
	$encryption_key = "AFBDDFCFBDBBDDCCFFACGHDFFFFEEDCC";	
 	$cipher = "AES-256-CTR"; // do not change cipher unless you know what you're doing
	$post = file_get_contents('php://input');
	$iv = 'ABCDEEABCDEEAA%A';
	$ivlen = 16;
	/* set for debugging. To encrypt, set to TRUE */
	$post = openssl_decrypt($post, $cipher, $encryption_key, $options=0, $iv);
	$req = json_decode($post,true);
	if (!$req) {
     	debug("error on decrypt");
     	debug(openssl_error_string());
 	}
 	$theOut = $req["theQuery"];
 	$tRet = base64_encode("Decrypted query: $theOut.\n");
 	echo $tRet; 		
?>