Issues retrieving binary data as string using DataReader

Data and Databases
1

I’m having trouble with a VB.NET function that reads user credentials from a database. When I try to convert a binary field to string, I get weird results. The database column is stored as BINARY type, but when I retrieve it through my data reader, the string conversion doesn’t work properly.

Sub LoadUserCredentials()
    Dim connectionStr As String = "server=" & serverName & ";database=" & dbName & ";uid=myuser;pwd=mypass;"
    
    Using conn As New SqlConnection(connectionStr)
        conn.Open()
        
        Dim query As String = "SELECT loginname, CAST(CONVERT(varchar, loginpass) AS varchar) AS 'loginpass' FROM accounts WHERE role='Administrator'"
        
        Using cmd As New SqlCommand(query, conn)
            Using reader As SqlDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Dim userName As String = reader("loginname").ToString()
                    Dim userPass As String = reader("loginpass").ToString() ' This line causes issues
                End While
            End Using
        End Using
    End Using
End Sub

The problem is that when I convert the binary password field to string, it shows up with missing characters or strange formatting. The output looks incomplete compared to what should be there. Has anyone dealt with binary to string conversion issues in SQL Server before?

2

hey, had similar issues! ya, binary data can be tricky. maybe try using System.Text.Encoding.UTF8.GetString() on the byte array directly? also, for security, hashing passwords with bcrypt is def a better approach. good luck!

3

Your problem is converting binary data wrong in the SQL query. When you convert BINARY to VARCHAR directly in SQL Server, you’ll get encoding issues because binary data has null bytes and non-printable characters that get chopped off or corrupted.

Don’t use CAST and CONVERT in your query. Instead, grab the binary data as a byte array with CType(reader("loginpass"), Byte()) in your VB.NET code, then convert it to string using System.Text.Encoding.UTF8.GetString(byteArray) or whatever encoding you need.

This way you keep all the binary data intact when pulling it from the database and handle the conversion properly in your app instead of at the database level.

4

hmm, curious - are you storing passwords as plain text in binary? what encoding did you use when inserting the data? check that first since conversion depends on the original encoding. also, why store passwords as binary instead of hashing them properly?