Including BLOB vaules in INSERT SQL

[Kevin Lawrence]

Hi all

I want to do "INSERT INTO Table (Blob) Values('blobdataasstring')".

...rather than using the parameter driven method, is it possible? And if so
what encoder do I use to convert the bytes to string format?

Thanks
Kev

 

[Nicholas Paldino [.NET/C# MVP]]

Kevin,

Why on earth would not want to use the parameter? The parameter will
not only do the work for you, but it will also prevent SQL injection
attacks.

 

[Kevin Lawrence]

Kevin,

Why on earth would not want to use the parameter? The parameter
will not only do the work for you, but it will also prevent SQL
injection attacks.

Because our SQL is generated on the fly, there's no possible chance of injection
attacks.

Is it possible or am I forced to use the parameter?

Thanks
Kev

 

[Nicholas Paldino [.NET/C# MVP]]

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).

 

[Kevin Lawrence]

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

 

[Nicholas Paldino [.NET/C# MVP]]

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]
- (e-mail address removed)

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

 

[sdbillsfan]

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.

Why on earth would you convert the byte[] to string representations
before inserting them? That right there is argument enough to use
parameters.

 

[Kevin Lawrence]

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?

Thanks for that - however I am already preventing injection attacks by escaping
the ' using '', so I don't really think there's much risk - plus of course
like you say the permissions in the database prevent anything serious from
happening.

How will performance be affected using parameterised queries? What if I have
50 INSERT's to perform, normally I would have each INSERT in a string list
- join them up with ; and then execute the lot in a batch, how will this
differ with p queries?

Thanks
Kev