Encoding the Connection String

Introduction

Examples of storing connection information while connecting to databases were shown in a previous tutorial, available here. The connection string information was stored in a web.config file.  However the string was stored in an easily readable XML file in clear text. All that is needed to hack a database would be available if one were to get access to the web.config file. It is essential therefore to make it harder to read this file to protect against such an eventuality. This tutorial discusses one method of obfuscating this information from prying eyes. However it is not infallible.

Base64 Encoding

Base64 encoding is a method of converting a piece of text (string) which can be read and comprehended into a string which looks as if it has been worked over (messed up). However, it can be carefully formatted back into a readable form with enough time. The process of decoding is the reverse of this process.

Visual Studio .NET with its System.Text.ASCIIEncoding class gives us a convenient way to encode and decode strings. The ConnectionString which contains all the information about connecting to a database is contained in a string; therefore, the class methods can be used to encode and decode. This next picture shows an object browser displaying the details of the System.Text.ASCIIEncoding class.

Simple code to encode and decode

This ASP.NET project called Securite has a single web form whose UI is as shown below. You make it work by entering an ASCII string in the String to Encode text box and clicking the Encode the String button. The encoded string will appear in the box below the button, as well as in the first text box in the Decode section. If you now click the Decode the String button, your original string reappears in the last text box as shown. In the database application you would  use the same code, but use the connection string.

Code for encoding and decoding

The code for the button click events for the above web form page is as shown here. Button1 is for encoding and Button2 is for decoding.

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim strgToEncode As String
strgToEncode = TextBox1.Text
Dim encodedStrg As String
encodedStrg = Convert.ToBase64String(System.Text. _
ASCIIEncoding.ASCII.GetBytes(strgToEncode)) 

'Takes the string in the textbox 1 and converts it to
'ascii bytes  TextBox2.Text = encodedStrg
TextBox3.Text = TextBox2.Text
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Dim decodedStrg As String
decodedStrg = System.Text.ASCIIEncoding.ASCII. _
GetString(Convert.FromBase64String(TextBox3.Text)) 

'takes the bytes and converts to string TextBox4.Text = decodedStrg
End Sub

SQL Connection String in configuration file

Connection string information can be stored in an external, persistent file such as  a configuration file. ASP.NET has the web.config file for web applications. There are advantages to using a configuration file, such as going over from a test set up to a production set up where the name of the server may change. As mentioned in the introduction, storing such information in clear text could help the hackers. This needs to be prevented. Instead of a string we shall store the encoded version, which is obtained by passing the string to the encode part of the program mentioned above. While retrieving the connection string we use the decode part of the above code.

If the ConnectionString were to be stored in clear text in the web.config file, the configuration information for a SQLConnection to my MSDE database will be as follows:

<appSettings>
<add key="orders"
value="workstation id=XPHTEK;
packet size=4096;
integrated security=SSPI;
data source='XPHTEK\NetSDK';
persist security info=False;
initial catalog=Northwind/>
</appSettings>

For the key='orders', the value is as shown above.

Now using the above code, we encode the string in the value of the above XML configuration file and process the code to derive the encoded value. Such a processing yields the following for the encoded value:

d29ya3N0YXRpb24gaWQ9WFBIVEVLO3BhY2tldCBzaXplPTQwOTY7aW 
50ZWdyYXRlZCBzZWN1cml0eT1TU1BJO2RhdGEgc291cmNlPSJYUEhURUtc
TmV0U0RLIjtwZXJzaXN0IHNlY3VyaXR5IGluZm89RmFsc2U7aW5pdGlhbCBj
YXRhbG9nPU5vcnRod2luZA==

Now the web.config will be modified by pasting the value above as follows:

<appSettings>
<add key="orders"
value="d29ya3N0YXRpb24gaWQ9WFBIVEVLO3BhY2tldCBzaXplPTQwOTY7aW 
50ZWdyYXRlZCBzZWN1cml0eT1TU1BJO2RhdGEgc291cmNlPSJYUEhURUtc
TmV0U0RLIjtwZXJzaXN0IHNlY3VyaXR5IGluZm89RmFsc2U7aW5pdGlhbCBj
YXRhbG9nPU5vcnRod2luZA=="/> </appSettings> 

Example of usage

Now using the AppSettings as above, to set the connection information to connect to the MS SQL 2000 Server you will use the following code:

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim strOrder As String
Dim dynSql As New SqlClient.SqlConnection
strOrder = System.Text.ASCIIEncoding.ASCII.GetString _
(Convert.FromBase64String(ConfigurationSettings. _
AppSettings("orders")))
dynSql.ConnectionString = strOrder
dynSql.Open()
Response.Write("Open <br>")
Response.Write(dynSql.ConnectionString & "<br>")
Response.Write(ConfigurationSettings.AppSettings. _
GetKey(0).ToString)
Response.Write("<br>")
dynSql.Close()
Response.Write("Closed<br>")
End Sub

The result shows that the above code successfully established the connection to the database server as shown below. The highlighted responses are seen in this resulting display.

Summary

Base64 encoding is not encryption, it is just reformatting the string in a slightly unreadable fashion. The characters you find in the encoded string are a giveaway that it is Base64, and that it can be unscrambled with some coding. However, it is somewhat better than clear text. It does take up more space than the text it replaces. If you need a copy of this project do send me an email.

blog comments powered by Disqus