Including BLOB vaules in INSERT SQL

[Kevin Lawrence]

Hi all

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

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

Re: Including BLOB vaules in INSERT SQL

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.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"Kevin Lawrence" <spamthis@spam. com> wrote in message
news:3a030fc310 42c8c7fa0d761fc 5f9@news.rmplc. co.uk...[color=blue]
> Hi all
>
> I want to do "INSERT INTO Table (Blob) Values('blobdat aasstring')".
>
> ..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
>
>
>[/color]

[Kevin Lawrence]

Re: Including BLOB vaules in INSERT SQL

> Kevin,[color=blue]
>
> 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.[/color]

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
[color=blue]
>
> "Kevin Lawrence" <spamthis@spam. com> wrote in message
> news:3a030fc310 42c8c7fa0d761fc 5f9@news.rmplc. co.uk...
>[color=green]
>> Hi all
>>
>> I want to do "INSERT INTO Table (Blob) Values('blobdat aasstring')".
>>
>> ..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[/color][/color]

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

Re: Including BLOB vaules in INSERT SQL

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

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m


"Kevin Lawrence" <spamthis@spam. com> wrote in message
news:3a030fc310 47a8c7fa1614ebd dca@news.rmplc. co.uk...[color=blue][color=green]
>> 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.[/color]
>
> 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
>[color=green]
>>
>> "Kevin Lawrence" <spamthis@spam. com> wrote in message
>> news:3a030fc310 42c8c7fa0d761fc 5f9@news.rmplc. co.uk...
>>[color=darkred]
>>> Hi all
>>>
>>> I want to do "INSERT INTO Table (Blob) Values('blobdat aasstring')".
>>>
>>> ..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[/color][/color]
>
>[/color]

[Kevin Lawrence]

Re: Including BLOB vaules in INSERT SQL

> Kevin,[color=blue]
>
> 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).[/color]

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]]

Re: Including BLOB vaules in INSERT SQL

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.String Value + "')";

And then you place it in a command, and do this:

// The command.
SqlCommand command = new SqlCommand(sql, connection);

// Execute.
command.Execute NonQuery();

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.Connect ion = 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.String Value;

// Add the parameter.
command.Paramet ers.Add(paramet er);

// Execute.
command.Execute NonQuery();

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]
- mvp@spam.guard. caspershouse.co m



"Kevin Lawrence" <spamthis@spam. com> wrote in message
news:3a030fc310 49b8c7fa18946f4 19e@news.rmplc. co.uk...[color=blue][color=green]
>> 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).[/color]
>
> 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
>
>[/color]

[sdbillsfan@gmail.com]

Re: Including BLOB vaules in INSERT SQL

> We have already written a framework that stores businessobjects into the[color=blue]
> 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 .
>[/color]

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]

Re: Including BLOB vaules in INSERT SQL

> Kevin,[color=blue]
>
> 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.String Value + "')";
> And then you place it in a command, and do this:
>
> // The command.
> SqlCommand command = new SqlCommand(sql, connection);
> // Execute.
> command.Execute NonQuery();
> 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.Connect ion = 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.String Value;
> // Add the parameter.
> command.Paramet ers.Add(paramet er);
> // Execute.
> command.Execute NonQuery();
> 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?[/color]

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