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; }
}