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.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]
- mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx



"Kevin Lawrence" <spamthis@xxxxxxxx> wrote in message
news:3a030fc31049b8c7fa18946f419e@xxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: User Login via SQL Server...(ASP Pages)...
    ... Even if your code did work, it would leave you vulnerable to SQL ... Injection attacks. ... a stored procedure that returns 0 or 1 if the user is found in the ... >because my SQL string is whack? ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Including BLOB vaules in INSERT SQL
    ... Even if your SQL is produced on the fly, ... is no reason you can't place the parameter marker in the SQL, ... you are more open to injection attacks as a result. ... 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 byteand convert them into string representations. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Books reviewed
    ... Roff (a heckuva ADO reference). ... > the Compact Framework can't be left out of any must have list. ... > Not everyone using the compact Framework will be using SQL Server CE,but ...
    (microsoft.public.dotnet.framework)