Geek Answers Handbook

Linking parameters: what happens under the hood?

.NET, Java, and other high-level database APIs in different languages ​​often provide methods known as prepared statements and parameter bindings rather than sending text commands to the database server. What I would like to know is what happens when you execute the statement as follows:

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

+6
c # sql database api
source share
3 answers

The MySQL manual page in prepared statements contains a lot of information (which should apply to any other DBMS).

Basically, your statement is parsed and processed ahead of schedule, and parameters are sent separately, and not processed together with SQL code. This eliminates SQL injection attacks as SQL is parsed before the parameters are even set.

+5
source share

If you are using MS SQL, download the profiler and you will see which SQL commands are generated when using parameterized queries. Here is an example (I am using Enterprise Libary 3.1, but the results are the same as 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 quote symbols 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
0
source share

in layman's terms: if a prepared statement is sent, then the database will use the plan, if it is available, it does not need to recreate the plan every time this request is sent, but only the parameter values ​​have changed. this is very similar to how procs works, an additional advantage with procs is that you can only give permission through procs and not to base tables in general

0
source share

More articles:


All Articles