How to get a value from a text box into the database

This question was asked on a forum and I took some time to construct a reasonably lengthy reply so I’m copying it to my blog for a bit of permanence.

I suspect that many of my regular readers will be dismayed at the lack of proper architecture (e.g. layering) but we all had to start somewhere and I suspect that your first programs were not properly layered or structured either. I know mine certainly weren’t. My aim with this was to show how a simple goal can be achieved, what basic things are needed and how to fit it all together by doing the simplest thing that would work (a mantra from the agile world).

Here’s the post (slightly edited to put back some of the original context):

Okay – Let’s step back and show the whole thing from text box to database. NOTE: that this example shows everything in one place. This is generally considered poor practice, but as you are only just starting I’ll not burden you with the principles of layered architecture and the single responsibility principle and so on. (Just be aware they exist and one day you’ll have to learn about them)

So, let’s say you have a form with two text boxes, one for a name, and one for an age. Lets call them NameTB and AgeTB. The user can enter information in these text boxes and press a button that adds them to the database.

First, we need to get the data from the text boxes into a form we can use.

string name = NameTB.Text;
int age = Convert.ToInt32(AgeTB.Text);

Since text boxes only deal with strings we have to convert the string into a number (an Int32 – a 32bit integer) for the age value.

Now, we need to set up the database connection and command in order to insert this. I’ll assume you already have a connections string to your database, I’ve called it myConnectionString for this example.

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand("INSERT Person(NameField, AgeField) "+
    "VALUES (@nameParam, @ageParam)", myConnection);

I’ve now set up the SQL Command with an insert statement. I’ve assumed there is a table called Person and it has two columns called NameField and AgeField. I’m also going to insert the values via parameters, which I’ve indicated with @nameParam and @ageParam. SQL Server requires that all parameter names start with an @ symbol. Other databases may vary.

myCommand.Parameters.AddWithValue("@nameParam", name);
myCommand.Parameters.AddWithValue("@ageParam", age);

We’ve now added the parameters into the SQL command and we’ve given each parameter the value we got earlier. Finally:

myConnection.Open();
myComment.ExecuteNonQuery();
myConnection.Close();

This opens the connection, runs the INSERT statement and closes the connection again. We’re using ExecuteNonQuery because we don’t expect any results back from SQL Server. If we were expecting data back (e.g. because we were using a SELECT statement) we could use ExecuteReader (for many rows/columns) or ExecuteScalar (for a single value).

This is a very basic example. I’ve not shown any error checking or exception handling. There is also the implicit assumption that all this code resides inside a button click event, which is considered poor practice for anything but a small or throw away application.

Tip of the Day #4 (Connection Strings in Config files)

From .NET 2.0 onwards a new and improved configuration management system has been put in place. You can now add a <connectionString> element to the config file and use it to place the connection strings to the database and then retrieve then in a consistent way in your application. It supports multiple connection strings too if you need to access multiple databases.

The config file looks like this:

<configuration>
...
   <connectionStrings>
    <add name="Default" connectionString="Server=(local);database=MyDatabase"/>
  </connectionStrings>
...
<configuration>

From the .NET application you can access the connection string like this:

connectionString =
    ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

Just remember to add a reference to System.Configuration in your project and ensure that the code file is using the System.Configuration namespace as well.

Inserting geometry through a .NET Application

THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008

Following from my previous posts (Getting started with Spatial Data in SQL Server 2008, Spatial Data in a .NET application) on the new spatial features of SQL Server 2008 I’ve been looking at how to get spatial data into SQL Server from a .NET application. This has not been as easy as expected.

I suspect I just have not found the right piece of documentation because my eventual solution isn’t one I’m entirely happy with.

I was unable to add a SqlGeometry as a parameter to the collection of parameters on the SqlCommand object in my .NET application. The SqlGeometry does not appear in the enumeration for SQL Server data types. My first thought was to put the data through as binary as I vaguely remember reading something about using binary in something I read recently, but I couldn’t quite remember where. However, when I created the geometry object in .NET then used .STAsBinary() on it so the parameter object would accept it the INSERT failed. The exception message was:

A .NET Framework error occurred during execution of user defined routine or aggregate 'geometry':
System.FormatException: One of the identified items was in an invalid format.
System.FormatException:
   at Microsoft.SqlServer.Types.GeometryData.Read(BinaryReader r)
   at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r)
   at SqlGeometry::.DeserializeValidate(IntPtr , Int32 )
.
The statement has been terminated.

 

Yes, that was the MESSAGE from the exception. The stack trace above comes from within SQL Server itself. There is a separate stack track for my application. (I’m guessing if you are familiar with CLR stored procedures you may have seen this sort of thing before. I’ve not used the CLR code in SQL Server before, so it is a new experience)

The solution I found was to mark the parameter as an NVarChar, skip the creation of an SqlGeometry object and use a string containing the WKT (Well Known Text) representation. For example:

cmd.Parameters.Add("@Point", SqlDbType.NVarChar) = "POINT (312500 791500)";

I mentioned earlier that I’m not all that happy with this solution. That is because if I already have an SqlGeometry object I don’t want to have to convert it to a human readable format and have SQL Server parse it back again. Human Readable formats tend not to be the most efficient way of representing data and the geometry type already has a more efficient (as I understand it) format. Although in this case I bypassed the creation of an SqlGeometry object in my code, I can foresee cases where I might have created an SqlGeometry object through various operations and would have produced a fairly complex object. In those cases I wouldn’t want the additional overhead formatting it into WKT, passing it over the network and parsing it on the other side.

If I find a better solution I will post it.

Spatial Data in a .NET application

THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008

Following from my last post about Getting Started with Spatial Data in SQL Server 2008 in the database, how about actually using it in an application? Since that is where most data will end up at some point, it must be possible to use the spatial data in an application some how.

So, using the Town table created before and populated with our fictional village of Pitcardine I wrote this very simple program to see what actually came through ADO.NET to the end application.

static void Main(string[] args)
{
    SqlConnection connection =
        new SqlConnection("Server=(local);"+
            "database=SpatialSandbox;Trusted_Connection=Yes;");
    SqlCommand command =
        new SqlCommand("SELECT * FROM Town", connection);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                object value = reader.GetValue(i);
                Console.WriteLine("{0} ({1}) = {2}",
                    reader.GetName(i), value.GetType(), value);
            }
        }
    }

    Console.ReadLine();
}

The result was:

TownId (System.Int32) = 1
Name (System.String) = Pitcardine
TownGeom (Microsoft.SqlServer.Types.SqlGeometry) = POLYGON ((0 0, 370 0, 370 160
, 200 250, 0 250, 0 0))
TownGeomText (System.String) = POLYGON ((0 0, 370 0, 370 160, 200 250, 0 250, 0
0))

At this point I’m looking around to find the assembly that I can reference in my .NET application. After some looking I found it in C:MSSQLMSSQL10.MSSQLSERVERMSSQLBinn however that directory is not guaranteed as it depends on your SQL Server installation. It would be nice if the assembly showed up in the .NET tab in the Add Reference dialog rather than require the developer to hunt it down. (I’m guessing that this is due to the CTP nature of the product I’m currently using)

Once the column value can be accessed as an SqlGeometry object in the code it becomes possible to query the object. However, the results are pretty much all SQL data types. For example:

SqlGeometry geometry = (SqlGeometry)reader.GetValue(2);
SqlDouble sqlArea = geometry.STArea();
double area = sqlArea.Value; // could also cast to double

Note that STArea() is a method, not a property as you might expect in this sort of scenario. I suspect that each time it is called the area is worked out which may be an expensive operation hence keeping the method syntax.

What is a DAL?

I occasionally see posts on forums asking for help with some database
problem. Then I see they’ve been using the the wizards in Visual Studio (which
is okay for a beginner, but should really be ditched once the basic concepts
have been learned). I suggest that they use a DAL and I get lots of positive
signals back that they’ll do that in future. Then a week or two later they’ve
got themselves into another problem and they’re still using wizard generated
code.

So, it occurred to me that just saying “Use a DAL” doesn’t mean anything. If
someone told me that I need to use the finkle-widget pattern I wouldn’t know
what they are talking about. And if a person has never heard the term DAL
before, or never had it explained to them, then it is just as useful as saying
“use the finkle-widget pattern”

So, I figured it would be a good idea to show what a very basic DAL looks
like and to explain what it is.

A DAL is a Data Abstraction Layer. It is the part of the application that is
responsible for communicating with a data source. That data source is typically
a database, but it can be anything you like, such as an XML file, a plain text
file or anything else that data can be read from or written to.

In this example it will be a static class that communicates with a SQL
Server. It picks up the connection string
from the config file. It will communicate with stored procedures and return
DataTables, DataSets or single values. It doesn’t do everything a DAL could do,
but it shows the basic functionality.

Whenever I create DAL classes I try and think about them as if they are a
proxy for a group of actual stored procedures. I give the methods the same name
as the stored procedures, I give each method the same parameter names as the
stored procedure.

A DAL class has to mange the connection, so in this example I have a static
initialiser that gets the connection string from the config file and stores
that.

        private static string connectionString;
        static Dal()         {             connectionString =
                ConfigurationManager.AppSettings["ConnectionString"];         }

On each query a new connection object is created. The reason for this is that
the recommendation is that you acquire-query-release. Acquire a connection,
perform your query, then release the connection back to the pool.

            cmd.Connection.Open();
            int result = (int)cmd.ExecuteScalar();
            cmd.Connection.Close();

The connection Open / Close calls are not necessary when using a DataAdapter
as it will open the connection and then close it again in the Fill method. If
the connection was open before Fill is called then it will stay open.

There are two helper methods that are not publicly available. They are
BuildCommand and BuildBasicQuery.

BuildCommand creates the connection and command object and attaches the
connection to the command. It also tells the command the name of the stored
procedure that is to be called.

BuildBasicQuery uses BuildCommand, but then attaches the command to a
DataAdapter so that it can be used to obtain query results.

The full code of the sample DAL is below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace Cam.DataAbstractionLayer
{
    public static class Dal
    {
        private static string connectionString;

        /// <summary>
        /// The static initialiser will be called the first time any
        /// method on the class is called. This ensures that the
        /// connection string is available.
        /// </summary>
        static Dal()
        {
            connectionString =
                ConfigurationManager.AppSettings["ConnectionString"];
        }


        /// <summary>
        /// Builds the command object with an appropriate connection and
        /// sets the stored procedure name.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored
        /// procedure</param>
        /// <returns>The command object</returns>
        private static SqlCommand BuildCommand(string storedProcedureName)
        {
            // Create a connection to the database.
            SqlConnection connection = new SqlConnection(connectionString);

            // Create the command object - The named stored procedure
            // will be called when needed.
            SqlCommand result = new SqlCommand(storedProcedureName,
                connection);
            result.CommandType = CommandType.StoredProcedure;
            return result;
        }

        /// <summary>
        /// Builds a DataAdapter that can be used for retrieving the
        /// results of queries
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored
        /// procedure</param>
        /// <returns>A data adapter</returns>
        private static SqlDataAdapter BuildBasicQuery(
            string storedProcedureName)
        {
            SqlCommand cmd = BuildCommand(storedProcedureName);

            // Set up the data adapter to use the command already setup.
            SqlDataAdapter result = new SqlDataAdapter(cmd);
            return result;
        }

        /// <summary>
        /// A sample public method. There are no parameters, it simply
        /// calls a stored procedure that retrieves all the products
        /// </summary>
        /// <returns>A DataTable containing the product data</returns>
        public static DataTable GetAllProducts()
        {
            SqlDataAdapter dataAdapter = BuildBasicQuery("GetAllProducts");

            // Get the result set from the database and return it
            DataTable result = new DataTable();
            dataAdapter.Fill(result);
            return result;
        }

        /// <summary>
        /// A sample public method. It takes one parameter which is
        /// passed to the database.
        /// </summary>
        /// <param name="invoiceNumber">A number which identifies the
        /// invoice</param>
        /// <returns>A dataset containing the details of the required
        /// invoice</returns>
        public static DataSet GetInvoice(int invoiceNumber)
        {
            SqlDataAdapter dataAdapter = BuildBasicQuery("GetInvoice");
            dataAdapter.SelectCommand.Parameters.AddWithValue(
                "@invoiceNumber", invoiceNumber);

            DataSet result = new DataSet();
            dataAdapter.Fill(result);
            return result;
        }

        /// <summary>
        /// A sample public method. Creates an invoice in the database
        /// and returns the invoice number to the calling code.
        /// </summary>
        /// <param name="customerId">The id of the customer</param>
        /// <param name="billingAddressId">The id of the billing
        /// address</param>
        /// <param name="date">The date of the invoice</param>
        /// <returns>The invoice number</returns>
        public static int CreateInvoice(int customerId,
            int billingAddressId, DateTime date)
        {
            SqlCommand cmd = BuildCommand("CreateInvoice");
            cmd.Parameters.AddWithValue("@customerId", customerId);
            cmd.Parameters.AddWithValue("@billingAddressId",
                billingAddressId);
            cmd.Parameters.AddWithValue("@date", date);

            cmd.Connection.Open();
            int result = (int)cmd.ExecuteScalar();
            cmd.Connection.Close();
            return result;
        }
    }
}

So, that is that. A very basic introduction to creating a DAL.

Tags:

SQL Injection Attacks

Every day I see messages on various forums asking for help with SQL. Nothing wrong with that. People want to understand how something works, or have a partial understanding but something is keeping them from completing their task. However, I frequently also see messages that have SQL statements being built in C# or VB.NET that are extremely susceptible to injection attack. Sometimes it is from the original poster and, while they really need to learn to defend their systems, that is fine as they are trying to learn. Nevertheless there is also a proportion of people responding to these questions that give advice that opens up gaping security holes in the original poster’s system, if they follow that advice.

Consider this following example:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = string.Format("SELECT * FROM Customers WHERE Country='{0}'",
        countryName);
    SqlCommand cmd = new SqlCommand(commandText, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = String.Format( _
        "SELECT * FROM Customers WHERE Country='{0}'", _
        countryName)
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

What happens here is that what ever the value of countryName is will be inserted (injected, if you prefer) directly into the SQL string. More often than not I see examples of code on forums where there has been absolutely no checking done and the developer has used countryNameTextBox.Text directly in the string format or concatenation statement. In these cases just imagine what the effect of various unrestricted text box entries might be.

For instance, imagine the values a malicious user might put in the text box on a web form. What if they type ';DROP TABLE Customers;-- ?

That would expand the full SQL Statement passed by the .NET application to be

SELECT * FROM Customers WHERE Country='';DROP TABLE Customers; -- '

So, no more customers (at least in the database… But how long in real life?)

Some people might then say, sure, but who in their right mind would give that kind of access on a SQL Server to the ASP.NET account? If you ask that question then you cannot have seen the number of people who post code with the connection strings clearly showing that, firstly, they are using the sa account for their web application and, secondly, by posting their problem to a forum they have given to the world the password of their sa account.

Some others might say, yes yes yes, but wouldn’t an attacker would have to know what the overall SQL statement is before they can successfully inject something? Not so, I say. If you look at code posted on forums it becomes obvious that the vast majority of values from textboxes are inserted right after an opening apostrophe, like the example above. Based on that assumption, all an attacker needs to do is close the apostrophe, add a semi-colon and then inject the code they want. Finally, just to make sure that any remaining SQL from the original statement is ignored they add a couple of dashes (comment markers in SQL)

These defenders-of-bad-SQL-because-you-can-never-completely-secure-your-system-anyway-so-why-bother will often follow up with, okay okaay! But the attacker would have to know the structure of the database as well! Well, maybe not. Normally there are common table names. I’m sure most people that have been dealing with databases for a few years will have come across many with tables with the same names. Customers, Users, Contacts, Orders, Suppliers are common business table names. If that doesn’t work it may be possible to inject an attack on sysobjects. Often an attacker just gets lucky or notices a quirky output when entering something unusual and uses that to work on cracking the web site or database.

So here I present three tips for improving the security of your SQL Server database. In no particular order, they are: Use parameterised queries. Login using an appropriate account and grant only the permissions necessary. Use stored procedures.

* Using parameterised queries is really very simple, and it can make your code easier to read, and therefore to maintain. Parameters also have other advantages too (for instance you can receive values back from parameters, not just use them for sending information into the query). The previous code example can be changed very easily to use parameters. For instance:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = "SELECT * FROM Customers WHERE Country=@CountryName";
    SqlCommand cmd = new SqlCommand(commandText, conn);
    cmd.Parameters.Add("@CountryName",countryName);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = "SELECT * FROM Customers WHERE Country=@CountryName"
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    cmd.Parameters.Add("@CountryName", countryName)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

* The application should be set up to use a specific account when accessing the SQL Server. That account should then be given access to only the things it needs. For instance:

GRANT SELECT ON Customers TO AspNetAccount

It is generally unwise to GRANT permission ON someObject TO PUBLIC because then everyone has the permission.

* My final tip is to use only stored procedures for selecting and modifying data, because then the code that accesses the tables is controlled on SQL server. You then do not need to grant access directly to the tables, only the stored procedures that are called. The extra protection then comes by virtue of the fact that the only operations that can be performed are those that the stored procedures allow. They can perform additional checks and ensure that relevant related tables are correctly updated.

NOTE: This was rescued from the Wayback Machine. The original post was dated Saturday, 25th September 2004.

Tags:


Original posts:

Excellent post Colin. I’d always wondered what a SQL Injection attack was without actually bothering to Google for it – and now I know.

9/26/2004 6:08 AM | Rob

this is great job, colin. i’ve just know the words ‘SQl injection’ but dont know what it exactly means. now i’ve got it. i can defend my own database.

thanks

10/3/2004 3:41 PM | Fired Dragon

I’ve seen people who’ve read this article thinking they can’t do it because you’ve only given .NET syntax. You should probably emphasize that ‘classic’ ADO also has Command objects with a Parameters collection, as does the more obscure underlying OLE DB object model. You can also use parameters with ODBC. There’s no excuse – parameters are cleaner, more secure, and less prone to error than building a SQL string. The slight disadvantage is that the code becomes a little less portable – SQL Server uses the @param notation, Oracle uses :param, and the OleDbCommand and OdbcCommand both use positional parameters marked with a ?

12/18/2004 1:01 AM | Mike Dimmick

I just can’t get this to work. I do exactly as the example shows, and the query doesn’t replace the parameters with the values to search for. The query came up with nothing, until I entered a row in the database with the name of the parameter as a field. It finds that, so it just doesn’t parse the command. I don’t get it.

7/27/2005 10:46 AM | Vesa Ahola

Since I cannot see your code I cannot see what is going wrong. However, I wrote a longer article about the subject over on codeproject.com. Perhaps that may help. See:

http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp

7/27/2005 4:28 PM | Colin Angus Mackay
Follow

Get every new post delivered to your Inbox.

Join 25 other followers