Kevin,
You don't have to change that much at all, actually.
Somewhere in your framework, you are taking values from objects and
inserting them into tables (or updating existing values). Somewhere in your
framework, you have the type of the column in the table which you are
converting the value to (you have to have this somewhere, or are doing a
mapping from the .NET type to the SQL type. Either way, you know something
about the schema of the column that you are updating/inserting into).
Now, with this, you create your SQL. Say you have a string value as a
property on an object which goes in a table. You do something along the
lines of this:
// The sql string.
string sql = "insert into MyTable ([Property]) values ('" +
myObject.StringValue + "')";
And then you place it in a command, and do this:
// The command.
SqlCommand command = new SqlCommand(sql, connection);
// Execute.
command.ExecuteNonQuery();
Now, say the value of the StringValue was this:
'); drop database MyDatabase; select ('
That would turn your SQL statement into:
insert into MyTable ([Property]) values (''); drop database MyDatabase;
select ('')
Then, when you call ExecuteNonQuery, your database is dropped. Of
course, this is a little bit of an outlandish example, since you should set
security appropriately so the logged in user doesn't have these rights. The
problem also exists for anything such as tables, columns (you can issue
alter table statements), etc, etc. Basically, ANY code can be injected into
your process this way.
Now, if you used parameters, the parameters/command take care of
formatting the values correctly so that the string passed in will be
formatted correctly (quotes become two quotes, indicating an escape for the
quote, etc, etc) and an injection attack will not occur.
So, in your case, instead of doing what you are doing, as you cycle
through the values (you are iterating through a schema of some kind, and
generating your statements based on the columns of the table), you could do
this:
// Create the command, as it will have to have parameters added to it.
SqlCommand command = new SqlCommand();
command.Connection = connection;
// Create the SQL string.
string sql = "insert into MyTable ([Property]) values (@stringValue)";
// Create the parameter.
SqlParameter parameter = new SqlParameter("@stringValue", SqlDbType.Char,
50);
// Set the value.
parameter.Value = myObject.StringValue;
// Add the parameter.
command.Parameters.Add(parameter);
// Execute.
command.ExecuteNonQuery();
Of course, your code will differ, but it's not that big of a deal if you
have the schema information already (which you have), and the value (which
you have).
And yes, you can convert the byte array to a string. I believe that you
have to get the hex value of each byte in the array and create a string from
that. Forgetting the security issues with injection attacks, why bother
writing the code to do it when it is already there for you?
--
- Nicholas Paldino [.NET/C# MVP]
-
mv*@spam.guard.caspershouse.com
"Kevin Lawrence" <sp******@spam.com> wrote in message
news:3a**************************@news.rmplc.co.uk ...
Kevin,
Use the parameter. Even if your SQL is produced on the fly, there
is no reason you can't place the parameter marker in the SQL, and then
create the parameter in the query.
And you should be able to do this easily as well, because you have
to have access to the value that you will place in the blob field, and
therefore, can set the value for the parameter. Unless, of course,
you don't know the types of the field, but you would have to do know
this as well, if you are generating the sql dynamically.
Also, the statement that your SQL being generated on the fly
making it impossible for injection attacks to occur is totally false.
If anything, you are more open to injection attacks as a result. The
reason for this is that writing the values out in SQL statement format
is a HUGE security gap, which is exactly what you are doing.
Use the parameters. You will reduce your codebase (because you
won't have to write code to convert values to SQL representations),
make it more maintainable (because the codebase is smaller), and make
it more secure in the process (using parameters virtually eliminates
the ability to conduct injection attacks).
We have already written a framework that stores businessobjects into the
database, this framework contains SQL generation by default, all I am
doing is expanding it to except types of byte[] and convert them into
string representations.
It's going to be a hell of a lot more work to re-work the framework and
change it's SQL generation rather than adapting what is there already, all
I really want to know is - is it possible?
Can you give an example of an injection attack and how it might ultimately
cause me a problem?
Thanks
Kev
