Inserting Byte Array Into Sql Server

I am constructing a sql_insert_string to be used in Microsoft.ApplicationBlocks.Data.SqlHelper to be used as follows: SqlHelper.ExecuteNonQuery(Transaction, CommandType.Text, sql_i

Solution 1:

you can insert the byte array like so:

privatevoidFireSql(byte[] input)
        {
            conststring sql_insert_string =
                "Insert into images_table(image_id, image_byte_array) values (@image_id, @image_byte_array)";

            SqlTransaction transaction = null; //wherever you get the transaction obj from.var imageIdParam = new SqlParameter("@image_id", SqlDbType.Int, 4)
            {
                Direction = ParameterDirection.Input,
                Value = 123
            }; //change the data type to whatever data type you are expectingvar byteParam = new SqlParameter("@image_byte_array", SqlDbType.VarBinary)
            {
                Direction = ParameterDirection.Input,
                Size = input.Length,
                Value = input
            }; //change the data type to whatever data type you are expecting

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql_insert_string, imageIdParam, byteParam);
        }

I would suggest looking at an ORM (https://en.wikipedia.org/wiki/Object-relational_mapping) like Entity Framework(http://www.asp.net/entity-framework) to do all of this for you while increasing security and future changes much easier.

Solution 2:

You should be using the Parameters while constructing the SQL Query which obviously will avoid SQL Injection attacks. How your queries are getting constructed is still unclear here. Something like this should do it for you.

SqlParametersParam=newSqlParameter("@image_byte_array", SqlDbType.VarBinary)
{
 Value = image
};
SqlHelper.ExecuteNonQuery(Transaction, CommandType.Text, sql_insert_string, sParam)

Solution 3:

You may use

string sql_insert_string = 
    String.Format("INSERT INTO images_table(image_id, image_byte_array) VALUES ('123', CAST('{0}' AS VARBINARY(MAX)))", System.Byte[].ToString());

And yes, as @marc_s commented, you shouldn't be constructing SQL statements as a security concern.