473,383 Members | 1,984 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

Insert Byte array to SQL Server using string

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.

Nov 1 '06 #1
7 33236
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();

}

Nov 1 '06 #2
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
Nov 1 '06 #3

"Hans Kesting" <ne***********@spamgourmet.comwrote in message
news:mn***********************@spamgourmet.com...
>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.

Nov 1 '06 #4
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
Nov 1 '06 #5
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.

--
Dave Sexton

"james" <ja***@com.comwrote in message
news:45**********************@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_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.

Nov 1 '06 #6

"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:eH**************@TK2MSFTNGP02.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.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.

Nov 2 '06 #7
Thanks it worked out!
james wrote:
"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:eH**************@TK2MSFTNGP02.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.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.
Dec 14 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jarrod Sharp | last post by:
Using the VB6 Winsock control in VB.NET the GetData(byRef Data as Object) method of the control returns an Object. I have used this code that works but am not familiar with .NET...
1
by: Paul W | last post by:
How do I do this with: Option Strict On? I know of the function System.Text.ASCIIEncoding.GetChars, but that takes a Byte array not a DataRow. So with Option Strict On, how do I take the byte...
4
by: David Bargna | last post by:
Hi I have a problem, I have a string which needs to be converted to a byte array, then have the string representation of this array stored in an AD attribute. This string attribute then has to...
1
by: ZorpiedoMan | last post by:
There MUST be an easier way... I have a byte array I want to convert to a string. I do NOT want to have characters above hex7h converted to a question mark (that is what...
2
by: Jaime Stuardo | last post by:
Hi all... I'm trying to retrieve a SQLXML query using VB.NET. When I programmed in VB 6.0, I used Stream object to accomplish this which was trivial. I cannot do the same thing in VB.NET. Here...
10
by: Danny | last post by:
I am working on a project where I will receive xml documents from clients machines as a byte array. They will use the web browser navigate method to post the data to my ASP.NET page. I then pick up...
10
by: Sayudh27 | last post by:
Hello all, I used multicasting and received compressed data packets over the network.I decompressed the packets at a specified memory block 'in'(declared as lzo_byte *in)....Now,i...
1
by: Jo | last post by:
TIA for the help!
2
by: TaeMike | last post by:
Hello, I have some varchar(2000) fields in my tables, and they have a lot of "weird" characters in them including line break and carriage returns, etc. When I do a select, I see the entire string of...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.