Parameter Binding: What happens under the hood?

Question

.NET, Java and other high level database API's in various language often provide techniques known as prepared statements and parameter binding as opposed to sending plain text commands to the Database server. What I would like to know is what happens when you execute a statement like this:

SqlCommand cmd = new SqlCommand("GetMemberByID");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@ID", memberID);
para.DbType = DbType.Integer;
cmd.Parameters.Add(param);

I know this is an best-practise, SQL injection attacks are minimized this way. But what exactly happens under to hood when you execute these statements? Is the end result still a SQL safe string? Or what is the end result? And is this enough to prevent SQL injection attacks?

Answer 1

The MySQL manual page on prepared statements provides lots of information (which should apply to any other RDBMS): http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html

Basically, your statement is parsed and processed ahead of time, and the parameters are sent separately instead of being handled along with the SQL code. This eliminates SQL-injection attacks because the SQL is parsed before the parameters are even set.

Answer 2

If you're using MS SQL, load up the profiler and you'll see what SQL statements are generated when you use parameterised queries. Here's an example (I'm using Enterprise Libary 3.1, but the results are the same using SqlParameters directly) against SQL Server 2005:

string sql = "SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did";
Database db = DatabaseFactory.CreateDatabase();
using(DbCommand cmd = db.GetSqlStringCommand(sql))
{
  db.AddInParameter(cmd, "DomName", DbType.String, "xxxxx.net");
  db.AddInParameter(cmd, "Did", DbType.Int32, 500204);

  DataSet ds = db.ExecuteDataSet(cmd);
}

This generates:

exec sp[underscore]executesql N'SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did',
  N'@DomName nvarchar(9),
  @Did int',
  @DomName=N'xxxxx.net',
  @Did=500204

You can also see here, if quotation characters were passed as parameters, they are escaped accordingly:

db.AddInParameter(cmd, "DomName", DbType.String, "'xxxxx.net");

exec sp[underscore]executesql N'SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did',
  N'@DomName nvarchar(10),
  @Did int',
  @DomName=N'''xxxxx.net',
  @Did=500204

Answer 3

in layman terms: if a prepared statement is sent then the DB will use a plan if it is available, it doesn't not have to recreate a plan every time this query is sent over but only the values of the params have changed. this is very similar to how procs work, the additional benefit with procs is that you can give permission through procs only and not to the underlying tables at all