An in-depth study of SQL injection problem

If you write the background access to the database method, splicing SQL statements, then the problem of SQL injection must have been encountered, and its surface is the user input data, in the process of splicing SQL statements, beyond the data itself, become a part of the SQL statement query logic, Compared to the solution to this problem, already very mature, I was lazy directly cut a diagram to post a solution, citing http://www.cnblogs.com/SkySoot/archive/2012/07/16/2593907.html

　

The problem came, I encountered this problem in the project, is the use of parametric command to solve the problem, it will be time tight without kung Fu study, now think, in the end is why can be avoided by the following way, if the ID is also stitched into an offensive SQL statement, why will not produce injection problems.

String sql = "SELECT * from the person where ID = @ID";

SqlCommand cmd = new SqlCommand (SQL, conn);

Cmd. Parameters.addwithvalue ("@ID", ID);

Look at this problem first to see SqlCommand compiling principle, although this can be imagined is a long way, but I think the programming still need to have a probe into the heart, even if not understand, everything at the beginning difficult.

I through the ilspy to see the source of the next SqlCommand, from the excutenonquery has been down, my idea is that the incoming parameters has a place to call, although a lot of parameters I do not necessarily know the principle of the But as soon as I see how to deal with parameter, I can find what I want, so I looked down, at least 5 layers down, found this method:

private void Buildexecutesql (CommandBehavior behavior, string commandtext, sqlparametercollection parameters, ref _ SQLRPC RPC)

{

int num = this. Countsendableparameters (parameters);

int num2;

if (num > 0)

{

num2 = 2;

}

Else

{

num2 = 1;

}

This. Getrpcobject (num + num2, ref RPC);

Rpc. ProcID = 10;

rpc.rpcname = "sp_executesql";

if (CommandText = = null)

{

CommandText = this. GetCommandText (behavior);

}

SqlParameter SqlParameter = new SqlParameter (null, (commandtext.length << 1 <= 8000)? SqlDbType.NVarChar:SqlDbType.NText, commandtext.length);

Sqlparameter.value = CommandText;

Rpc.parameters[0] = SqlParameter;

if (num > 0)

{

string text = this. Buildparamlist (This._stateobj.parser, this. Batchrpcmode? Parameters:this._parameters);

SqlParameter = new SqlParameter (null, (text). Length << 1 <= 8000)? SqlDbType.NVarChar:SqlDbType.NText, text. Length);

Sqlparameter.value = text;

RPC.PARAMETERS[1] = SqlParameter;

BOOL Inschema = Commandbehavior.default! = (behavior & commandbehavior.schemaonly);

This. Setuprpcparameters (RPC, num2, Inschema, parameters);

}

}

Everybody see this yellow place, I instantly understand, essentially in C # call class Library Execute SQL statement, in fact, are walking sp_executesql This command, familiar with SQL cheese know, sp_executesql This command is used to deal with dynamic SQL, In fact, the numeric parameterization, the dynamic SQL to be executed will not change, that is, will not recompile, but the parameters are constantly changing. This solves my question, in fact C # does not do too much processing of SQL statements, but rather the corresponding statements and parameters to the SQL processing.

This is the first article I wrote in the blog Park, this technical point is actually not particularly advanced, but I suddenly encountered in the process of the problem, and then the Internet search did not correspond to the answer, so want to study carefully, no matter how it is written, I hope I and everyone can have an exploration of the essence of the spirit, Instead of just staying on the surface, write it down and share my thoughts with you, thank you.
 　　 
If there is something wrong, I hope you correct me, thank you.

Finally stick a piece of code that I think is more useful in my research

private void Setuprpcparameters (_SQLRPC rpc, int startcount, bool Inschema, sqlparametercollection parameters)
{
int parametercount = this. GetParameterCount (parameters);
int num = Startcount;
Tdsparser parser = This._activeconnection.parser;
BOOL Isyukonornewer = parser. Isyukonornewer;
for (int i = 0; i < parametercount; i++)
{
SqlParameter SqlParameter = parameters[i];
Sqlparameter.validate (i, commandtype.storedprocedure = = This.commandtype);
if (!sqlparameter.validatetypelengths (isyukonornewer). ISPLP && sqlparameter.direction! = ParameterDirection.Output)
{
SQLPARAMETER.FIXSTREAMDATAFORNONPLP ();
}
if (Sqlcommand.shouldsendparameter (SqlParameter))
{
Rpc.parameters[num] = SqlParameter;
if (sqlparameter.direction = = Parameterdirection.inputoutput | | sqlparameter.direction = = parameterdirection.output)
{
Rpc.paramoptions[num] = 1;
}
if (sqlparameter.direction! = ParameterDirection.Output && Sqlparameter.value = = null && (!inschema | | sqldbtype.structured = = Sqlparameter.sqldbtype))
{
byte[] Expr_b4_cp_0 = rpc.paramoptions;
int expr_b4_cp_1 = num;
Expr_b4_cp_0[expr_b4_cp_1] |= 2;
}
num++;
}
}
}

An in-depth study of SQL injection problem