Examples of encryption for database access

[William Prothero]

Folks:
Woke up this morning and realized I need to clarify a couple of points on my post.
1. For a test, you can use the LC script I included, exactly as given, which will access the included php test script on my server. 
2. The php script just returns the decrypted text that you put in the tPostA[“theQuery”] array element. For real world use, you would want to, in the php, encrypt the return text.
3. As far as I can tell, I need to have the encryption key and iV stored on both the LC app (to encrypt the text that is being sent) and the php script, to decrypt it.
4. I left out the part where the php encrypts the return value and the LC decrypts it. I’ll add it in if anybody wants it.

Best,
Bill

> On Jun 24, 2018, at 5:17 PM, William Prothero via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> 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; 		
> ?>
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode