public static string DataTableToCsv(this DataTable dataTable) { var excelSanitizer = new NoFormulaeExcelSanitizer(); const char separator = ','; var sb = new StringBuilder(); for (var i = 0; i < dataTable.Columns.Count; i++) { sb.Append(dataTable.Columns[i]); if (i < dataTable.Columns.Count - 1) { sb.Append(separator); } } sb.AppendLine(); if (dataTable.Rows.Count > 0) { foreach (DataRow dr in dataTable.Rows) { for (var i = 0; i < dataTable.Columns.Count; i++) { sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer)); if (i < dataTable.Columns.Count - 1) { sb.Append(separator); } } sb.AppendLine(); } } return(sb.ToString()); }
public static string DataSetSentOnToCsv(this DataTable datatable, DataTable columnNameDataTable) { var excelSanitizer = new NoFormulaeExcelSanitizer(); var seperator = ','; var sb = new StringBuilder(); //Remove Column 0 from table for nil returns if (datatable.Columns.Contains("0")) { datatable.Columns.Remove("0"); } for (var i = 0; i < datatable.Columns.Count; i++) { //Replace columnnames from number 1 starting from 14th column if (columnNameDataTable != null && columnNameDataTable.Rows.Count > 0) { var matchingRow = columnNameDataTable.AsEnumerable().FirstOrDefault( x => x.Field <int>("SiteOperatorId").ToString() == datatable.Columns[i].ColumnName); if (matchingRow != null) { sb.Append("\""); sb.Append(matchingRow.Field <string>("SiteOperatorData")); sb.Append("\""); } else { sb.Append(datatable.Columns[i]); } } else { sb.Append(datatable.Columns[i]); } if (i < datatable.Columns.Count - 1) { sb.Append(seperator); } } sb.AppendLine(); if (datatable.Rows.Count > 0) { foreach (DataRow dr in datatable.Rows) { for (var i = 0; i < datatable.Columns.Count; i++) { sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer)); if (i < datatable.Columns.Count - 1) { sb.Append(seperator); } } sb.AppendLine(); } } return(sb.ToString()); }
public void Sanitize_WithUnsafeInput_RemovesLeadingEqualsSign() { // Arrange string input = "=cmd|'/C ping 127.0.0.1'!A0"; NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer(); // Act string result = sanitizer.Sanitize(input); // Assert Assert.Equal("cmd|'/C ping 127.0.0.1'!A0", result); }
public void Sanitize_WithSafeInput_ReturnsInputUnmodified() { // Arrange string input = "This is a safe input."; NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer(); // Act string result = sanitizer.Sanitize(input); // Assert Assert.Equal("This is a safe input.", result); }
public void IsThreat_WithUnsafeInput_ReturnsTrue() { // Arrange string input = "=cmd|'/C ping 127.0.0.1'!A0"; NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer(); // Act bool result = sanitizer.IsThreat(input); // Assert Assert.Equal(true, result); }
public void IsThreat_WithSafeInput_ReturnsFalse() { // Arrange string input = "This is a safe input."; NoFormulaeExcelSanitizer sanitizer = new NoFormulaeExcelSanitizer(); // Act bool result = sanitizer.IsThreat(input); // Assert Assert.Equal(false, result); }
public static string DataTableToCsv(this DataTable dataTable, List <string> columnsToRemove) { foreach (var columnName in columnsToRemove) { for (var columnCount = dataTable.Columns.Count - 1; columnCount >= 0; columnCount--) { if (dataTable.Columns[columnCount].ColumnName.Contains(columnName)) { dataTable.Columns.RemoveAt(columnCount); } } } var excelSanitizer = new NoFormulaeExcelSanitizer(); const char separator = ','; var sb = new StringBuilder(); for (var i = 0; i < dataTable.Columns.Count; i++) { sb.Append(dataTable.Columns[i]); if (i < dataTable.Columns.Count - 1) { sb.Append(separator); } } sb.AppendLine(); if (dataTable.Rows.Count > 0) { foreach (DataRow dr in dataTable.Rows) { for (var i = 0; i < dataTable.Columns.Count; i++) { sb.Append(EncodeAndCheck(dr[i].ToString(), excelSanitizer)); if (i < dataTable.Columns.Count - 1) { sb.Append(separator); } } sb.AppendLine(); } } return(sb.ToString()); }
public static string EncodeAndCheck(string value, NoFormulaeExcelSanitizer excelSanitizer) { string result; if (value == null) { result = string.Empty; } else { result = value.Equals("0.000") ? string.Empty : value.ToString(); } if (excelSanitizer.IsThreat(result)) { var message = string.Format( "A potentially dangerous string was identified and sanitised when writing CSV data. The value was \"{0}\".", result); Trace.TraceWarning(message); result = excelSanitizer.Sanitize(result); } if (result.Contains(",")) { result = string.Concat("\"", value, "\""); } result = result.Replace("\r\n", " "); result = result.Replace("\n\n", " "); result = result.Replace("\r", " "); result = result.Replace("\n", " "); result = result.Trim(); return(result); }