Tuesday, February 22, 2011

Bind Variables

I have been answering a lot of questions on Stack Overflow where I've referenced Bind Parameters.  So what are Bind Parameters?  First, let's look at a really simple way to insert some records into a database (I'll be using C# for this, but this concept is available in many platforms / languages).

Here is the old school, plane Jane way to do this:
string sql = "INSERT INTO CUSTOMERS " +
             " (FirstName, LastName)"
             " VALUES " + 
             " ('Jack', 'Shepard') ";

using(var command = new SqlCommand(sql, connection))
{
   command.ExecuteNonQuery();
}


So what's wrong with it?  Well, a few things:
  • Security: This approach is susceptible to SQL Injection Attack.  Just concatenating strings from the user and passing them to the database is a risky endeavor.  This risk is multiplied when releasing an application on the web.
  • Performance: Most RDBMs will evaluate a new execution plan for each of these statements.  In other words, if you insert 100 customers, the execution plan will be calculated 100 times.  In some circumstances, this time is greater than the time it actually takes to perform the statement.
  • Robustness:  You have to remember to escape the apostrophe and format dates correctly.  These are the kinds of problems that don't show up until runtime and sometimes don't show up until an edge case is hit.
Enter Bind Parameters
Here's another way to implement the same thing:

string sql = "INSERT INTO CUSTOMERS " +
             " (FirstName, LastName)"
             " VALUES " + 
             " (@FirstName, @LastName) ";

using(var command = new SqlCommand(sql, connection))
{
   SqlParameter param;

   param = new SqlServerParameter("@FirstName", SqlDbType.VarChar, 50);
   param.Value = "Jack";
   
   command.Parameters.Add(param);

   param = new SqlServerParameter("@LastName", SqlDbType.VarChar, 50);
   param.Value = "Shepard";
   
   command.Parameters.Add(param);

   command.ExecuteNonQuery();
}

Or, a much more terse version:

string sql = "INSERT INTO CUSTOMERS " +
             " (FirstName, LastName)"
             " VALUES " + 
             " (@FirstName, @LastName) ";

using(var command = new SqlCommand(sql, connection))
{
   SqlParameter param;

   command.Parameters.Add(new SqlServerParameter("@FirstName", SqlDbType.VarChar, 50)) { .Value = "Jack" });
   command.Parameters.Add(new SqlServerParameter("@LastName", SqlDbType.VarChar, 50)) { .Value = "Shepard" });

   command.ExecuteNonQuery();
}


Obviously, this example is geared towards SQL Server.  Doing the same thing on other database platforms is usually extremely easy.   The biggest differences between the platforms involve naming conventions for the parameters.  While DB2 OleDB allows the exact same parameter name syntax as above, DB2 over ODBC requires a different syntax.

No matter what syntax your particular RDMS requires, Bind Parameters are an excellent way to guard against sql injection attacks, user input and increase performance.

      

No comments:

Post a Comment