postgresql - NpgsqlException：错误：22P02：json 类型的输入语法无效

Question

我有以下 JSON 字符串：

{"packedRecipeType":"DynamicCharacterAvatar","name":"Character","race":"HumanFemale","dna":[{"dnaType":"DynamicUMADna","dnaTypeHash":1932961400,"packedDna":"{\"bDnaAsset\":{\"instanceID\":108582},\"bDnaAssetName\":\"HumanFemaleDNA 1\",\"bDnaSettings\":[{\"name\":\"height\",\"value\":180},{\"name\":\"headSize\",\"value\":128},{\"name\":\"headWidth\",\"value\":57},{\"name\":\"neckThickness\",\"value\":128},{\"name\":\"armLength\",\"value\":128},{\"name\":\"forearmLength\",\"value\":128},{\"name\":\"armWidth\",\"value\":128},{\"name\":\"forearmWidth\",\"value\":128},{\"name\":\"handsSize\",\"value\":128},{\"name\":\"feetSize\",\"value\":128},{\"name\":\"legSeparation\",\"value\":128},{\"name\":\"upperMuscle\",\"value\":51},{\"name\":\"lowerMuscle\",\"value\":128},{\"name\":\"upperWeight\",\"value\":51},{\"name\":\"lowerWeight\",\"value\":128},{\"name\":\"legsSize\",\"value\":128},{\"name\":\"belly\",\"value\":128},{\"name\":\"waist\",\"value\":128},{\"name\":\"gluteusSize\",\"value\":128},{\"name\":\"earsSize\",\"value\":178},{\"name\":\"earsPosition\",\"value\":128},{\"name\":\"earsRotation\",\"value\":128},{\"name\":\"noseSize\",\"value\":96},{\"name\":\"noseCurve\",\"value\":115},{\"name\":\"noseWidth\",\"value\":102},{\"name\":\"noseInclination\",\"value\":128},{\"name\":\"nosePosition\",\"value\":134},{\"name\":\"nosePronounced\",\"value\":128},{\"name\":\"noseFlatten\",\"value\":134},{\"name\":\"chinSize\",\"value\":115},{\"name\":\"chinPronounced\",\"value\":134},{\"name\":\"chinPosition\",\"value\":204},{\"name\":\"mandibleSize\",\"value\":118},{\"name\":\"jawsSize\",\"value\":64},{\"name\":\"jawsPosition\",\"value\":137},{\"name\":\"cheekSize\",\"value\":96},{\"name\":\"cheekPosition\",\"value\":255},{\"name\":\"lowCheekPronounced\",\"value\":128},{\"name\":\"lowCheekPosition\",\"value\":255},{\"name\":\"foreheadSize\",\"value\":83},{\"name\":\"foreheadPosition\",\"value\":140},{\"name\":\"lipsSize\",\"value\":137},{\"name\":\"mouthSize\",\"value\":73},{\"name\":\"eyeRotation\",\"value\":124},{\"name\":\"eyeSize\",\"value\":121},{\"name\":\"breastSize\",\"value\":0},{\"name\":\"breastCleavage\",\"value\":149},{\"name\":\"eyeSpacing\",\"value\":150},{\"name\":\"green\",\"value\":214},{\"name\":\"red\",\"value\":255},{\"name\":\"blue\",\"value\":184}]}"}],"characterColors":[{"name":"Skin","colors":[197,118,90,255,0,0,0,0,255,255,255,255,0,0,0,0,255,255,255,255,0,0,0,0]},{"name":"Hair","colors":[84,71,57,255,0,0,0,0,255,255,255,255,0,0,0,0,255,255,255,255,0,0,0,0]},{"name":"Eyes","colors":[183,216,225,255,0,0,0,0,255,255,255,255,0,0,0,0,255,255,255,255,0,0,0,0]},{"name":"Undies","colors":[255,255,255,255,0,0,0,0,255,255,255,255,0,0,0,0,255,255,255,255,0,0,0,0]}],"wardrobeSet":[{"slot":"Eyes","recipe":"CatEyeWard"},{"slot":"Ears","recipe":"M_Elf_Ears"},{"slot":"Underwear","recipe":"FemaleUndies2"},{"slot":"Hair","recipe":"FemaleHair1"},{"slot":"Eyebrows","recipe":"FEyebrow1"},{"slot":"Eyelashes","recipe":"Fem_Eyelash"},{"slot":"Face","recipe":"FFace2"}],"raceAnimatorController":"UltimateCharacterControllerDemo"}

如果我从文件中读取它并以这种方式插入到 postgreSQL：

    public static void testwrite(NpgsqlConnection conn)
{
        string acct = "account1";
        string char_name = "character";
        string dna = File.ReadAllText("./file.txt", Encoding.UTF8);
    using (var cmd = new NpgsqlCommand())
    {
        cmd.CommandText = "INSERT INTO character_data (account_name, character_name, dna_and_equipment) VALUES (@p, @q, @r)";
        cmd.Parameters.Add(new NpgsqlParameter("@p", acct));
        cmd.Parameters.Add(new NpgsqlParameter("@q", char_name));
        cmd.Parameters.Add(new NpgsqlParameter("@r", NpgsqlDbType.Json) { Value = dna });
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();

    }
}

一切正常。但是，如果我从 Windows 机器将字符串作为字符串发送到我的 Linux 服务器，并尝试通过以下方式从服务器插入到同一台机器上的数据库：

public static IEnumerator Create_Char(string acct_name, string char_name, string DNA_wardrobe, Action<string> succ, Action<string> err)
    {
        string bad_register = "";
        if (char_name.Length >= 15 || char_name.Length <= 3 || char_name.Any(char.IsDigit) || char_name.Any(char.IsSymbol) || char_name.Contains(" "))
        {
            bad_register = "Please choose an account name having between 4 and 14 characters, letters only.";
            err(bad_register);
            yield break;
        }
        else if (badwords.Any(w => char_name.Contains(w)) || char_name.Any(char.IsSymbol))
        {
            bad_register = "Please choose an appropriate character name.";
            err(bad_register);
            yield break;
        }
        else
        {


            using (var cmd = new NpgsqlCommand())
            {
                cmd.CommandText = "SELECT EXISTS (SELECT character_name FROM character_data WHERE character_name = @q)";
                cmd.Parameters.Add(new NpgsqlParameter("@q", char_name));
                using (var conn = new NpgsqlConnection(dbConnectionString))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    if (Convert.ToBoolean(cmd.ExecuteScalar()))
                    {
                        bad_register = "That character name is already chosen.";
                        err(bad_register);
                        conn.Close();
                        yield break;
                    }
                    else
                    {

                        bad_register = "";
                        cmd.CommandText = "INSERT INTO character_data (account_name, character_name, dna_and_equipment) VALUES (@n, @p, @q)";
                        cmd.Parameters.Add(new NpgsqlParameter("@n", acct_name));
                        cmd.Parameters.Add(new NpgsqlParameter("@p", char_name));
                        cmd.Parameters.Add(new NpgsqlParameter("@q", NpgsqlDbType.Json) { Value = DNA_wardrobe });
                        cmd.ExecuteNonQuery();
                        succ(bad_register);
                        conn.Close();
                        yield break;
                    }
                }
            }
        }
    }

我得到 NpgsqlException: ERROR: 22P02: invalid input syntax for type json。现在，我知道我遗漏了很多，但很明显，我不能发布整个程序。如果您需要更多信息，请告诉我。

问题是，如果我在服务器上保存到文件，在它被转换为字节并从客户端发送并在服务器上接收之后，我可以使用从文件读取并插入的第一种方法......事实上，我甚至做了一个bash“diff”命令来查看两个文件之间的区别，根本没有区别......我不明白为什么一个插入工作而另一个不工作......我唯一能做的认为是编码。这可能是错误的原因吗？JSON 字符串中是否存在可能导致此错误的固有内容？可能是别的东西，我遗漏了一些相关信息吗？如果是这种情况，我很乐意根据需要进行编辑和添加。

我在 Ubuntu 18.04 服务器上有以下 postgres 包：postgresql-server-dev-10。方法 testwrite 使用来自 nuget 的最新 Npgsql 驱动程序，而协程 Create_Char 使用驱动程序的 Unity3d 变体。

编辑：我应该提到 JSON 来自第 3 方。我已经联系了他们，他们声称这是有效的 JSON，因为任何在线 JSON 验证器都会同意。但是，我刚刚注意到一件事。字符串中转义双引号的许多斜杠并不存在于每个双引号中。它给了我一个删除所有斜线的想法，果然，没有斜线它不是有效的 JSON。

标签： postgresqlc#npgsql