1
votes

I'm using C# and Postgres. I rolled my own security to escape the special chars I was aware off (like single quote), but then was advised that I didn't need to do that - parameterization could do it all for me!

So I deleted (archived) my code and replaced it all with parameterized Npgsqlcommands. Great!

However, a bit of testing shows that it doesn't escape single quotes when using NpgsqlCommand.ExecuteReader().

It seems to work fine when I do NpgsqlCommand.ExecuteNonQuery(). I listed my working code at the bottom of this message in case anyone doing this stumbles across it and finds it useful.

I'm kind of a novice, and would really appreciate someone more experienced to look over my code and help me understand what I'm doing wrong.

Here's my method for SQL queries that return data:

public static bool ExecuteSQLQuery(NpgsqlCommand cmdText, out DataTable dataTable)
        {
            try
            {
                string connectionString = ConfigurationManager.AppSettings["connectionString"];

                NpgsqlConnection sqlConnection = new NpgsqlConnection(connectionString);
                sqlConnection.Open();

                cmdText.Connection = sqlConnection;

                dataTable = new DataTable();
                dataTable.Load(cmdText.ExecuteReader(CommandBehavior.CloseConnection));

                sqlConnection.Close();

                return true;
            }
            catch(Exception ex)
            { dataTable = new DataTable();  return false; }
        }

Here's my code for calling that code:

DataTable dataTable = new DataTable();
            NpgsqlCommand sqlCommand = new NpgsqlCommand("SELECT * FROM users.users WHERE (email ilike @email or username ilike @username)");
            sqlCommand.Parameters.AddWithValue("email", NpgsqlTypes.NpgsqlDbType.Varchar, email);
            sqlCommand.Parameters.AddWithValue("username", NpgsqlTypes.NpgsqlDbType.Varchar, username);

DatabaseConnectivity.ExecuteSQLQuery(sqlCommand, out dataTable)

If I feed it a single quote character it fails.

Thanks in advance for any help.

PS: Here's my working code for SQL that doesn't return any data:

public static bool ExecuteSQLNonQuery(NpgsqlCommand cmdText)
        {
            try
            {
                string connectionString = ConfigurationManager.AppSettings["connectionString"];

                NpgsqlConnection sqlConnection = new NpgsqlConnection(connectionString);
                sqlConnection.Open();

                cmdText.Connection = sqlConnection;
                cmdText.ExecuteNonQuery();

                sqlConnection.Close();

                return true;
            }
            catch (Exception ex)
            { return false; }
        }
1
Can you provide more information on how it fails when you "feed it a single quote character"? - Shay Rojansky
Hi, I guess I hadn't tested it properly. I was using this string as input: '\ It was actually the \ character that wasn't getting escaped, which was confusing because in some scenarios it does. It seems that if you do an ilike query that contains a \ character, you need to escape it yourself. I think possibly its the executereader part that requires the extra \ to escape the ilike query. This leaves me worried that there will be other characters that aren't escaped properly. Of course there's always the chance that I'm just a noob doing it wrong! - Lewyh
The error I get is that a like query cant end with an escape character. - Lewyh
Ok, so I managed to fix it by replacing any instance of @"\" with @"\\" on parameters that are going to be used in ilike queries being read into datatables by the ExecuteReader command. It all works great now :) - Lewyh

1 Answers

0
votes

Ok, more testing showed that I was talking rubbish about the single quote not being escaped - it escapes it perfectly. What actually was happening is that @"\" (the backslash) character wasn't being escaped when send to the ExecuteReader method when the Npgsqlcommand was an ilike query. All other methods seem to escape the backslash just fine.

I managed to fix it by replacing any instance of @"\" with @"\" on parameters that are going to be used in ilike queries being read into datatables by the ExecuteReader command. It all works great now :)

Thanks to Shay who gave this junior programmer the kick I needed to do proper testing. Also thanks to my bed, which gave me the sleep I needed to be able to do the proper testing the next day.