Thomas Sampson


1 Comment

Getting HTML into a database

Today I found a great function in ASP.net to convert user inputted string into a format suitable to be held in a database.

The first thing you must do is turn off ValidationRequests. Validation requests are a system .NET has in place to immediately stop the user inputting html into any form . But sometimes you need to allow this. Changing the ValidateRequest attribute of the text entry and submission button didnt work for me , im not sure why, but if the following is included in the webconfig then the user is allowed to submit html through a form (this will compromise security and should be odne with caution).

<system.web>
<pages validaterequest”false”>

</pages>

At this stage your webapp can now handle html being submitted, but before it can be inserted into a database it must be converted into escape characters.

Here is an example of how to do so, where userinput is plain html entered by the user

string encodedinput=HttpUtility.HtmlEncode(userinput);

this html data can now be inserted into any database using an SQL command. There is no harm using this method on all form submisions, even if they are not likely to contain html. The reason for this is the encode function will also encode illegal charcaters such as double quotes (\”) & % and @ symbols which will be rejected by the database.

When retrieving this encoded html from the database it can easily be dedoded back into regulr html using

string decodedhtml=HttpUtility.HtmlDecode(htmlfromdatabase);

If used carefully, along with SQL injection prevention techniques, this makes for a safe way to store scripts and html in a database.


Leave a comment

Avioding SQL injection

After much playing around and problems I finally figured how to add safe parameters into an odbc command. Here is a sample….

OdbcCommand postcomment = new OdbcCommand(“insert into comments values (‘”+newguid+”‘,'”+itemid+”‘,?)”, temp);

Here the ? represents the first and only parameter to be passed into the command using..

postcomment.Parameters.AddWithValue(“@comment”, comment);

then

postcomment.ExecuteNonQuery();

If you were to include > 1 ? symbols, the parameters must be added in the order they appear in your original command.


Leave a comment

C# Microsoft Access database access

This can be used in either a windows application OR an asp.net file.

NOTICE: if using with asp.net file the access file MUST be placed and refered to in the ROOT directory of IIS.

ok, first make the reference

using System.Data.OleDb;

to open and close connection….

OleDbConnection thisConnection = new OleDbConnection(
@”Provider=Microsoft.Jet.OLEDB.4.0;” +
@”Data Source=E:/WEB_SERVER/server_user_database.MDB”);
thisConnection.Open();
//work here//
thisConnection.Close();

to add data…

OleDbCommand new1 = new OleDbCommand(“INSERT INTO tablename (column(s)) values(‘valueshere’)”, thisConnection);
new1.ExecuteNonQuery();

to retrieve data…

OleDbDataReader myReader=null;
OleDbCommand test = new OleDbCommand(“select * from tablename, thisConnection);
myReader = test.ExecuteReader();
while(myReader.Read())
{Label1.Text=(myReader[“columnnamehere”].ToString());};
myReader.Close();

always close connection!!!…

thisConnection.Close();


Leave a comment

Notes on SQL Server

 

Include the reference;

using System.Data.SqlClient;

to connect and open…

SqlConnection myConnection = new SqlConnection(“user id=SQLSERVERUSERNAME;” +”password=SQLSERVERPASSWORD;server=SERVER PATH HERE, USE . IF LOCAL;” + “Trusted_Connection=yes;”+ “database=DATABSE TO CONNECT TO; “);
myConnection.Open();

NOTE: Connection must be made EVERY time data is added / retrieved

To Add Data the connected Database….

//create new command to add data//
SqlCommand COMANDNAMEHERE=newSqlCommand(“INSERTINTO tablename(collumname) ” //here colum names can be comma seperated//
+ “Values (valuetoadd)”, myConnection);//here the values to add can be comma seperated//

// myConnection is name of establishd connection//

//this command will insert new value (valuetoadd) into tablename in specified column//

COMMANDNAMEHERE.ExecuteNonQuery();
//run command//

To Retrieve Data from the connected Database and assign to a label….

SqlDataReader READERNAMEHERE = null; //create new reader object//
//create new command to retrieve data//

SqlCommand COMMANDNAMEHERE = new SqlCommand(“select * from tablename where collum LIKE ‘value’, myConnection);
//this command selects all the values from the table where the value appears in the collum//
READERNAMEHERE = COMMANDNAMEHERE.ExecuteReader();
//code above runs the command into the reader and starts it ‘reading’//
while(READERNAMEHERE.Read())
{
labelname.Text+=myReader[“Collum”].ToString();
}
//while ever the reader is reading, grab the values from the collumn and add them to a multiline text box//

CLOSE THE CONNECTION (Must be done after data has been added / retrieved)

MyConnection.Close(); //myConnection is the name used when creating connection//