How to insert base64string to varbinary type column sql sever

Question

I have base64string (image logo), which I want to insert in db. In database the column type is varbinary. my query is

update Organization set MobileLogo='gjdgfkjgk...' where orgId=1676

The below error I am getting

Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Code to convert image to base64

string imagePath = @"C:\Users\arvind.ch\Downloads\Icons\SISClient_iCons\Enrich.png";
string imgBase64String = GetBase64StringForImage(imagePath);

Here I want to store the image-

I need to have sql query to inset it to db not C# code. The URL's given above is all about C#

base 64 is a way of taking *arbitrary binary data* and making it storable/transmittable as a string. And you have a `varbinary` column which is capable of storing *arbitrary binary data*. Do you maybe think you're missing the obvious here? — Damien_The_Unbeliever, Sep 11 '18 at 07:36
Don't convert to base 64 / *decode* the base 64 to get back to arbitrary binary data. Then pass that to SQL Server directly in a `varbinary` parameter and *avoid* treating it as a string at all. It's most natural representation is binary data. *store that*. — Damien_The_Unbeliever, Sep 11 '18 at 07:41
Post the actual code that stores the image. Not the code that converts it to a string. — Panagiotis Kanavos, Sep 11 '18 at 07:54

score 2 · Answer 1 · answered Sep 11 '18 at 07:43

2

Two things are needed to answer this properly:

First, let's answer your question about how to store the object. You've already made a varbinary column, so don't bother with the Base64 conversion, as a varbinary column can store arbitrary data already. Read the file into a byte array and then send that to the database.

Second, and probably what is making this seem difficult to do, is that you need to parameterize your SQL. This is for two reasons: 1) To allow you to send the byte array in the first place and 2) To prevent SQL injection attacks, which are the easiest vulnerabilities to defend against.

Consider the following code, which will do both:

using ( SqlConnection con = new SqlConnection ("your connection string"))
using ( SqlCommand com = new SqlCommand("UPDATE Organization SET MobileLogo=@FileData WHERE YourKeyColumn=@YourKeyValue", con) { CommandType = CommandType.StoredProcedure } )
{
    con.Open();
    com.Parameters.AddWithValue("@FileData", yourByteArray);
    com.Parameters.AddWithValue("@YourKeyValue", yourKeyValue);
    com.ExecuteNonQuery();
}

answered Sep 11 '18 at 07:43

dodexahedron

4,584
1
25
37

@dodexahedron- Thank u. But I am using Sql-server to insert image to db. Using C# to convert image to base64. – R15 Sep 11 '18 at 07:45
2

@Arvindraja *don't* use C# to convert the binary data to a string. `varbinary(max)` is a *binary* type. Base64 is a *string*. Store the binary data in the varbinary(max) field – Panagiotis Kanavos Sep 11 '18 at 07:48
Yeah! now trying to convert image to binary. – R15 Sep 11 '18 at 07:50
1

@Arvindraja why convert anything? Just load the image file. Perhaps you should clarify your question and post the actual code you are using? – Panagiotis Kanavos Sep 11 '18 at 07:52
The image is *already* binary. Don't convert it to a string. Just read the file into a byte array and be done with it. – dodexahedron Sep 11 '18 at 08:02

Paweł Dyl · Answer 2 · 2018-09-11T07:56:46.600

1

SQL Server told you everything:

Use the CONVERT function to run this query.

Here is udated query:

update Organization set MobileLogo=convert(varbinary(MAX),'gjdgfkjgk...') where orgId=1676

You could also convert values on client side using Convert.FromBase64String:

var varbinaryData = Convert.FromBase64String("gjdgfk45vbgu");

UPDATE after question update:

To read file as byte array, use:

var imagePath = @"C:\Users\arvind.ch\Downloads\Icons\SISClient_iCons\Enrich.png";
var byteArray = File.ReadAllBytes(imagePath);

Then pass this byteArray as query parameter.

edited Sep 11 '18 at 07:56

answered Sep 11 '18 at 07:34

Paweł Dyl

8,888
1
11
27

When I am doing this, getting error- Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'TestDb' is full due to 'LOG_BACKUP'. – R15 Sep 11 '18 at 07:36
https://learn.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017 – Paweł Dyl Sep 11 '18 at 07:39
Convert.FromBase64String("gjdgfk45vbgu"), this line giving me array of binary like 03 56 66 77... how can I collect it? – R15 Sep 11 '18 at 07:51

How to insert base64string to varbinary type column sql sever

2 Answers2