Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i am trying to insert value in error log in postgre
INSERT INTO errorlog (idError,iduser,strMessage,xmlError)
this are coulmn value in strmessage conatin
6307d86b-8af9-41bf-a686-a84a86373703', '', '', '12/5/2023 7:44:51 AM', 'HandlingInstanceID: 907bf805-1512-421b-85bc-73bb066a31a6

An exception of type 'System.Data.Entity.Core.UpdateException' occurred and was caught.

Because of this system I am not able to insert it gave me error as ERROR: syntax error at or near "System" how to put single quote in between where system is used.

What I have tried:

var cmd = context.Database.Connection.CreateCommand();
cmd.CommandType = CommandType.Text;

if (strMessage.Length > 200) 
    strMessage = strMessage.Substring(0, 200);

string sanitizedExceptionDetails = "$$"+strMessage.ToString()+"$$";

  cmd.CommandText = "INSERT INTO mps_errorlog (idError,iduser,strMessage,xmlError) " +
"VALUES ('" + idError + "', '" + null + "', '" + , '" + strMessage + "','"+xmlError+"')";

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":iduser", NpgsqlTypes.NpgsqlDbType.Uuid)
{ Value = iduser=null });

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":strAnonymousId", NpgsqlTypes.NpgsqlDbType.Varchar)
{ Value = strAnonymousId });

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":dtmError", NpgsqlTypes.NpgsqlDbType.Timestamp)
{ Value = dtmError });

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":strmessage", NpgsqlTypes.NpgsqlDbType.Varchar)
{ Value = sanitizedExceptionDetails});

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":strPage", NpgsqlTypes.NpgsqlDbType.Varchar)
{ Value = strPage });

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":xmlError", NpgsqlTypes.NpgsqlDbType.Text)
{ Value = xmlError });

Updated 4-Dec-23 21:47pm

1 solution

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

Fix that, and you'll also fix your error.

cmd.CommandText = "INSERT INTO mps_errorlog (idError, iduser, strMessage, xmlError) " +
"VALUES (:iderror, :isuser, :strmessage, :xmlError)";

cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":iderror", NpgsqlTypes.NpgsqlDbType.Uuid){ Value = idError });
cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":iduser", NpgsqlTypes.NpgsqlDbType.Uuid){ Value = null });
cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":strmessage", NpgsqlTypes.NpgsqlDbType.Varchar){ Value = strMessage });
cmd.Parameters.Add(new Npgsql.NpgsqlParameter(":xmlError", NpgsqlTypes.NpgsqlDbType.Text){ Value = xmlError });

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
Share this answer
Maciej Los 5-Dec-23 3:53am    
Andre Oosthuizen 5-Dec-23 14:38pm    
+5 BOOM!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900