Parameter Binding: What happens under the hood?

By : IceHeat
Source: Stackoverflow.com
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 a best practice. SQL injection attacks are minimized this way. But what exactly happens under the hood when you execute these statements? Is the end result still a SQL safe string? If not, what is the end result? And is this enough to prevent SQL injection attacks?

By : IceHeat


Answers

This will get you the TD element you want as a jQuery object:

var thIndex = $( '#tablica #bli' ).index (); // get index of TH in thead
var $td = $( $( '#dva td' ).get ( thIndex ) ); // get the TD in the target TR on the same index as is in the thead

By : Eli Courtwright



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
By : Kev


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

By : SQLMenace


This video can help you solving your question :)
By: admin