Saving file base64 string in SQL Server database

Question

I working on a file upload, on upload the file is converted to base64 string and this string is sent to an API that saves the string in a SQL Server database table.

The type of the base64 string column in the table is NVARCHAR(MAX) but I noticed when the file is saved, SQL truncates the string such that when you convert online using base64 to image decoder, it produces just part of the whole picture and I noticed that the base64 string before saving to SQL is about 72,000 in character count but after saving it SQL it is reduced to about 4,000 which is responsible for the incomplete image decoded.

Why does SQL truncate the base64 string and what can I do to help my situation?

Here is my base64 conversion code:

public async Task<IActionResult> UploadFile()
{
    string base64string;
    var myFile = Request.Form.Files["claimFile"];
    var filetype = myFile.ContentType;
    var filepath = Path.GetTempFileName();
    using (var stream = System.IO.File.Create(filepath))
    {
        await myFile.CopyToAsync(stream);
    }
    byte[] imageByte = System.IO.File.ReadAllBytes(filepath);
    base64string = Convert.ToBase64String(imageByte);
    HttpContext.Session.SetString("Base64Str", base64string);
    return new EmptyResult();
}

It's not trunicating it, Base64 is just text (ASCII encoding). Save it as VARCHAR(MAX), there's nothing numeric about Base64. — Trevor
– Trevor, Commented Feb 13, 2020 at 21:30
Sounds like either your database mapping layer is truncating or the database itself is truncating the value. 4000 is the maximum limit for nvarchar when it is restricted (not max) so the fact that your database value has 4000 characters is not a coincidence. There is no reason to use nvarchar though, use varchar instead. — Igor
– Igor, Commented Feb 13, 2020 at 21:33
Unrelated - Why write to a file first and then to a session variables? Also why write to a session variable? — Igor
– Igor, Commented Feb 13, 2020 at 21:56
How are you getting the base64 out of SQL? Through SSMS? SSMS is probably only displaying the first 8000 characters, even though the full value is in the database. see: stackoverflow.com/questions/11897950/… (for example). Try writing the data to a file and opening from there. — Jonathan
– Jonathan, Commented Feb 13, 2020 at 22:32

Dale K · Accepted Answer · 2020-02-13 22:59:28Z

3

I suspect the problem might be that, by specifying NVARCHAR (16-byte characters), you're inadvertently "corrupting" the string.

TWO SUGGESTIONS:

Redefine the column as VARCHAR(MAX)
Save a uuencoded string, then read the text back and see if the saved/retrieved string values match.

Look here:

https://dba.stackexchange.com/questions/212160/why-do-i-get-incorrect-characters-when-decoding-a-base64-string-to-nvarchar-in-s

Please post back what you find!

Also - out of curiosity - how are you doing the Base64 encoding in the first place?

edited Feb 13, 2020 at 22:59

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 13, 2020 at 21:35

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

upsidedownwf Over a year ago

thanks for your reply i have updated my question with the encoding logic and i have also tried using VAR(MAx), the string was truncated to 8000 character count

paulsm4 Over a year ago

So you're uuencoding to a C#/.Net string. Q: Where are you writing that string to MSSQL? And why are you bothering with session variables? Can't you pass your base64 string to an object that can write it to the database directly?

upsidedownwf Over a year ago

i stored in a session because my update controller is in another class, i retrieved it an passed it to a model class object which i then sent to the Api, i don't have the source code for the api, but while debugging ,directly before the object enters the update api endpoint i retrieved the value from the object and it was the correct character count , i even tried converting it image back online and it gave me the proper image

upsidedownwf Over a year ago

i have changed the data type of the database column to VARCHAR(MAX) and i just noticed that if i run a query to get the string and copy it from the result, i get a truncated string but if i right click the table and "edit top 200 rows" and go to that particular base64 string, though the input box appears empty if i Ctrl+A and Ctrl+C i get the full base64 string which i have converted to get the proper image. Even on my application i have made a get request for the base64 string to the api and the string gotten is complete too, i have tested it too.

elDoctorWho Over a year ago

Try changing the column to TEXT data type

Mark Schultheiss · Accepted Answer · 2022-04-06 11:38:14Z

I read and write to db but to nText column type with the following just fine: you may convert from vb.net to c# if needed.

#Region "write/read files to db"

Public Function SaveToDB(ByVal fullfilename As String) As String
    Dim filedata = Convert.ToBase64String(File.ReadAllBytes(fullfilename))
    Return filedata
End Function

Public Function LoadFromDB(ByVal filedata As String, ByVal fullfilename As String) As String
    Dim filepath As String = (fullfilename)
    File.WriteAllBytes(filepath, Convert.FromBase64String(filedata))
    Return filepath
End Function
#End Region

C# version:

class SurroundingClass
{
    public string SaveToDB(string fullfilename)
    {
        var filedata = Convert.ToBase64String(File.ReadAllBytes(fullfilename));
        return filedata;
    }

    public string LoadFromDB(string filedata, string fullfilename)
    {
        string filepath = (fullfilename);
        File.WriteAllBytes(filepath, Convert.FromBase64String(filedata));
        return filepath;
    }
}

Collectives™ on Stack Overflow

Saving file base64 string in SQL Server database

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related