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, Feb 13 '20 at 21:30
You need to show us the code from the point where you know you have the full 72,000 characters as somewhere between there and the save you have a variable only 4,000 chars long. — Dale K, Feb 13 '20 at 21:32
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, Feb 13 '20 at 21:33
Unrelated - Why write to a file first and then to a session variables? Also why write to a session variable? — Igor, Feb 13 '20 at 21:56
@igor i wrote to a session variable in order to retrieve in another controller where the model class containing the base64string is to be passed to the api for updating — upsidedownwf, Feb 13 '20 at 21:59
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: https://stackoverflow.com/questions/11897950/how-do-you-view-all-text-from-an-ntext-or-nvarcharmax-in-ssms (for example). Try writing the data to a file and opening from there. — Jonathan, Feb 13 '20 at 22:32
You haven't showed the code where you are updating the database - which is the important code in this case. — Dale K, Feb 13 '20 at 22:58
@DaleK i just passed the base64 string to a model class and sent it to an api, i do not have the source code for the api. but while debugging, directly before i called the update api endpoint i retrieved the base64 string and the character count is correct, i even converted the string back to image using an online decoder and got the correct image. i have done my investigation and i have been able to isolate the truncating to occur at the sql side — upsidedownwf, Feb 13 '20 at 23:17
Sure, but once you send the data via the API there are multiple possibilities for what is going wrong, so we can't help you unless we can see that. The API code somehow sends the data to SQL Server, the API code could be using the wrong datatype to send the data. Maybe the API uses a stored procedure to carry out the update, in that case the stored procedure code could be wrong. All you know is that you have the correct column datatype in the end table. But SQL Server itself doesn't do any truncating, its the code which updates it which is incorrect. And without access to that we cannot help. — Dale K, Feb 13 '20 at 23:20
@Igor i have change 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 — upsidedownwf, Feb 13 '20 at 23:49
so it appears the string i get from my select query result is misleading, the get request returns the complete base 64 string — upsidedownwf, Feb 13 '20 at 23:51

score 4 · Accepted Answer · edited Feb 13 '20 at 22:59

4

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 '20 at 22:59

Dale K

25,246
15
42
71

answered Feb 13 '20 at 21:35

FoggyDay

11,962
4
34
48

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 – upsidedownwf Feb 13 '20 at 21:51
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? – FoggyDay Feb 13 '20 at 23:06
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 Feb 13 '20 at 23:11
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. – upsidedownwf Feb 13 '20 at 23:52
Try changing the column to TEXT data type – elDoctorWho Dec 15 '22 at 15:40

score 0 · Answer 2 · edited Apr 06 '22 at 11:38

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;
    }
}

Saving file base64 string in SQL Server database

2 Answers2