C# : how to upload image to a SQL Server database using varbinary(Max)

Question

I have been trying to upload images to my database, but I get this error:

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

Here is my code:

con.Open();

cmd = new SqlCommand("SELECT * FROM ImagePosts", con);
ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);

int id = ds.Tables[0].Rows.Count + 1;

byte[] buffer = File.ReadAllBytes(file);

SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', '" + textBox1.Text + "', '" + "@image" + "')", con);

var binary1 = cmd2.Parameters.Add("@image", SqlDbType.VarBinary, -1);
binary1.Value = buffer;

cmd2.ExecuteNonQuery();
con.Close();
this.Close();

Edit: my bad, I forgot to remove the parenthesis around @image.

Leaving aside the actual error here, I don't think it is wise to store files in a database. Why don't you use the filesystem instead, and store the filepaths in the database? https://stackoverflow.com/a/13421029/1666620 — user1666620, Apr 06 '20 at 15:22
remove the single quotes around the `@image` parameter. and use the parameter approach for the textBox1.Text too. — Cee McSharpface, Apr 06 '20 at 15:29
@user1666620 two reasons pro: transactional integrity, one-file-contains-all backup. two reasons contra external storage and path reference: external paths can move, and server may not always have access to them unless (5 not obvious conditions are met), and would require a separate logic to safely write and retrieve them. — Cee McSharpface, Apr 06 '20 at 15:38
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Apr 06 '20 at 16:37
And don't just parameterize SOME of the column values - do that for ALL of the them! — SMor, Apr 06 '20 at 17:18

score 2 · Answer 1 · answered Apr 06 '20 at 15:41

The issue is that you are inserting '@image' when it should just be @image. If you put '', that is saying you want to insert the value "@image" into the field. Drop the '' and it should work. However, I would also recommend doing the same thing for the Textbox.Text or else you can get Sql Injected:

           con.Open();
        cmd = new SqlCommand("SELECT * FROM ImagePosts", con);
        ad = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        ad.Fill(ds);
        int id = ds.Tables[0].Rows.Count + 1;


        byte[] buffer = File.ReadAllBytes(file);
        SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', @Title, @image)", con);
        cmd2.Parameters.Add("@Title", textBox1.Text);
        var binary1 = cmd2.Parameters.Add("@image", SqlDbType.VarBinary, -1);
        binary1.Value = buffer;
        cmd2.ExecuteNonQuery();
        con.Close();
        this.Close();

You could even do it with the ID one, too.

mituw16 · Answer 2 · 2020-04-06T15:33:54.960

0

You don't need to wrap varbinary data in single quotes in sql server. Try this

SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', '" + textBox1.Text + "',  @image)", con);

edited Apr 06 '20 at 15:33

answered Apr 06 '20 at 15:29

mituw16

5,126
3
23
48

(why? it would mean the string `'@image'`, which is implicitely a varchar, instead of the `@image` parameter's value, which at the same time explains the error message OP gets) – Cee McSharpface Apr 06 '20 at 15:31
I don't understand what you're asking....but varbinary data is not encapsulated by single quotes in sql server – mituw16 Apr 06 '20 at 15:32
1

I felt that your expression "don't need to" is too weak and does not explain much: it's not optional - it's the cause for the error they're seeing. this is a suggestion to add the explanation to your answer. And while OP is at fixing their statement, they should remove the injection vulnerability that `textBox1.Text` is, too. – Cee McSharpface Apr 06 '20 at 15:34

C# : how to upload image to a SQL Server database using varbinary(Max)

2 Answers2