byte[] to String not executing in query

Question

I have the following code. What I am passing to the function GetInsert is the ID which then creates the INSERT Statement. Note that _Image1 is of type byte[]. Image1 is of type IMAGE in the database table. Note that the way I am doing below in terms of outputting a string I am kind of stuck with as it is part of a large piece of code.

    public string GetInsert(string ID)
    {

      System.Text.StringBuilder stringBuilder = new System.Text.StringBuilder();

      stringBuilder .Append("INSERT INTO tblMain(ID,Image1) VALUES (");

      stringBuilder .Append((ID) + ", ");
      stringBuilder .Append(ByteToString(_Image1) + ")");

      return stringBuilder.ToString();

    }

Because the above code needs to be converted to a string, I converted _Image1 to a string as shown below. Please let me know if this is the correct approach.

    public string ByteToString(byte[] bytes)
    {

        if(bytes == null)
        {

            return "NULL";

        }
      else
      {
          return "'" + System.Text.Encoding.UTF8.GetString(bytes) + "'";

      }

   }

Then elsewhere in the code I am doing the following to execute the insert statement:

    command.CommandText = obj1.GetInsert(sID);
    command.ExecuteNonQuery();

The issue that I am running into is that when I do it as shown above the string that is converted is not in a way that is recognized when I the code tries to execute it in the ExecuteNonQuery. Please let me know if my approach is correct. Any suggest would be helpful

What kind of column is Image1 in SQL? VARCHAR(MAX), VARBINARY(MAX), IMAGE, something else? — Dan Field
– Dan Field, Commented Sep 8, 2015 at 17:57
I would suggest to use parameterized query and pass the byte array to type _image1 — Adil
– Adil, Commented Sep 8, 2015 at 17:57

Jonatas W. Gonçalves · Accepted Answer · 2015-09-08 18:04:05Z

3

Try to use command params insted of using StringBuilder and convert byte do string.

see microsoft SqlCommand.Parameters docs

Your code should be like this:

command.CommandText = "INSERT INTO tblMain(ID,Image1) VALUES (@Id,@Image)";
command.Parameters.AddWithValue("@Id", sID);
command.Parameters.AddWithValue("@Image", _Image1);
command.ExecuteNonQuery();

answered Sep 8, 2015 at 18:04

Jonatas W. Gonçalves

2972 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evk · Accepted Answer · 2015-09-08 18:03:32Z

1

To make it work you need to fix like this:

public string ByteToString(byte[] bytes)
{

    if(bytes == null)
    {

        return "NULL";

    }
  else
  {
      return "0x" + BitConverter.ToString(bytes).Replace("-", "");
  }

}

BUT doing it this way you are asking for many troubles like sql injections. Just use parametrized queries instead.

answered Sep 8, 2015 at 18:03

Evk

102k8 gold badges156 silver badges204 bronze badges

Collectives™ on Stack Overflow

byte[] to String not executing in query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related