Using SqlParameters()

In my last post I showed you my first ASP.NET code that I programmed, a first for many years.  I also talked about why the code isn’t very friendly, and quite basic which could provide hackers with a way to manipulate the SQL.

Well as promised I’ve rewritten the code and included a SqlParameter to thwart any hacking.

Here is the new code from the code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace Diary
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Datebox.Text = System.DateTime.Now.ToString();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn;
            SqlParameter DateParam;
            SqlParameter NoteParam;

            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NoteBook.mdf;Integrated Security=True;User Instance=True");

            SqlCommand MyCommand = new SqlCommand();

            MyCommand.CommandText = "Insert NoteBook (Date, Note) Values (@Datep, @Notep)";
            MyCommand.CommandType = CommandType.Text;
            MyCommand.Connection = conn;

            DateParam = new SqlParameter();
            DateParam.ParameterName = "@DateP";
            DateParam.SqlDbType = SqlDbType.DateTime;
            DateParam.Direction = ParameterDirection.Input;
            DateParam.Value = Convert.ToDateTime(Datebox.Text).ToString("yyyy-MM-dd");

            NoteParam = new SqlParameter();
            NoteParam.ParameterName = "@NoteP";
            NoteParam.SqlDbType = SqlDbType.NVarChar;
            NoteParam.Direction = ParameterDirection.Input;
            NoteParam.Value = Notebox.Text;

            MyCommand.Parameters.Add(DateParam);
            MyCommand.Parameters.Add(NoteParam);

            MyCommand.Connection.Open();
            MyCommand.ExecuteNonQuery();
            MyCommand.Connection.Close();
        
        }

    }
}

The additional of MyCommand , DateParam/NoteParam commands.

The plan is to continue with developing this program in the weeks and months ahead.

Advertisements

Looking at ADO.NET and using ASP.NET 4

ADO.NET is still popular today, even if LINQ to SQL or EF are the newer ‘fun’ technologies to be programming in.

Some developers have the pleasure of coding in new technologies every day.  Others are not so lucky and are stuck in ASP.NET 2.0 land.

I thought my first code appearing in this blog would be ADO.NET compiled using framework 4.0, though of course this could be in any framework.

First off, I needed to think up an idea for an application.  I came up with a simple Note Book application, which basically allow the user to type in his daily thoughts.  So what is the simplest quick and dirty way of coding it.  Remember this is my first attempt, I came up with the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Diary.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Note Book"></asp:Label>
        <br />
        <br />
        <asp:Label ID="Label2" runat="server" Text="Date: "></asp:Label>
        <asp:TextBox ID="Datebox" runat="server"></asp:TextBox>
        <br />
        <br />
        Note:<br />
        <br />
        <asp:TextBox ID="Notebox" runat="server" Height="300px" 
            TextMode="MultiLine" Width="800px"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="Button1" runat="server" Text="Post" onclick="Button1_Click" />
        <br />
        <br />
        <br />
    
    </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace Diary
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Datebox.Text = System.DateTime.Now.ToString();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn;
            SqlCommand cmd;
            string cmdString = @"Insert NoteBook (Date, Note) Values ('" + Datebox.Text + "', '" + Notebox.Text + "')";
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NoteBook.mdf;Integrated Security=True;User Instance=True");
            cmd = new SqlCommand(cmdString, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

        }

    }
}

The database:

image

As you can see with the code it’s very ‘naked’.  There are no fancy error checking routines, validation or verification, and the SQL part could lead to a hacker manipulating your program (more on this in a future post).  Remember it’s just raw code here that assumes the user will enter in some data into the Note field and not tamper with the Date textbox.  The date is automatically entered in the Date box.

When you run the program, the following screen appears:

image

Once the user clicks Post button, the screen will flicker, which means the data has been stored in the database.  Simple.  Though I don’t recommend you code this way for a client or your company, the code does what it needs.

So this is the beginnings of a new system.