String Encryption With Visual Basic .NET - Creating the Database

(Page 5 of 7 )

At this point you have the option of creating your database in either SQL Server or Oracle. The difference between the databases is really that of our Primary Key. In SQL Server we simply create a primary key in our table and define it as an Identity with an auto incrementing value. With Oracle, on the other hand, you must create the primary key, and then create a Sequence where an incremented number can be pulled from and put into each record.

If you are using SQL Server create a new Database called 3DESOrders and then run the following SQL in the Enterprise Manager to create a table for our order data.

CREATE TABLE [dbo].[CUSTOMER_ORDER] (
[Order_ID] [int] IDENTITY (1, 1) NOT NULL ,
[First_Name] [nvarchar] (15) NULL ,
[Last_Name] [nvarchar] (25) NULL ,
[Address] [nvarchar] (50) NULL ,
[City] [nvarchar] (20) NULL ,
[State] [nvarchar] (2) NULL ,
[Zip] [nvarchar] (10) NULL ,
[CC_Type] [nvarchar] (20) NULL ,
[CC_Number] [varbinary] (100) NULL ,
[CC_Exp] [nvarchar] (5) NULL
) ON [PRIMARY]
GO

When creating our table in Oracle, not only must we create the table, but it's also a good idea to create a new user for the table so our Table isn't inserted into the System tablespace. Therefore, the SQL needed is a bit longer. If you are using Oracle as your database backend, run the following SQL to create the required table, sequence and user.

CREATE USER "ORDERS" PROFILE "DEFAULT"
IDENTIFIED BY "orders" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED
ON USERS
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "ORDERS";

CREATE TABLE ORDERS.CUSTOMER_ORDER
(
ORDER_ID NUMBER(18,2) UNIQUE NOT NULL,
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(25),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(20),
STATE VARCHAR2(2),
ZIP VARCHAR2(10),
CC_TYPE VARCHAR2(20),
CC_NUMBER RAW(100),
CC_EXP VARCHAR2(5),
CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY (ORDER_ID )
)

create sequence ORDERID start with 1 increment by 1;

Before you continue, you may want to use SQL Server Enterprise Manager, or the Oracle equivalent, to verify that the table does in fact exist and all is functioning properly.

Saving an Encrypted Order
To save an encrypted order to the database we need to do 7 things:

1. Create variables to store the data about to be saved
2. Retrieve the data from the order entry form and store in variables
3. Declare parameters defining data types that are about to be inserted into the database
4. Open a connection to the database
5. Define the insert SQL statement
6. Add the previously added parameters to the Command object
7. Finally, save the data

We are going to implement this order saving code as a private subroutine, rather than coding it directly into a button click. Insert the following code into your Form1 class:

Private Sub InsertOrderItem()
' Here is our connection string. This uses a trusted connection so
' your user id must have access to the database you are attempting to
' connect to. Replace the "data source" with the correct server.
Dim ODC As String = "data source=dotnetserver;initial catalog=3DESOrders;
Trusted_Connection=true;workstation id=POWERHOUSE;packet size=4096"
Dim myConnection As New SqlConnection(ODC)
Dim myCommand As New SqlCommand()

' Used for storing the information we wish to save.
Dim FirstName As String
Dim LastName As String
Dim Address As String
Dim City As String
Dim State As String
Dim Zip As String
Dim CCType As String
Dim CCNumber() As Byte
Dim CCExp As String
Dim TDES As New TripleDES()

' Retrieve the order information
FirstName = txtFirstName.Text
LastName = txtLastName.Text
Address = txtAddress.Text
City = txtCity.Text
State = txtState.Text
Zip = txtZip.Text
CCType = cmbCCType.Text
CCNumber = TDES.Encrypt(txtCCNumber.Text)
CCExp = txtCCExp.Text

' Declare parameters for the information we wish to insert into the
' database.
Dim paramFirstName As New SqlParameter("@FirstName", FirstName)
Dim paramLastName As New SqlParameter("@LastName", LastName)
Dim paramAddress As New SqlParameter("@Address", Address)
Dim paramCity As New SqlParameter("@City", City)
Dim paramState As New SqlParameter("@State", State)
Dim paramZip As New SqlParameter("@Zip", Zip)
Dim paramCCType As New SqlParameter("@CCType", CCType)
Dim paramCCNumber As New SqlParameter("@CCNumber", CCNumber)
Dim paramCCExp As New SqlParameter("@CCExp", CCExp)

myCommand.Connection() = myConnection
myCommand.Connection.Open()
myCommand.CommandText = "INSERT INTO CUSTOMER_ORDER
(FIRST_NAME,LAST_NAME,ADDRESS,CITY,STATE,ZIP,CC_TYPE,CC_NUMBER,CC_EXP) VALUES
(@FirstName,@LastName,@Address,@City,@State,@Zip,@CCType,@CCNumber,@CCExp)"
myCommand.Parameters.Clear()
myCommand.Parameters.Add(paramFirstName)
myCommand.Parameters.Add(paramLastName)
myCommand.Parameters.Add(paramAddress)
myCommand.Parameters.Add(paramCity)
myCommand.Parameters.Add(paramState)
myCommand.Parameters.Add(paramZip)
myCommand.Parameters.Add(paramCCType)
myCommand.Parameters.Add(paramCCNumber)
myCommand.Parameters.Add(paramCCExp)
' Actually insert the information into the database.
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub

This subroutine is strictly to access a SQL Server database. To use it you must change the Data Source to point to your SQL Server. In the code above, my database happens to reside on a server called dotnetserver. If you will be using an Oracle database, you will need to change 2 lines of code. The first line defines the connection to the database. Replace line 2 of the above code with the following line:

Dim ODC As String = "Provider=MSDAORA.1;Password=orders;User ID=orders;Data Source=tst"

As well as the connection string, you must also replace all Sql commands with OleDb. For example, the SqlParameter would then become OleDbParameter. We have already created an orders user with the SQL script previously provided, but you may need to modify the Data Source setting to point to your Oracle instance (also known as the SID). Once you have fixed the connection string, you will need to modify the actual insert statement, since Oracle and SQL Server will insert data differently. Replace the insert statement with this line of code:

myCommand.CommandText = "INSERT INTO CUSTOMER_ORDER( ORDER_ID, FIRST_NAME, LAST_NAME, ADDRESS,CITY, STATE, ZIP, CC_TYPE, CC_NUMBER, CC_EXP) VALUES (ORDERID.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

In this line of SQL, we are utilizing the ORDERID sequence that was created by calling a function called NextVal. This retrieves the next sequence value and then increments the sequence by a value of 1.

Now that we have defined both the SQL Server and Oracle versions of the code, lets take a closer look at it. As you can see, it is a very simple routine to insert data into database. You may also notice that at the very beginning we instantiate an instance of our TripleDES class and then later on we call the Encrypt method of that class. This passes the credit card number from our form to the encryption routine and returns a byte array, which is stored in the database. It's definitely a very straightforward class to use.

We will also need a routine to clear all values on the Order Entry forms. Insert the following subroutine into your Form1 class:

Private Sub ClearItems()
txtFirstName.Text = ""
txtLastName.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtState.Text = ""
txtZip.Text = ""
cmbCCType.Text = ""
txtCCNumber.Text = ""
txtCCExp.Text = ""
End Sub

Now that both of those subroutines are in place, we will actually be able to save information to the database. Return to the Project Explorer and view Form1 of the project. Double click the SaveOrder button so we can modify the click method of this button. Place the following code (in bold) into the click event of this button.

Private Sub btnSaveOrder_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles btnSaveOrder.Click
InsertOrderItem()
ClearItems()
End Sub

Our application should encrypt the credit card number and save the information so let's give it a try. Run the application and enter in some order information as I have done here in Figure 3.



Once you have entered in the information, click the Save Order button. The information should be saved to the database and all the information on the order form will be cleared.

If you are using SQL Server, you can simply use the Server Explorer to view the data that was just committed to the table. Expand the server connection and locate the table where the order was just saved as seen here in Figure 4.



Once you have found the table you can right click on it and choose Retrieve Data from Table to view the data. The results from SQL Server simply indicate that Binary data is present within the table.



With Oracle, you can also use a program such as TOAD (Tool for Oracle Application Developers) to view the data. When using TOAD we can actually see the data within each column. Remember it is in encrypted byte format so it won't make much sense but you can verify that the data did in fact get inserted, as seen here:

Next: Decrypting the Order Data >>
 

More VB.Net Articles
More By Wrox Team

Comments On: String Encryption With Visual Basic .NET

· Is this imports statement only available in .NET framework 2.0 and above ?.I am...    · Thanks for making my job so much easier. It would have taken me days to figure this...    · Thanks for the help, clear easy to use example!!!! :)    · Should read Imports System.TextImports System.Security.Cryptography   >>> Post your comment now!