How to pass Byte Array in a Query using C#?

Question

I'm trying to insert bytes of byte array in the database. using following code.

String query = String.Format(@"INSERT INTO [Documents]
                              ([InsertedBy], [DocumentName], [Document])
                              VALUES
                              ('{0}','{1}',{2})",
                              insertedBy, docName, docBytes);

Cmd.CommandText = query;
Cmd.ExecuteNonQuery();

Following exception is occured:

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. Incorrect syntax near ''.

I'm not getting what the reason is.

What are the types of these columns? What are the values of your `insertedBy`, `docName` and `docBytes` exactly? — Soner Gönül, Feb 15 '14 at 11:29
insertedBy is int, documentname is varchar(100) and Document is VarBinary(max) — Salman, Feb 15 '14 at 11:30
I would always insert the varbinary as a parameter in this way: http://stackoverflow.com/a/1088630/812598, since it seems to be a raw `byte[]` type by it's name. — GoRoS, Feb 15 '14 at 11:53
You should not code SQL this way, it is susceptible to [SQL Injection Attacks](http://en.wikipedia.org/wiki/Sql_injection_attack). — Erik Philips, Feb 15 '14 at 12:37
I second that. Don't forge SQL queries yourself. Use ADO.NET or some similar library that do the translation to the underlying Database dialect — Tseng, Feb 15 '14 at 13:47

score 5 · Accepted Answer · answered Feb 15 '14 at 14:35

Never use string concatenation or string functions to make parametrized queries.

Also, because (I suspect that) docBytes is a byte[], string concatenation will not have the results that you hope for.

Here is how I would do it:

private static void InsertDocument(SqlCommand cmd, int insertedBy, string docName, byte[] docBytes)
{
    cmd.CommandText = @"INSERT INTO [Documents]
                        ([InsertedBy], [DocumentName], [Document])
                        VALUES
                        (@insertedBy,@docName,@docBytes)";
    cmd.Parameters.Add("insertedBy", SqlDbType.Int).Value = insertedBy;
    // Note: consider using `nvarchar` instead of `varchar`;
    cmd.Parameters.Add("docName", SqlDbType.VarChar, 100).Value = docName;
    // Note: -1 maps to the nvarchar(max) length;
    cmd.Parameters.Add("docBytes", SqlDbType.VarBinary, -1).Value = docBytes;

    // The following call presupposes that the associated `SqlConnection` is open
    cmd.ExecuteNonQuery();
}

Soner Gönül · Answer 2 · 2014-02-15T11:33:54.517

1

If your insertedBy column is an int, you don't need to use single quotes with it. Because you are try to insert characters to your int typed column.

Just use it like;

string query = String.Format(@"INSERT INTO [Documents]
                              ([InsertedBy], [DocumentName], [Document])
                              VALUES
                              ({0},'{1}',{2})",
                              insertedBy, docName, docBytes);

But since we don't know your values, this is the only suggestion I have.

edited Feb 15 '14 at 11:33

answered Feb 15 '14 at 11:27

Soner Gönül

97,193
102
206
364

I'm not an expert but I would assume `docBytes` could be a problem too, e.g. if it's a `byte[]`. The error message in the question might indicate something like that. – Dirk Feb 15 '14 at 11:37
same error even after removing the quotations, this is for sure that those quotation are not the cause of problem – Salman Feb 15 '14 at 12:46

How to pass Byte Array in a Query using C#?

2 Answers2