Passing parameters to database

ChkNet posted on Friday, January 08, 2010

Hi, i was watching the new videos (which are incidentally pretty good except the pictures of code could be sharper in places).

So i new to CSLA.

In Data Access video. When passing parameters to DB for insert a compound SQL statement is used. Similar to this

cm.commandtext =
"insert into TestData(name,city) values(@name,@city) ; select scope_identity()"

cm.parameters.Add..Value ("@name",name);
cm.parameters.Add..Value ("@city",city);

etc..

It is also said that using this technique prevents sql injections which for a store procedure would be correct.

What i am not sure about is whether this security feature working correctly?

The reason is that the SQL statement used is itself a compound statement.

The very thing this is supposed to be stopping!

Any explaination on this would be much appreciated.

JonnyBee replied on Friday, January 08, 2010

Hi,

The commandtext is a "constant" and when using parameters and letting the Command object handle how text+parameters is sent to the database in secure way - really does prevent possible SqlInjection.  The very point is that command text is NOT merged into a sqlstring when using this technique. Try running the sample with SqlProfiler attached and you can see how it is actually sent to the DB.

If, on the other hand, you write:
string sql = "insert into TestData(name,city) values('" + name + "', '" + city + "'); select scope_identity()";
you would be exposed to SqlInject if the city is f.ex
var city ="'; delete * from tablename; --"

the resulting text would be a valid sql that would delete all rows from a table.

ajj3085 replied on Friday, January 08, 2010

Using the Parameter object will properly escape the user supplied data.  Sql injection occurs when you blindly tack strings together, without properly escaping the user supplied data.