Inserting byte array into SQL Server

Question

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

When I hover over the SQL statement it looks like below:

 string sql_insert_string = "Insert into images_table(image_id,     image_byte_array) values ('123', System.Byte[])

One of the insert value is a byte array as shown above. The variable has value in the byte array, say like byte[6738] . But after the sql_insert_string is constructed, it comes as System.Byte[]. The image_byte_array column type is varbinary(max). The database is SQL Server 2008. Because of this the database throws the following error:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as \"\" or [] are not allowed. Change the alias to a valid name.

Your sql string builder just call ToString() on your variable of type byte[]. Show method which create a sql query string — Fabio
– Fabio, Commented Jul 27, 2016 at 4:15
You shouldn't be constructing SQL statements - you should be using parameters to avoid SQL injection attacks! — marc_s
– marc_s, Commented Jul 27, 2016 at 4:16
SqlParameter not only saves you from SQL Injection, in addition you will not have such a problem, because all input values will will be properly "converted" to SqlParameters — Fabio
– Fabio, Commented Jul 27, 2016 at 4:18

Sumit Maingi · Accepted Answer · 2016-07-27 04:37:07Z

you can insert the byte array like so:

        private void FireSql(byte[] input)
        {
            const string 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 expecting

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

Deepak · Accepted Answer · 2016-07-27 04:52:40Z

0

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.

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

answered Jul 27, 2016 at 4:52

Deepak

1197 bronze badges

Comments

ydoow · Accepted Answer · 2016-07-27 04:28:31Z

-4

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.

answered Jul 27, 2016 at 4:28

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

Inserting byte array into SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related