Insert Byte array to SQL Server using string

[james]

Hi,
I am trying to insert a record into a database table, one field of which is
a byte array.

Using the below:

Byte[] imgArr;

.... <code to put image into imgArr>

String sbSQL = "INSERT INTO qlink (qlink_name, qlink_start, qlink_end,
qlink_image, qlink_allday, "
+ "qlink_am, qlink_pm, qlink_default_text, qlink_default_notes) VALUES ('"
+ ButtonCaption + "', '" + DefaultStartTime + "', '" + DefaultEndTime + "',"
+ imgArr + ", " + Convert.ToInt16(AllDay) + ", " + Convert.ToInt16(AMOnly)
+ ", " + Convert.ToInt16(PMOnly) + ", '" + DefaultDescription.Replace("'",
"''")
+ "', '" + DefaultNotes.Replace("'", "''") + "');";

SqlCommand sbCMD = new SqlCommand(sbSQL, dbConn);
try
{
sbCMD.ExecuteScalar();
}

But it fails when the query executes, because in my SQL, imgArr has been
replaced by "System.Byte[]" (i.e. what the "ToString()" method would do)
rather than the array of bytes...
So, I understand why it's doing it, but how do I get around it? Googling
I've seen mention of using command builders and datasets or datatables and
parameters and so on, but it seems like a load more work!

James.

 

[Marc Gravell]

You should probably look at parameterised queries; also, I suspect
ExecuteNonQuery may work better.

Can I copy a reply from a while back, which discussed efficient insertion of
image (byte[]) data into SqlServer (in this case from a stream)? This is
intended for illustration only; obviously streams are different to arrays;
you *may* be able to get away with simply throwing the entire byte[] at the
server in one go... maybe...

/* Corresponding SP:

ALTER PROC mgtsave @id int, @data image, @append bit = 1
AS
DECLARE @ptr binary(16)
IF @append = 0 -- need to put in some empty data (not null) for
TEXTPTR to work
UPDATE MGT
SET data = ''
WHERE id = @id

SELECT @ptr = TEXTPTR(data)
FROM MGT
WHERE id = @id

IF @append = 1
UPDATETEXT MGT.data @ptr NULL 0 @data
ELSE
WRITETEXT MGT.data @ptr @data

*/
static void Main() {
long totalBytes = 0;
using (FileStream input = File.OpenRead(@"C:\Out.pdf")) //
random file
using (SqlConnection conn = new
SqlConnection(Properties.Settings.Default.ConKey))
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value =
1; // just a row marker
SqlParameter paramData = cmd.Parameters.Add("@data",
System.Data.SqlDbType.Image); // the binary
SqlParameter paramAppend = cmd.Parameters.Add("@append",
System.Data.SqlDbType.Bit); // replace or append?
paramAppend.Value = false; // first pass should replace
existing
cmd.CommandText = "mgtsave";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Prepare();
const int SQL_IMAGE_BUFFER_SIZE = 8040; // optimal chunk
size
byte[] buffer = new byte[SQL_IMAGE_BUFFER_SIZE];
int bytesRead;
conn.Open();
while((bytesRead = input.Read(buffer, 0,
SQL_IMAGE_BUFFER_SIZE)) > 0) {
if(bytesRead==SQL_IMAGE_BUFFER_SIZE) { // pass the
filled buffer
paramData.Value = buffer;
} else { // didn't fill an entire buffer
byte[] smallBuffer = new byte[bytesRead];
Buffer.BlockCopy(buffer, 0, smallBuffer, 0,
bytesRead);
paramData.Value = smallBuffer;
}
cmd.ExecuteNonQuery();
paramAppend.Value = true; // subsequent calls should
append data
totalBytes += bytesRead;
}
conn.Close();
input.Close();
}
Console.WriteLine(totalBytes);
Console.ReadLine();

}

 

[Hans Kesting]

Hi,
....

So, I understand why it's doing it, but how do I get around it? Googling I've
seen mention of using command builders and datasets or datatables and
parameters and so on, but it seems like a load more work!

James.

As you are searching the web, search also for "sql command injection
attack" for some reasons why it's not such a good idea to build your
own sql-command-strings this way. This attack will not work if you use
parameters.

Hans Kesting

 

[james]

As you are searching the web, search also for "sql command injection
attack" for some reasons why it's not such a good idea to build your own
sql-command-strings this way. This attack will not work if you use
parameters.

Hans Kesting

I'm already a bit aware of that from some php stuff I did a while back. This
particular issue is a Winforms app rather than a web one, so hopefully it
shouldn't be such a problem, although Marc's post points towards using
stored procedures and params as well, so it's probably the way to go.
James.

 

[Marc Gravell]

winforms doesn't protect against this, *except* that winforms are generally
intranet based, so you have a *slightly* less hostile client. But it still
doesn't stop people putting in surnames as "o'neil", or more hostile
injection attacks.

The "sp" vs "parameterised text query" debate is one that will run and run.
And run. Personally I like SPs as they give me more granular security and
object promotion processes. Other people like parameterised SQL built via
(for instance) C#. I don't personally get that excited about this dilemma!
Horses for courses.

But yup; don't go near string concatenation unless you *really* know what
you are doing and what every allowed input is (and enforce this...). This
can be useful e.g. when the column name is selected at runtime, so can't be
done as a SQL parameter (sp or otherwise).

Marc

 

[Dave Sexton]

Hi James,

What you would want to do, if you decide to stick with a textual query, is to
convert the Byte[] into a string of byte values that are formatted in
Hexidecimal:

string imgStr = "0x" + BitConverter.ToString(imgArr).Replace("-",
string.Empty);

The imgStr value should not be quoted when inserted into the textual query
(like you would do for a varchar, for instance).

Note: If you have large images or just a large number of them there are more
efficient ways to get a hex string from the Byte[] - just search for "dotnet
Byte[] format Hex" in google groups and you'll find some posts. For example,
I've seen people use a hex-lookup table in memory.

I agree with Marc, however, that you may want to think about using
parameterized queries or stored procedures if only because you may forget to
escape the ' character from time to time, but it will probably help
performance as well, make your code more legible and probably make debugging
easier.

 

[james]

Hi James,

What you would want to do, if you decide to stick with a textual query, is
to convert the Byte[] into a string of byte values that are formatted in
Hexidecimal:

string imgStr = "0x" + BitConverter.ToString(imgArr).Replace("-",
string.Empty);

The imgStr value should not be quoted when inserted into the textual query
(like you would do for a varchar, for instance).

Note: If you have large images or just a large number of them there are
more efficient ways to get a hex string from the Byte[] - just search for
"dotnet Byte[] format Hex" in google groups and you'll find some posts.
For example, I've seen people use a hex-lookup table in memory.

I agree with Marc, however, that you may want to think about using
parameterized queries or stored procedures if only because you may forget
to escape the ' character from time to time, but it will probably help
performance as well, make your code more legible and probably make
debugging easier.

Thanks for that Dave - I already changed the code to use parameters
yesterday and it seems to insert fine - nice to know there was a way to do
it in the "original" fashion too though. At the moment, I am storing a few
32x32 images, so it's not massive amounts of data (it's basically storing an
icon to be displayed on a button, these are configurable by the user, and
stored in the DB to eliminate file path problems etc...)

James.

 

[Raj,Zara]

Thanks it worked out!

Hi James,

What you would want to do, if you decide to stick with a textual query, is
to convert the Byte[] into a string of byte values that are formatted in
Hexidecimal:

string imgStr = "0x" + BitConverter.ToString(imgArr).Replace("-",
string.Empty);

The imgStr value should not be quoted when inserted into the textual query
(like you would do for a varchar, for instance).

Note: If you have large images or just a large number of them there are
more efficient ways to get a hex string from the Byte[] - just search for
"dotnet Byte[] format Hex" in google groups and you'll find some posts.
For example, I've seen people use a hex-lookup table in memory.

I agree with Marc, however, that you may want to think about using
parameterized queries or stored procedures if only because you may forget
to escape the ' character from time to time, but it will probably help
performance as well, make your code more legible and probably make
debugging easier.

Thanks for that Dave - I already changed the code to use parameters
yesterday and it seems to insert fine - nice to know there was a way to do
it in the "original" fashion too though. At the moment, I am storing a few
32x32 images, so it's not massive amounts of data (it's basically storing an
icon to be displayed on a button, these are configurable by the user, and
stored in the DB to eliminate file path problems etc...)

James.