This topic is locked

Encrypt and decrypt data in the database with ASPRunnerPro 6.3

11/17/2010 5:09:46 PM
ASPRunnerPro Tips and tricks
Sergey Kornilov

In this example we'll show you how to encrypt and decrypt the contents of Model field in Cars table in few easy steps.
[size="6"]Scenario 1. Pure ASP solution.[/size]
Works with all databases. Requires ASPRunnerPro 6.3. Data encrypted and decrypted in ASP code.
Since ASP doesn't provide built-in encryption routines we are going to use RC4 algorithm. While it's the most secure one it works fine for many purposes.

  1. Create a file named encrypt.asp in the output directory and paste the following code there. Make sure to use your own passphrase.



<%

Dim sbox(255)

Dim arrkey(255)

dim pass_phrase

pass_phrase="my pass phrase"
Sub RC4Initialize(strPwd)
dim tempSwap

dim a

dim b
intLength = len(strPwd)

For a = 0 To 255

arrkey(a) = asc(mid(strpwd, (a mod intLength)+1, 1))

sbox(a) = a

next
b = 0

For a = 0 To 255

b = (b + sbox(a) + arrkey(a)) Mod 256

tempSwap = sbox(a)

sbox(a) = sbox(<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=15769&image=1&table=forumtopics' class='bbc_emoticon' alt='B)' />

sbox(<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=15769&image=2&table=forumtopics' class='bbc_emoticon' alt='B)' /> = tempSwap

Next



End Sub



Function encrypt(plaintxt)
dim temp

dim a

dim i

dim j

dim k

dim cipherby

dim cipher
i = 0

j = 0
RC4Initialize pass_phrase
For a = 1 To Len(plaintxt)

i = (i + 1) Mod 256

j = (j + sbox(i)) Mod 256

temp = sbox(i)

sbox(i) = sbox(j)

sbox(j) = temp



k = sbox((sbox(i) + sbox(j)) Mod 256)
cipherby = Asc(Mid(plaintxt, a, 1)) Xor k

cipher = cipher & Chr(cipherby)

Next
encrypt = cipher
End Function



Function decrypt(plaintxt)



decrypt = encrypt(plaintxt)



End Function
%>


Now proceed to your project and setup events
2. Global event: After Application Initialized

asp_include "encrypt.asp",false


3. Add page: Before record added

values("Model")=encrypt(values("Model"))


4. Edit page: Before record updated

values("Model")=encrypt(values("Model"))


5. Edit page: Process record values

values("Model")=decrypt(values("Model"))


6. Use 'List page: Before record processed' event to decrypt data

data("Model") = decrypt(data("Model"))


7. Build your application and enjoy.
[size="4"]Other considerations[/size]

  1. Encoding existing data
    This approach works the best for new data to be added to your database. If you have unencrypted data in the database you need to encode it manually. This article provides sample code for this job: http://xlinesoft.com/articles/encrypt_passwords.htm.
  2. If your application uses a login page and password is encrypted as well you need to implement BeforeLogin event to decrypt the password.



set rs = CustomQuery("SELECT username, password from users where username='" & username & "'")
if decrypt(rs("password"))=password then

SESSION("UserID"] = username

SESSION("AccessLevel") = ACCESS_LEVEL_USER

Response.Redirect "Location: menu.asp"

Response.End

end if
BeforeLogin=true


[size="6"]Scenario 2. Using MySQL functions.[/size]
If your project is MySQL-based you can enjoy the beauty of builtin MySQL encryption fucntionality. Works with MySQL 5.x and all versions of ASPRunnerPro.

  1. Create triggers in the database to encrypt data before insert and update operations.



delimiter |
CREATE TRIGGER insert_encrypt BEFORE INSERT ON cars

FOR EACH ROW BEGIN

SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase");

END;

|
delimiter |
CREATE TRIGGER update_encrypt BEFORE UPDATE ON cars

FOR EACH ROW BEGIN

SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase");

END;

|


2. Modify SQL query to decrypt data on the fly. Use the same passphrase.

SELECT

...

AES_DECRYPT(Model,"my passphrase") as Model2,

...

FROM carscars


Note the the alias of decrypted field.
3. BeforeEdit/BeforeAdd events.
Since calculated field cannot be updated directly we need a little magic to update our source field.
Add the following code to BeforeAdd/BeforeEdit event:



values("Model")=values("Model2")

values.Remove("Model2")


This will replace Model2 with Model in SQL query and will allow to update our database. Data will be encrypted by trigger we created on step 1.
4. If your application uses a login page and password is encrypted as well you need to implement BeforeLogin event to decrypt the password.


set rs = CustomQuery("SELECT username, aes_decrypt(password,'my passphrase') as p from users where username='" & username & "'")
if decrypt(rs("p"))=password then

SESSION("UserID"] = username

SESSION("AccessLevel") = ACCESS_LEVEL_USER

Response.Redirect "Location: menu.asp"

Response.End

end if
BeforeLogin=true


[size="4"]Other considerations[/size]

  1. Encoding existing data
    This is a piece of cake since database handles encryption for you. To encrypt existing data create a trigger first (bullet #1) and then run the following query:

Update TableName set FieldName=FieldName


Trigger will update FieldName with encrypted value. Make sure you only run this query once. If you attempt to encrypt data twice you won't be able to decrypt it.
This is it.

D
dlangham 12/9/2010

Sergey,
I am using the latest version of ASPRunner 7113 and everything above works for encrypting data above for MS SQL, but when I try to add encryption for login page and password, I cannot get it to work and the link to the script for encrypting existing data does not work.

Do you have anymore indepth instructions on how this works with User logon encryption?

D
dlangham 1/30/2011

Bump.... any ideas?