Search code examples
c#sqlsql-injectionpsqlnpgsql

PostgreSQL, Npgsql returning 42601: syntax error at or near “$1”


While passing PostgreSQL command following error

42601: syntax error at or near "$1"

using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(DBManager.GetConnectionString()))
            {
                conn.Open();
                Logger.Info("connection opened for adding column");
                using (Npgsql.NpgsqlCommand addColumnQuery = new Npgsql.NpgsqlCommand(@"ALTER TABLE @tableName ADD COLUMN IF NOT EXISTS @columnName  @columnType;", conn))
                {
                    addColumnQuery.Parameters.AddWithValue("@tableName", tableName);
                    addColumnQuery.Parameters.AddWithValue("@columnName", columnName);
                    addColumnQuery.Parameters.AddWithValue("@columnType", columnType);
                    addColumnQuery.ExecuteNonQuery();
                }
            }

Solution

  • After some testing I found that only table values can be passed as parameter not table name and column name. So I changed code like this

    ALTER TABLE tableName ADD COLUMN columnName columnType;