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_t ext, qlink_default_n otes) VALUES ('"
+ ButtonCaption + "', '" + DefaultStartTim e + "', '" + DefaultEndTime + "',"
+ imgArr + ", " + Convert.ToInt16 (AllDay) + ", " + Convert.ToInt16 (AMOnly)
+ ", " + Convert.ToInt16 (PMOnly) + ", '" + DefaultDescript ion.Replace("'" ,
"''")
+ "', '" + DefaultNotes.Re place("'", "''") + "');";

SqlCommand sbCMD = new SqlCommand(sbSQ L, dbConn);
try
{
sbCMD.ExecuteSc alar();
}

But it fails when the query executes, because in my SQL, imgArr has been
replaced by "System.Byt e[]" (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]

Re: Insert Byte array to SQL Server using string

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(P roperties.Setti ngs.Default.Con Key))
using (SqlCommand cmd = conn.CreateComm and()) {
cmd.Parameters. Add("@id", System.Data.Sql DbType.Int).Val ue =
1; // just a row marker
SqlParameter paramData = cmd.Parameters. Add("@data",
System.Data.Sql DbType.Image); // the binary
SqlParameter paramAppend = cmd.Parameters. Add("@append",
System.Data.Sql DbType.Bit); // replace or append?
paramAppend.Val ue = false; // first pass should replace
existing
cmd.CommandText = "mgtsave";
cmd.CommandType = System.Data.Com mandType.Stored Procedure;
cmd.Prepare();
const int SQL_IMAGE_BUFFE R_SIZE = 8040; // optimal chunk
size
byte[] buffer = new byte[SQL_IMAGE_BUFFE R_SIZE];
int bytesRead;
conn.Open();
while((bytesRea d = input.Read(buff er, 0,
SQL_IMAGE_BUFFE R_SIZE)) 0) {
if(bytesRead==S QL_IMAGE_BUFFER _SIZE) { // pass the
filled buffer
paramData.Value = buffer;
} else { // didn't fill an entire buffer
byte[] smallBuffer = new byte[bytesRead];
Buffer.BlockCop y(buffer, 0, smallBuffer, 0,
bytesRead);
paramData.Value = smallBuffer;
}
cmd.ExecuteNonQ uery();
paramAppend.Val ue = true; // subsequent calls should
append data
totalBytes += bytesRead;
}
conn.Close();
input.Close();
}
Console.WriteLi ne(totalBytes);
Console.ReadLin e();

}

[Hans Kesting]

Re: Insert Byte array to SQL Server using string

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]

Re: Insert Byte array to SQL Server using string


"Hans Kesting" <news.2.hansdk@ spamgourmet.com wrote in message
news:mn.0ae17d6 bf15dc7ee.43821 @spamgourmet.co m...

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

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]

Re: Insert Byte array to SQL Server using string

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 "parameteri sed 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]

Re: Insert Byte array to SQL Server using string

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.To String(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.

--
Dave Sexton

"james" <james@com.comw rote in message
news:45487372$0 $2434$db0fefd9@ news.zen.co.uk. ..

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_t ext, qlink_default_n otes) VALUES ('"
+ ButtonCaption + "', '" + DefaultStartTim e + "', '" + DefaultEndTime + "',"
+ imgArr + ", " + Convert.ToInt16 (AllDay) + ", " + Convert.ToInt16 (AMOnly)
+ ", " + Convert.ToInt16 (PMOnly) + ", '" + DefaultDescript ion.Replace("'" ,
"''")
+ "', '" + DefaultNotes.Re place("'", "''") + "');";
>
SqlCommand sbCMD = new SqlCommand(sbSQ L, dbConn);
try
{
sbCMD.ExecuteSc alar();
}
>
But it fails when the query executes, because in my SQL, imgArr has been
replaced by "System.Byt e[]" (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.

[james]

Re: Insert Byte array to SQL Server using string


"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:eHklA5c$GH A.1464@TK2MSFTN GP02.phx.gbl...

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.To String(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]

Re: Insert Byte array to SQL Server using string

Thanks it worked out!
james wrote:

"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:eHklA5c$GH A.1464@TK2MSFTN GP02.phx.gbl...

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.To String(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.