How to insert byte array into SQL table?

Question

I try insert byte array in sql table, data type in table is VarBinary(Max), but it finish with this error:

System.Data.SqlClient.SqlException: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Any adice, I am beginner with Sql...

Here is my code:

   byte[] key;
   byte[] iv;

   void generateTDES()
   {
       TripleDESCryptoServiceProvider tdes;
       tdes = new TripleDESCryptoServiceProvider();
       tdes.KeySize = 128;
       key = tdes.Key;
       iv = tdes.IV;
   }


    public void WriteUser(string _GUID, string _Name, string _Pass)
    {
        generateTDES();
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        sql = "Insert into MembersTable (GUID,Name,Pass,CryptKey)"
        + "VALUES('" + _GUID + "','" + _Name + "','" + _Pass + "','" +key + "');";
        SqlCommand cmdIns = new SqlCommand(sql, conn);
        cmdIns.ExecuteNonQuery();
        conn.Close();
    }

Luke Girvin · Accepted Answer · 2011-09-18 14:41:58Z

3

You will maintain more control over the types, protect yourself from injection attacks, and enjoy slightly better performace by parameterizing your query as follows:

    sql = "Insert into MembersTable (GUID,Name,Pass,CryptKey)"
    + "VALUES(@guid, @name, @pass, @key);";

    SqlCommand cmdIns = new SqlCommand(sql, conn);

    SqlParameter _guidParam = new SqlParameter("@guid", DbType.UniqueIdentifier);
    _guidParam.Value = _GUID;

    cmdIns.Parameters.Add(_guidParam);

    // Repeat for other parameters, specifying the appropriate types

    cmdIns.ExecuteNonQuery();

edited Sep 18, 2011 at 14:41

Luke Girvin

13.5k10 gold badges69 silver badges87 bronze badges

answered Sep 17, 2009 at 10:58

GalacticCowboy

11.8k2 gold badges46 silver badges69 bronze badges

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

3 Comments

Faiz Over a year ago

True! But is there a type that can handle varbinary?

GalacticCowboy Over a year ago

There's also an overload on the constructor where you can specify a field length; for example, new SqlParameter("@name", DbType.NVarChar, 128);

GalacticCowboy Over a year ago

The type you want for a varbinary is, appropriately enough, "DbType.VarBinary". In order to support more than 8000 bytes you have to explicitly specify the type as I did above.

Faiz · Accepted Answer · 2009-09-17 11:07:50Z

-1

Prepare a hex string from the binary value and use it in the query for concatenation. a hex string will look like 0x123456789ABCDEF

Your final code will be like,

hexStr = "0x1234FFCD5";
sql = "Insert into MembersTable
(GUID,Name,Pass,CryptKey)"
         + "VALUES('" + _GUID + "','" + _Name + "','" + _Pass + "','"
+ hexStr + "');";

Check the code for constructing hex from binary here

For Each bytTemp As Byte In bytAllBytes
  'For your exact requirements
  strHex &= bytTemp.ToString("X2") & " "
  'however, you can use "X4" or "Xn" where replacing "n" with a numeric value will pad that number of zeros in the begining
Next

Sorry, I speak VB... :)

edited Sep 17, 2009 at 11:07

answered Sep 17, 2009 at 10:52

Faiz

5,46311 gold badges48 silver badges60 bronze badges

1 Comment

Eamon Nerbonne Over a year ago

This may work, but you're much better off parametrizing the query instead.

Collectives™ on Stack Overflow

How to insert byte array into SQL table?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related