Selecting Multi-line strings through Entity Framework

Question

I am using Entity Framework 4 in C# (Yes I know I should upgrade but thats besides the point).

I have 70,000 private RSA Keys stored in a database The format is like this: Note - this is a demo key

-----BEGIN RSA PRIVATE KEY-----
MIICXQIDEMODEMODEMODEMODEMOEbGslt0N21onxRjBUwXFKI7CN6clxPAS+nEUK
BZAEiJUDJLqcGa37EZv40UQnmGKpLZO3ypee9qvKRVC4Q3Oi3S+Ukfet7eJYvCwr
WlneDOqvzyDz8cwlLcE2YhOnCMLYJ3v3YSt0n10oryaKyADDbCd5PmBbUwIEB7CO
+QKBgCtLDEMODEMODEMODEMODEMOnNVbWYfVLhFJpdS1+5dTZbkOg6zaP+A/VKf1w
3psFl7VfbggqiEvAi+4hTULVZu0W0wqPQUkw+rGzVT8Fg1uwBrzUKScJbscuRE/J
1I6iizZ0sudjUynn+EORc12p1NRslf2BUW3nFIognt849r9JAkEA1V+5aGmJLZPP
xZ/ETst7tb1WMeOvNxu+s/h2kcW6+WCvlUErYiB2/E/LkTbYGXu5/OHIMDgbQbB+
28Bu7lYmUwJBAM12343234235AEIDmvGJChsPOaqwnClV/fSEx/6E8DXMhDHTENA
STt+v4k1rlkSlM2aliZ8kz2u8K45SepsVwECQQCmECecqVtfuIvC+8EKvlM536OT
XrLIeDOkRak8yRqQFEZzAlOXkOtKIxHBJuVT39sPK+xzU30FwRdLE858eBphAkB+
LExh7ejTrxN3dHr1mDhJWoLzm59dzgwfmuEznnH+OmMZSAY21IouIAXjnKfXeCLo
fRvUv5EuQkvGUJi1/25JAkAXZy7yjsJxaNBGB9TxakoHHnI2yWI/R9yewhzLorNo
Oj49W6l9uaWaNxxmCrNZRIZoDEMODEMODEMODEMODEMO
-----END RSA PRIVATE KEY-----

Note: for some reason the END RSA line doesnt format properly on Stack Overflow.

However, when I try to select the key back through entity framework, I get only the following: -----BEGIN RSA PRIVATE KEY-----

I believed that this might be SQL Server (2008 in my case) terminating the string at the new line character ('\n') So I wrote a stored proc to try and replace it server side, before being returned to the calling application.

Ended up with something like this

Declare @str as nvarchar(max)
Declare @strReplaced as nvarchar(max)
set @str = (select PrivateKey from PrivateKeys where Id = @ID)
set @strReplaced = (select (REPLACE(@str, CHAR(10), '<<newline>>')))
select @strReplaced

When run on the sql server (the stored proc) - it gives me the private key with the appropriate <> replacements However when I execute the stored proc via the following C# code, it is still terminating the result on the first line of the key.

var str = o.ExecuteStoreQuery<string>("Execute dbo.GetPrivateKey " + Id.ToString());
Console.WriteLine(str.ToList()[0]);

Can someone suggest a solution to allow my C# code to get access to the whole RSA Key?

When executing my stored proc in the SQL Server Management Studio: I get -----BEGIN RSA PRIVATE KEY-----<<newline>>MIICXwIBAAKBgQDuCFrUx55g9yqezNvzSpX0YegUDnrp0IP7jsMab0NVqAgl/azO<<newline>>qbp5SNe10mAYJNDsxBS0AHYSOrAHApgKPgWTKOE5gFUKxaB1eJgIDEMODEMODEMODEMO<<newline>>0PU0Kma2/8Znv23JFUDrO3Sk8Ft++aT8crCs45ygnJVNSdgsU0MDEMODEMODEMODEMO<<newline>>awKBgAXv6PqRNvFuEg5Pt+0IjwTMEHLqPBPEh5k2eS1aRYKyl/T40fe5SaXiMxWb<<newline>>f8SUoLXDPdOdkXqBqZfOs94hjtk6mRDZuBN9MWL7F4D+b6/n4s+YDEMODEMODEMODEMO<<newline>>kEr7DrtF/n/kDrH/Pty6pyHCfGLVLMtG6cFYIPncyYzViLTDAkEA+riR/e+elYVe<<newline>>bc+KPq9NlSXAFz21R00puCUJhrVBNcVZnKw2hV8D9Ie4b+WMaPcGh/7gHAiUC84o<<newline>>JpLA8EjGPQJBAPMLZGRwcvNiUW7gl2EBIq33gDZvefzQC4kr5J2SFLaA4lX/wKMJ<<newline>>ymHJdjQlmo5nKd7qdmg0DwbUSOjCcux/RpkCQQDXde/XX9kIxD9NQ2wV0DhQ4LFU<<newline>>J/OXlxZ2yD+Tnr7lFFN/QghU8Vuc0G4eRPR2kg5X6FBya3nQ5miDgl8j76pDAkEA<<newline>>pud2zrTMxBul8BDP4+gqll18jcRt0sV7fERvaMT+0gL/M2QamRevgd22ipKbBAUV<<newline>>P4f1Jtj6aFqVQ9n8RewtuwJBAL4iLMt/GWg71PBQhPlZlkDlmDGoDF1Ou6MYlONv<<newline>>z24JA/EMdx3Jx1/WG7xyzqmEJcVGk/8O/NWtP5DXQEFBLnw=<<newline>>-----END RSA PRIVATE KEY-----<<newline>>

When executing the stored proc via C# code: `string sConnectionString = "InsertConnectionStringHere"; using (var conn = new System.Data.SqlClient.SqlConnection(sConnectionString)) using (var command = new System.Data.SqlClient.SqlCommand("GetPrivateKey", conn) { CommandType = CommandType.StoredProcedure

                        })
                        {
                            conn.Open();
                            command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DomainID", vTransaction.Domain.Id));
                            var vReader = command.ExecuteReader();
                            vReader.Read();
                            var vResult = vReader.GetValue(0);
                            Console.WriteLine(vResult);
                            conn.Close();
                        }`

It gives me -----BEGIN RSA PRIVATE KEY----- Its like whatever the cause is, is affecting SQL at a low level. So it impacts both direct raw SQL and the Entity Framework that rests on top of it.

Edit -- On closer inspection, I am using SQL Server Express- does it have limitations that might affect the strings returned?

EDIT---- I modified the Stored Proc to convert the text field to Binary data and then send it to the caller. via this code select CONVERT(VARBINARY(max), @str )

It works on the SQL Server, but when called remotely via the C# code, it only gets 62 bytes of data, when converted to string - once again, those bytes construct the '-----BEGIN RSA PRIVATE KEY-----'

I am at a loss....

Answer 1

Use Regex

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;


namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            string key =
                "-----BEGIN RSA PRIVATE KEY-----\n" +
                "MIICXQIDEMODEMODEMODEMODEMOEbGslt0N21onxRjBUwXFKI7CN6clxPAS+nEUK\n" +
                "BZAEiJUDJLqcGa37EZv40UQnmGKpLZO3ypee9qvKRVC4Q3Oi3S+Ukfet7eJYvCwr\n" +
                "WlneDOqvzyDz8cwlLcE2YhOnCMLYJ3v3YSt0n10oryaKyADDbCd5PmBbUwIEB7CO\n" +
                "+QKBgCtLDEMODEMODEMODEMODEMOnNVbWYfVLhFJpdS1+5dTZbkOg6zaP+A/VKf1w\n" +
                "3psFl7VfbggqiEvAi+4hTULVZu0W0wqPQUkw+rGzVT8Fg1uwBrzUKScJbscuRE/J\n" +
                "1I6iizZ0sudjUynn+EORc12p1NRslf2BUW3nFIognt849r9JAkEA1V+5aGmJLZPP\n" +
                "xZ/ETst7tb1WMeOvNxu+s/h2kcW6+WCvlUErYiB2/E/LkTbYGXu5/OHIMDgbQbB+\n" +
                "28Bu7lYmUwJBAM12343234235AEIDmvGJChsPOaqwnClV/fSEx/6E8DXMhDHTENA\n" +
                "STt+v4k1rlkSlM2aliZ8kz2u8K45SepsVwECQQCmECecqVtfuIvC+8EKvlM536OT\n" +
                "XrLIeDOkRak8yRqQFEZzAlOXkOtKIxHBJuVT39sPK+xzU30FwRdLE858eBphAkB+\n" +
                "LExh7ejTrxN3dHr1mDhJWoLzm59dzgwfmuEznnH+OmMZSAY21IouIAXjnKfXeCLo\n" +
                "fRvUv5EuQkvGUJi1/25JAkAXZy7yjsJxaNBGB9TxakoHHnI2yWI/R9yewhzLorNo\n" +
                "Oj49W6l9uaWaNxxmCrNZRIZoDEMODEMODEMODEMODEMO\n" +
                "-----END RSA PRIVATE KEY-----\n";

            Regex expr = new Regex("-.*$", RegexOptions.Multiline);
            key = expr.Replace(key, "").Trim();

        }
    }
}
​