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.

ASP.NET 4 Learning Week 8

Now that I’m near the end of Beginning ASP.NET in C# and VB book, I’ve decided to jump the gun and start on Professional ASP.NET in C# and VB.

I read the following:

  • Application and Page Frameworks
  • ASP.NET Server Controls and client-side scripts
    I’m currently reading about Web Server Controls. 
    If you compare both books you can notice how more advanced the Pro book is.  The code listings in the Pro book don’t related to one big web site that you build like you do in the Beginners book.

ASP.NET 4 Learning Week 7

This week I learnt the following:

  • Working with data – formatting of various data-bound controls, handle events, and caching 
  • Security

ASP.NET 4 Learning Week 6

This week I got down and learnt the following:

  • Display, edit, delete data using controls (GridView, DetailsView, SqlDataSource)
  • LINQ and the ADO.NET Entity Framework

Quite a learning leap from my ASP.NET 1.1 days of using Stored Procedures and DataGrids.

Also this week I’ve been working on getting TortoiseSVN running on my laptop.  With help from a few Devs on Twitter I’ve started to get things running.  Still need to read up a bit more on it.

Will I stick with TortoiseSVN or go back to VisualSVN, I cannot answer until I get a few personal projects up and running.

ASP.NET 4 Learning Week 5

This week I seemed be concentrating more on getting SQL Server 2008 r2 to work with the demo database in the book.

So I just went through 1 chapter this week:

  • All about databases and using Visual Studio Server Explorer and CRUD.

Tomorrow I start using GridView, DetailsView etc.  This is really the heart of programming with ASP.NET.

ASP.NET 4 Learning Week 4

This week has been a bit up and down.  I haven’t been feeling well all week, so my learning using Beginning ASP.NET 4 In C# and VB was limited to this:

  • Validating data
  • ASP.NET Ajax (UpdatePanel, ScriptManager, UpdateProgress, Web Services)
    This weekend I’ll try and pick myself up and get back on track for Monday.